CODEWITHSUNDEEP
pythonsqlsqlalchemyrelationship
richzilla
richzilla

Reputation: 42082

How to define two relationships to the same table in SQLAlchemy

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.

Upvotes: 34

Views: 26300

Answers (4)

Otobong Jerome
Otobong Jerome

Reputation: 506

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

    ...

Upvotes: 1

ReWrite
ReWrite

Reputation: 2708

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

Upvotes: 31

van
van

Reputation: 77092

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

Upvotes: 17

minhee
minhee

Reputation: 5828

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)

Upvotes: 4

Related Questions