How to define two relationships to the same table

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.

Answer 1

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

Answer 2

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)

Answer 3

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")