How to define two relationships to the same table in SQLAlchemy

Question

I’ve looked all over the SQLAlchemy tutorial and other similar questions but I seem to be struggling to get this join to work:

The scenario: I have a pages table represented by the Page model. Pages can be created by an user and edited by an user, but not necessarily the same one. My Page model looks like this (abridged):

class Page(Base):
    __tablename__ = 'pages'

    id = Column(Integer, primary_key = True)
    slug = Column(Text)
    title = Column(Text)
    direct_link = Column(Text)
    body = Column(Text)
    category_id = Column(Integer, ForeignKey('categories.id'))
    published_on = Column(DateTime)
    publishing_user_id = Column(Integer, ForeignKey('users.id'))
    last_edit_on = Column(DateTime)
    last_edit_user_id = Column(Integer, ForeignKey('users.id'))

    # Define relationships
    publish_user = relationship('User', backref = backref('pages', order_by = id), primaryjoin = "Page.publishing_user_id == User.id")
    edit_user = relationship('User', primaryjoin = "Page.last_edit_user_id == User.id")
    category = relationship('Category', backref = backref('pages', order_by = id))

My users are stored in the users table represented by the User model. As I said I’ve been all over the SQLAlchemy docs looking for this, I’ve tried to make it look as similar to their example as possible, but no to no avail. Any help would be greatly appreciated.

Im not sure ive set it up right for what i want it to do. I want to be able to get a page from a query, and call page.publish_user to get the publishing user. Ive tried the suggestion below, but still no luck — richzilla, Sep 25 '11 at 20:50
You say it's not working, but can you be more specific -- an error, an unexpected return value, what? I don't see any obvious problems in the code you've posted. — FMc, Sep 25 '11 at 20:55

ReWrite · Answer 1 · 2015-03-06T22:09:00.987

As of version 0.8, SQLAlchemy can resolve the ambiguous join using only the foreign_keys keyword parameter to relationship.

publish_user = relationship(User, foreign_keys=[publishing_user_id],
                                  backref=backref('pages', order_by=id))
edit_user = relationship(User, foreign_keys=[last_edit_user_id])

Documentation at http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#handling-multiple-join-paths

score 15 · Answer 2 · answered Sep 26 '11 at 08:27

I think you almost got it right; only instead of Model names you should use Table names when defining primaryjoin. So instead of

# Define relationships
publish_user = relationship('User', backref = backref('pages', order_by = id), 
    primaryjoin = "Page.publishing_user_id == User.id")
edit_user = relationship('User', 
    primaryjoin = "Page.last_edit_user_id == User.id")

use:

# Define relationships
publish_user = relationship('User', backref = backref('pages', order_by = id), 
    primaryjoin = "pages.publishing_user_id == users.id")
edit_user = relationship('User', 
    primaryjoin = "pages.last_edit_user_id == users.id")

this is probably the 20th sqlalchemy question you've helped me with — user2682863, Jan 14 '20 at 22:22

score 4 · Answer 3 · answered Sep 25 '11 at 20:31

4

Try foreign_keys option:

publish_user = relationship(User, foreign_keys=publishing_user_id,
                                  primaryjoin=publishing_user_id == User.id,
                                  backref=backref('pages', order_by=id))
edit_user = relationship(User, foreign_keys=last_edit_user_id,
                               primaryjoin=last_edit_user_id == User.id)

answered Sep 25 '11 at 20:31

minhee

5,688
5
43
81

2

When you add ``foreign_keys`` and join condition is deducible, there is no need to add ``primaryjoin`` attributes. – user2683246 Apr 01 '14 at 13:27

Otobong Jerome · Answer 4 · 2022-06-04T10:04:06.353

The example in this documentation
http://docs.sqlalchemy.org/en/rel_0_9/orm/join_conditions.html#handling-multiple-join-paths isn't for one-to-many.... I think.
In the one-to-many case here's what worked for me:


class Pipeline(Base):
    __tablename__ = 'pipelines'
    id = Column(UUID(as_uuid=True), primary_key=True, unique=True, default=uuid.uuid4)
...

    input_resources = relationship("Resource", foreign_keys="Resource.input_pipeline_id")
    output_resources = relationship("Resource", foreign_keys="Resource.output_pipeline_id")

   ...


class Resource(Base):
    __tablename__ = 'resources'
    id = Column(UUID(as_uuid=True), primary_key=True, unique=True, default=uuid.uuid4)
   ....

    input_pipeline_id = Column(UUID(as_uuid=True), ForeignKey("pipelines.id"))
    output_pipeline_id = Column(UUID(as_uuid=True), ForeignKey("pipelines.id"))

    ...

How to define two relationships to the same table in SQLAlchemy

4 Answers4

Linked