SqlAlchemy - two foreign keys and bidirectional relationship

Viewed 7

I have a small base for a website for a real estate agency, below are two tables:

class Person(Base):
    __tablename__ = "people"
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    surname = Column(String, nullable=False)
    city = Column(String, nullable=True)
    # TODO - add lists


class Property(Base):
    __tablename__ = "properties"
    id = Column(Integer, primary_key=True, index=True)
    city = Column(String, nullable=False)
    address = Column(String, nullable=False)
    owner_id = Column(Integer, ForeignKey("people.id"), nullable=False)
    manager_id = Column(Integer, ForeignKey("people.id"), nullable=False)

    # TODO - rework
    owner = relationship("Person", foreign_keys=[owner_id], backref=backref("owners"))
    manager = relationship("Person", foreign_keys=[manager_id], backref=backref("managers"))

I would like my 'Person' object to have two lists of properties - "owned_properites" and "properties_to_manage" (without losing reference to the owner/manager in the 'Property' class). But i don't know how to define a relationship to make auto mapping work properly.

If the class 'Property' only had one foreign key to the 'Person', for example - only "owner_id" key and "owner" object then it would be simple:

#in Property
owner_id = Column(Integer, ForeignKey("people.id"), nullable=False)
owner = relationship("Person", back_populates="property")

#in Person
owned_properties = relationship("Property", back_populates="owner")

But how to do the same with two keys, as shown at the beginning?

0 Answers
Related