Создание модели сотрудников и групп в SQLAlchemy

1. Создание модели сотрудников и групп в SQLAlchemy

Условие задачи:
Необходимо спроектировать модели в SQLAlchemy для двух сущностей: Employee и Group.
Для сущности Employee должны храниться:

табельный номер
имя
email

Для сущности Group должны храниться:

имя или название группы
описание группы

Также нужно реализовать связи между таблицами:

у одной группы может быть несколько сотрудников
один сотрудник может состоять в нескольких группах
одна группа может включать в себя другие группы
одна группа может входить в состав нескольких других групп

То есть требуется реализовать:

связь many-to-many между сотрудниками и группами
самоссылочную many-to-many связь для групп

Нужно описать SQLAlchemy-модели и связи между ними.

Спойлеры к решению

Подсказки

Между Employee и Group нужна связь many-to-many.
Для many-to-many в SQLAlchemy используется промежуточная таблица и параметр secondary.
Между Group и Group тоже нужна many-to-many связь, но самоссылочная.
Для самоссылочной many-to-many связи нужны две колонки: parent_group_id и child_group_id.
parent_group_id показывает группу-родителя.
child_group_id показывает вложенную группу.
Для таблиц-связок удобно использовать составной первичный ключ, чтобы не было дублей связей.

Решение

from __future__ import annotations

from sqlalchemy import ForeignKey, String, Table, Column, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


employee_group = Table(
    "employee_group",
    Base.metadata,
    Column(
        "employee_id",
        ForeignKey("employees.id", ondelete="CASCADE"),
        primary_key=True,
    ),
    Column(
        "group_id",
        ForeignKey("groups.id", ondelete="CASCADE"),
        primary_key=True,
    ),
)


group_group = Table(
    "group_group",
    Base.metadata,
    Column(
        "parent_group_id",
        ForeignKey("groups.id", ondelete="CASCADE"),
        primary_key=True,
    ),
    Column(
        "child_group_id",
        ForeignKey("groups.id", ondelete="CASCADE"),
        primary_key=True,
    ),
)


class Employee(Base):
    __tablename__ = "employees"

    id: Mapped[int] = mapped_column(primary_key=True)
    personnel_number: Mapped[str] = mapped_column(
        String(50),
        unique=True,
        nullable=False,
    )
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)

    groups: Mapped[list[Group]] = relationship(
        secondary=employee_group,
        back_populates="employees",
    )


class Group(Base):
    __tablename__ = "groups"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)

    employees: Mapped[list[Employee]] = relationship(
        secondary=employee_group,
        back_populates="groups",
    )

    child_groups: Mapped[list[Group]] = relationship(
        "Group",
        secondary=group_group,
        primaryjoin=id == group_group.c.parent_group_id,
        secondaryjoin=id == group_group.c.child_group_id,
        back_populates="parent_groups",
    )

    parent_groups: Mapped[list[Group]] = relationship(
        "Group",
        secondary=group_group,
        primaryjoin=id == group_group.c.child_group_id,
        secondaryjoin=id == group_group.c.parent_group_id,
        back_populates="child_groups",
    )

Связь сотрудников и групп:

employees N ─── M groups

Она реализована через промежуточную таблицу:

employee_group

В ней хранятся пары:

employee_id
group_id

Это означает, что один сотрудник может состоять в нескольких группах, и одна группа может содержать нескольких сотрудников. В SQLAlchemy many-to-many связь обычно описывается через промежуточную таблицу, переданную в relationship(..., secondary=...). ([Документация SQLAlchemy][1])

Самоссылочная связь групп:

groups N ─── M groups

Она реализована через таблицу:

group_group

В ней хранятся пары:

parent_group_id
child_group_id

Например:

parent_group_id = 1
child_group_id = 2

означает, что группа 2 входит внутрь группы 1.

Поле:

child_groups

показывает группы, которые входят в текущую группу.

Поле:

parent_groups

показывает группы, в состав которых входит текущая группа.

Для самоссылочной many-to-many связи нужно явно указать primaryjoin и secondaryjoin, потому что SQLAlchemy должен понимать, какая колонка таблицы-связки отвечает за родительскую группу, а какая — за дочернюю. В документации SQLAlchemy такая схема описывается как self-referential many-to-many relationship. ([Документация SQLAlchemy][2])

Пример использования:

employee = Employee(
    personnel_number="EMP-001",
    name="Ivan Ivanov",
    email="ivan@example.com",
)

backend_group = Group(
    name="Backend",
    description="Backend developers",
)

python_group = Group(
    name="Python",
    description="Python developers",
)

backend_group.employees.append(employee)
backend_group.child_groups.append(python_group)

После этого:

employee.groups

будет содержать группу Backend.

А:

backend_group.child_groups

будет содержать группу Python.

При этом:

python_group.parent_groups

будет содержать группу Backend.