Trying to create a one-to-many relationship in both directions

All, thanks for taking the time to help. Basically, I have a table called Task that has a one-to-many relationship with a table called Label. This works just fine. I can assign every label I create to a task ID and then I can query labels by the task id. But I would also like to have a one-to-many relationship in reverse so every label can have a relationship with all tasks. Hope that makes sense. This is all pretty new to me so maybe I'm going about it all wrong. Maybe I'm making it too complicated and should remove all relationships and just create a column in each table that stores the information. I was hoping to take advantage of some of the things that come with having a relationship though.

Here are my classes with a lot of extra stuff removed to make it easier to view.


class Task(Base):
  __tablename__ = 'tasks'

  # This works fine
  labels: Mapped[List["Task"]] = relationship(
    "Label",
    back_populates="task",
    )

  # I then added these for the reverse one-to-many and get the error
  label_id: Mapped[PYTHON_UUID] = mapped_column(
        UUID,
        ForeignKey("labels.id"),
    )
  label: Mapped["Label"] = relationship(
    "Label",
    back_populates="task_ids"
    )

  # this is a different one-to-many relationship that goes to a Comment table. This
  # works okay too, but maybe it's relevant? 
  comments: Mapped[List["Task"]] = relationship(
    "Comment",
    back_populates="task",
    cascade="all, delete",
    )


class Label(Base):
  __tablename__ = 'labels' 
  # These work fine
  task_id: Mapped[Optional[PYTHON_UUID]] = mapped_column(
        UUID,
        ForeignKey("tasks.id"),
        nullable=False,
    )
  task: Mapped[List[Task]] = relationship(
    "Task",
    back_populates="labels"
    )

  # I then added this for the reverse one-to-many and get the error
  tasks: Mapped[List["Label"]] = relationship(
    "Task",
    back_populates="label",
    )

This is the error:

sqlalchemy.exc.AmbiguousForeignKeysError: Could not determine join condition between parent/child tables on relationship Task.labels - there are multiple foreign key paths linking the tables. Specify the 'foreign_keys' argument, providing a list of those columns which should be counted as containing a foreign key reference to the parent table.

It sounds like maybe I just need to add the 'foreign_keys' argument, but I'm a little lost on how to accomplish that with what I'm trying to do.

Here are some of the relevant libraries I'm using:

SQLAlchemy: 2.0.31
fastapi: 0.111.0
asyncpg: 0.29.0
pydantic: 2.8.2
pydantic_core: 2.20.1

TIA, Jon

Edit to update based on what @snakecharmerb mentioned. I did try a many-to-many, but wasn't sure if that was the right way to do it. I had this as my association table:

label_task_association_table = Table(
  'association',
  Base.metadata,
  Column('task_id', UUID, ForeignKey('tasks.id')),
  Column('label_id', UUID, ForeignKey('labels.id')),
)

I just wasn't seeing a column of tasks associated with the label and wasn't seeing a list of labels associated with the column.

Solution

A many-to-many relationship is usually regarded as this one-to-many relationship in both directions. In database design terms, it means defining an intermediate table with columns of the two primary IDs of the related tables to define that the relationship exists.

Many-to-many relationship

Say your tasks table is populated like this:

ID	Name
1	T1
2	T2

And your labels table looks like this:

ID	Name
1	L1
2	L2
3	L3

And let's say you want to express that:

Task T1 has the labels L1 and L2
Task T2 has the labels L2 and L3

This intermediate table has to hold a record for each association between the tasks and labels. So it will look like this:

ID	FK to tasks	FK to labels
1	1	1
2	1	2
3	2	2
4	2	3

A many-to-many relationship allows reading task has labels and label has tasks. Reading this table, this time we can also express that:

Label L1 is in task T1
Label L2 is in tasks T1 and T2
Label L3 is in task T2

In SQLAlchemy

Because you want to both access the labels a task has, and the tasks a label is in, you want to define a bi-directional many-to-many relationship:

# this is the intermediate table that defines the many-to-many
tasks_labels = Table(
  "tasks_labels",
  Base.metadata,
  Column("task_id", ForeignKey("tasks.id"), primary_key=True),
  Column("label_id", ForeignKey("labels.id"), primary_key=True),
)

class Task(Base):
  __tablename__ = "tasks"

  # this id might be redundant if your Base class adds it
  id: Mapped[int] = mapped_column(primary_key=True)
  # labels will get populated with the labels the task has
  # no need to add Columns or FKs here, since that's tasks_labels job
  labels: Mapped[List[Label]] = relationship(
    secondary=tasks_labels, back_populates="tasks"
  )

class Label(Base):
  __tablename__ = "labels"

  # this id might be redundant if your Base class adds it
  id: Mapped[int] = mapped_column(primary_key=True)
  # tasks will get populated with the tasks the label is in
  tasks: Mapped[List[Child]] = relationship(
    secondary=tasks_labels, back_populates="labels"
  )