CODEWITHSUNDEEP
pythonpostgresqlormsqlalchemy
Mark Hallbauer
Mark Hallbauer

Reputation: 75

SQLAlchemy ORM - 3-some object relationships

I'm using SQLAlchemy with Postgres.
Code example from https://auth0.com/blog/sqlalchemy-orm-tutorial-for-python-developers/

I have 3 objects:

In the original code example, the relationships are as follow:

See Code:

class Actor(Base):
    __tablename__ = 'actors'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    birthday = Column(Date)


class Stuntman(Base):
    __tablename__ = 'stuntmen'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    active = Column(Boolean)
    actor_id = Column(Integer, ForeignKey('actors.id'))
    actor = relationship("Actor", backref=backref("stuntman", uselist=False))


class ContactDetails(Base):
    __tablename__ = 'contact_details'

    id = Column(Integer, primary_key=True)
    phone_number = Column(String)
    address = Column(String)
    actor_id = Column(Integer, ForeignKey('actors.id'))
    actor = relationship("Actor")

I'm struggling to extend this model to following relationships:

Stuntmen work hard too and deserve ContactDetails. Who can help please?!

Upvotes: 0

Views: 88

Answers (1)

Cysio
Cysio

Reputation: 53

In my opinion best way to achieve what you need is using association table. This one should work:

actors_to_contact_details = Table('actors_to_contact_details', Base.metadata,
    Column('actor_id', Integer, ForeignKey('actors.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'))
)


stuntmen_to_contact_details = Table('stuntmen_to_contact_details', Base.metadata,
    Column('stuntman_id', Integer, ForeignKey('stuntmen.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'))
)


class Actor(Base):
    __tablename__ = 'actors'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    birthday = Column(Date)
    contact_details = relationship("ContactDetails", secondary=actors_to_contact_details)


class Stuntman(Base):
    __tablename__ = 'stuntmen'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    active = Column(Boolean)
    actor_id = Column(Integer, ForeignKey('actors.id'))
    actor = relationship("Actor", backref=backref("stuntman", uselist=False))
    contact_details = relationship("ContactDetails", backref="stuntman", secondary=stuntmen_to_contact_details)


class ContactDetails(Base):
    __tablename__ = 'contact_details'

    id = Column(Integer, primary_key=True)
    phone_number = Column(String)
    address = Column(String)

if you want, you can put unique=True in association table like this

actors_to_contact_details = Table('actors_to_contact_details', Base.metadata,
    Column('actor_id', Integer, ForeignKey('actors.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'), unique=True)
)


stuntmen_to_contact_details = Table('stuntmen_to_contact_details', Base.metadata,
    Column('stuntman_id', Integer, ForeignKey('stuntmen.id')),
    Column('contact_detail_id', Integer, ForeignKey('contact_details.id'), unique=True)
)

Other option would be to put another nullable foreign key on Stuntman table, but I would go with solution presented above.

Upvotes: 1

Related Questions