OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

Ternary Table Relationships in SQLAlchemy ORM

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Guest
I'm trying to figure out how to do a "ternary" table relationship, which is many-many-many. I've found online how to model this as an ERD, but I'm having difficulty modeling this in SQLAlchemy's DeclarativeBase approach.

Here's an ERD of what i'm trying to accomplish: Screenshot of ERD with tables

And here's the Python table code I'm trying to get working:

Code:
class Base(DeclarativeBase):
    id: Mapped[int] = mapped_column(Integer, primary_key=True)

workers_queues_table = Table(
    "workers_queues",
    Base.metadata,
    Column("fk_worker_id", ForeignKey("workers.id"), primary_key=True),
    Column("fk_queue_id", ForeignKey("queues.id"), primary_key=True),
)

profiles_workers_queues_table = Table(
    "profiles_workers_queues",
    Base.metadata,
    Column("fk_profile_id", ForeignKey("profiles.id"), primary_key=True),
    Column("fk_workers_queues_worker_id", ForeignKey("workers_queues.fk_worker_id"), primary_key=True),
    Column("fk_workers_queues_queue_id", ForeignKey("workers_queues.fk_queue_id"), primary_key=True),
    UniqueConstraint("fk_workers_queues_worker_id", "fk_workers_queues_queue_id"),
)

class Queue(Base):
    __tablename__ = "queues"
    name: Mapped[str] = mapped_column(Text)

    workers: Mapped[list["Worker"]] = relationship(
        secondary=workers_queues_table,
        back_populates="queues",
    )
    profiles: Mapped[list["Profile"]] = relationship(
        secondary=workers_queues_table,
        back_populates=""
    )

class Worker(Base):
    __tablename__ = "workers"
    __table_args__ = (
        UniqueConstraint("name", "hostname")
    )
    name: Mapped[str] = mapped_column(Text, nullable=False)

    profiles: Mapped[list["Profile"]] = relationship()
    queues: Mapped[list["Queue"]] = relationship()

class Profile(Base):
    __tablename__ = "profiles"
    name: Mapped[str] = mapped_column(Text)

    workers: Mapped[list["Worker"]] = relationship()
    queues: Mapped[list["Queue"]] = relationship()

I don't know how to fill in the relationship() bits in the class models, especially back_populates since that would be expecting an attribute in another model, not a Table entity (i assume?).

Is this possible in the ORM?

Thanks!
<p>I'm trying to figure out how to do a "ternary" table relationship, which is many-many-many. I've found online how to model this as an ERD, but I'm having difficulty modeling this in SQLAlchemy's DeclarativeBase approach.</p>
<p>Here's an ERD of what i'm trying to accomplish:
<a href="https://i.sstatic.net/Jfp0ChX2.png" rel="nofollow noreferrer">Screenshot of ERD with tables</a></p>
<p>And here's the Python table code I'm trying to get working:</p>
<pre class="lang-py prettyprint-override"><code>class Base(DeclarativeBase):
id: Mapped[int] = mapped_column(Integer, primary_key=True)

workers_queues_table = Table(
"workers_queues",
Base.metadata,
Column("fk_worker_id", ForeignKey("workers.id"), primary_key=True),
Column("fk_queue_id", ForeignKey("queues.id"), primary_key=True),
)

profiles_workers_queues_table = Table(
"profiles_workers_queues",
Base.metadata,
Column("fk_profile_id", ForeignKey("profiles.id"), primary_key=True),
Column("fk_workers_queues_worker_id", ForeignKey("workers_queues.fk_worker_id"), primary_key=True),
Column("fk_workers_queues_queue_id", ForeignKey("workers_queues.fk_queue_id"), primary_key=True),
UniqueConstraint("fk_workers_queues_worker_id", "fk_workers_queues_queue_id"),
)

class Queue(Base):
__tablename__ = "queues"
name: Mapped[str] = mapped_column(Text)

workers: Mapped[list["Worker"]] = relationship(
secondary=workers_queues_table,
back_populates="queues",
)
profiles: Mapped[list["Profile"]] = relationship(
secondary=workers_queues_table,
back_populates=""
)

class Worker(Base):
__tablename__ = "workers"
__table_args__ = (
UniqueConstraint("name", "hostname")
)
name: Mapped[str] = mapped_column(Text, nullable=False)

profiles: Mapped[list["Profile"]] = relationship()
queues: Mapped[list["Queue"]] = relationship()

class Profile(Base):
__tablename__ = "profiles"
name: Mapped[str] = mapped_column(Text)

workers: Mapped[list["Worker"]] = relationship()
queues: Mapped[list["Queue"]] = relationship()
</code></pre>
<p>I don't know how to fill in the <code>relationship()</code> bits in the class models, especially <code>back_populates</code> since that would be expecting an attribute in another model, not a Table entity (i assume?).</p>
<p>Is this possible in the ORM?</p>
<p>Thanks!</p>
 

Latest posts

Top