Consolidate multiple one-to-one into a list

[Jens Troeger]

Hello,

Taking the relationship examples from the documentation, suppose I have the following:

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)

    oldest_child_id = Column(Integer, ForeignKey("child.id"))
    oldest_child = relationship("Child", foreign_keys=oldest_child_id)

    youngest_child_id = Column(Integer, ForeignKey("child.id"))
    youngest_child = relationship("Child", foreign_keys=oldest_child_id)

    # children = ...

class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)

For the sake of argument, we care for exactly two children per parent. Now my question is: how can I introduce a set/list of all children on the parent?

The naive approach would be something like

@property
def children(self):
    return [self.oldest_child, self.youngest_child] # Or set(), or tuple().

In my particular case, the Child is actually a File table, and different other tables may have one or more Files associated. But it would be nice if these tables had a “files” property which is a consolidation of all their explicitly associated files.

Thank you!

Jens

[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. 

[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…

[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.

[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

[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 🤓

[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

[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

[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.