[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jonathan Vanasco]

On Friday, April 24, 2020 at 1:16:10 PM UTC-4, Jens Troeger wrote:
>
>
> If I understand you correctly, then the solution above is as good as it 
> gets and SQLA doesn’t provide a builtin solution for what I’m trying to do?
>

There are so many hooks in SqlAlchemy, there may still be a more elegant 
manner... however, you are accomplishing exactly what you want with 
documented features, so it's not going to break in an update.

>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/6d4ef82a-6c81-4790-8f0c-1dc7e21efc20%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

Thanks Jonathan! Yes, all classes derive from the declarative base:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base(metadata=MetaData(naming_convention={...}))

class Parent(Base):
...

class Child(Base):
...

If I understand you correctly, then the solution above is as good as it 
gets and SQLA doesn’t provide a builtin solution for what I’m trying to do?

I’m just curious if there is a better solution to this, or if perhaps my 
design could be be improved in general 樂

Much thanks,
Jens

>

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/1bcd7db4-e8f2-4bd1-b087-e51d110c9cf5%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jonathan Vanasco]

Assuming you are using declarative, this is okay (
https://docs.sqlalchemy.org/en/14/orm/mapping_api.html?#sqlalchemy.orm.Mapper
)

Note this line:
  A class which was mapped by the sqlalchemy.ext.declarative 

 extension will also have its mapper available via the __mapper__ attribute.

Mike generally doesn't change things once they're stated in the docs.

If you're not using declarative, it will probably be okay. Those things 
don't typically change.  If they do, you can easily grab the mapper in your 
property with something like this:

sqlalchemy.orm.class_mapper(self.__class__).relationships 

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/6a39b653-0139-4968-b6aa-c671769262cf%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

So, here’s what I’m experimenting with and it seems to work:

@property
def children(self):
children = (getattr(self, r.key) for r in 
self.__mapper__.relationships if r.target.name == "child")
return [c for c in children if c is not None]

I’m not sure if this is the proper way to work with SQLA’s internals, but 
it gives me a list of children 邏

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f9bc3585-d907-4058-8b87-31f6b56f3d9f%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

Thank you, Jonathan.

I’ve used SQLA’s association proxies before, I’ll take a look again.

You bring up a good point, though:

Ok, so this isn't a one-to-one relationship, but a many-to-many 
> relationship.
>

That’s something I’ve been debating with myself for a while before I posted 
here: the kind of relationship here. A Child can have only a single Parent, 
but a Parent has multiple Children. At first is looks like a 1-to-many, but 
the oddity is the “type” of the Child expressed through a named foreign key 
constraint: “youngest_child” and “oldest_child” are the examples here. The 
reason why it’s done this way is because a Child should not have knowledge 
of its type and how the Parent views the Child.

It’s always possible to explicitly enumerate the Child objects on the 
Parent:

children = [youngest_child, oldest_child]

but I am curious if there is a better way to do that, one that involves 
less typing and would pick changes (e.g. adding a “shortest_child” or some 
such) transparently.

Cheers,
Jens

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/26803606-3d2e-48f1-b10a-2ac07cd23e94%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jonathan Vanasco]

On Friday, April 17, 2020 at 8:02:50 PM UTC-4, Jens Troeger wrote:
>
>
>  Indeed, Child does have multiple parents…
>

Ok, so this isn't a one-to-one relationship, but a many-to-many 
relationship.

I would opt for a 3 table structure:

Parent
Parent_2_Child
Child

The AssociationProxy extension (part of SqlAlchemy itself) can 
transparently map the Parent and Child relationships through the 
Parent_2_Child table as attributes on Parent & Child tables itself.  (
https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html)

The section "Simplifying Association Objects" (
https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#simplifying-association-objects)
 
gives a good example of this on User/UserKeyword/Keyword

You could even remove the `oldest_child_id` and `youngest_child_id` 
columns, and turn them into readonly attributes that can compute the child 
as needed.

SqlAlchemy is very robust and you can do almost anything with it.  I am 
certain it is possible to build something that only uses the columns on the 
Parent table to accomplish exactly what you desire... but it really sounds 
like you should be using an association table in this model.


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/746f8e4d-c61a-4a74-9812-faecce7b0efa%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jens Troeger]

Hi Jonathan,

The line you commented out from the example was either:
>
> children = relationship("Child")
>
> children = relationship("Child", back_populates="parent")
>
>
> both of those lines create an iterable list of all the Child objects on 
> the `children`
>

Neither of them would work, because

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join 
condition between parent/child tables on relationship Parent.children - 
there are multiple foreign key paths linking the tables.  Specify the 
'foreign_keys' argument, providing a list of those columns which should be 
counted as containing a foreign key reference to the parent table.

Unfortunately, the following didn’t work either:

children = relationship("Child", foreign_keys=[oldest_child_id, 
youngest_child_id])

(Oddly enough, specifying a list with a single element *does* work.)

Off the top of my head, the simplest way to accomplish this would be to add 
> a "parent_id" column on the child table, and then create a relationship for 
> "children" that correlates the `Parent.id` to `Child.parent_id`.  
>
> That change might not work with your data model if a Child can have 
> multiple parents. 
>

 Indeed, Child does have multiple parents…

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/f548656e-75aa-4041-a505-4a272c28f9b4%40googlegroups.com.

[sqlalchemy] Re: Consolidate multiple one-to-one into a list

[Jonathan Vanasco]

This departs a bit from the example, because you are caching the youngest 
and oldest ids onto the Parent object.  is that necessary for your usage?

> Now my question is: how can I introduce a set/list of all children on the 
parent?

The line you commented out from the example was either:

children = relationship("Child")

children = relationship("Child", back_populates="parent")


both of those lines create an iterable list of all the Child objects on the 
`children`

There are a handful of ways you could structure this.  It really depends on 
your data model and usage patterns.

Off the top of my head, the simplest way to accomplish this would be to add 
a "parent_id" column on the child table, and then create a relationship for 
"children" that correlates the `Parent.id` to `Child.parent_id`.  

That change might not work with your data model if a Child can have 
multiple parents. 



-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/ecfe0528-e141-44f4-a39e-eff4e1a3fe6d%40googlegroups.com.