SQLAlchemy one-to-many relationship clarification

After reading the SQLAlchemy documentation, it's still unclear to me how one-to-many relationships are actually supposed to be specified. I'll break down the documentation and explain why I'm confused (http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many):

A one to many relationship places a foreign key on the child table referencing the parent.

It looks like I am to place some Column attribute on the model that will be on the "many" side of the relationship.

relationship() is then specified on the parent, as referencing a collection of items represented by the child:

This means that there is some attribute on the parent that specifies the model participating in the "many" side of the relationship.

This would make total sense to me if it weren't for that fact that there could be a situation where I want to define two one-to-many relationships with the same participants on both sides of the relationship.

How does SQLAlchemy know that the ForeignKey column on the "many" side of the relationship corresponds to the relationship attribute placed on the "one" side?

Solution

A one-to-many relationship is set up like this:

class Group(Base):
    id = Column(Integer, primary_key=True)
    users = relationship(lambda: User)

class User(Base):
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Group.id))

SQLAlchemy infers that you meant to use parent_id as the join condition for users based on the fact that it's the only foreign key linking the two tables.

In the case where you have circular relationships:

class Group(Base):
    id = Column(Integer, primary_key=True)
    owner_id = Column(Integer, ForeignKey("users.id"))
    users = relationship(lambda: User)

class User(Base):
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Group.id))
    owned_groups = relationship(Group)

If you try this, it won't work because SQLAlchemy complains that it cannot infer what foreign key to use for each relationship. Instead, you have to tell it explicitly what to use:

class Group(Base):
    id = Column(Integer, primary_key=True)
    owner_id = Column(Integer, ForeignKey("users.id"))
    users = relationship(lambda: User, foreign_keys=lambda: User.parent_id)

class User(Base):
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Group.id))
    owned_groups = relationship(Group, foreign_keys=Group.owner_id)

A more complete example with backrefs:

class Group(Base):
    id = Column(Integer, primary_key=True)
    owner_id = Column(Integer, ForeignKey("users.id"))
    users = relationship(lambda: User, foreign_keys=lambda: User.parent_id, back_populates="parent")
    owner = relationship(lambda: User, foreign_keys=owner_id, back_populates="owned_groups")

class User(Base):
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Group.id))
    owned_groups = relationship(Group, foreign_keys=Group.owner_id, back_populates="owner")
    parent = relationship(Group, foreign_keys=parent_id, back_populates="users")