sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper[User(user)]

Question

I can't figure out what's wrong with this I tried to look for solutions, but there were mistakes. Maybe

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, Mapped, DeclarativeBase, mapped_column
import uuid as uuid_pkg


class Base(DeclarativeBase):
    pass


class User(Base):
    __tablename__ = "user"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True, comment="Уникальный идентификатор пользователя")
    uuid: Mapped[uuid_pkg.UUID] = mapped_column(unique=True, insert_default=uuid_pkg.uuid4,
                                                comment="Уникальный uuid4 пользователя")
    email: Mapped[str] = mapped_column(unique=True, comment="Почта, по которой пользователь регистрировался")
    hashed_password: Mapped[str] = mapped_column(comment="Хеш пароля от аккаунта")
    _user_project: Mapped["UserProject"] = relationship(secondary="_user", lazy="joined")
    _subscription: Mapped["SubscriptionPlan"] = relationship(back_populates="_user", lazy="joined")


class Project(Base):
    __tablename__ = "project"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    uuid: Mapped[uuid_pkg.UUID] = mapped_column(unique=True, insert_default=uuid_pkg.uuid4)
    name: Mapped[str] = mapped_column(nullable=False)
    _notification: Mapped["Notification"] = relationship(back_populates="_project", lazy="joined")
    _user_project: Mapped["UserProject"] = relationship(secondary="_project", lazy="joined")


class UserProject(Base):
    __tablename__ = "user_project"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey(column=User.id, ondelete='cascade', onupdate='cascade'))
    project_id: Mapped[int] = mapped_column(ForeignKey(column=Project.id, ondelete='cascade', onupdate='cascade'))
    role: Mapped[str] = mapped_column(nullable=False)
    _user: Mapped["User"] = relationship(secondary="_user_project", lazy="joined")
    _project: Mapped["Project"] = relationship(secondary="_user_project", lazy="joined")


class Notification(Base):
    __tablename__ = "notification"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    project_id: Mapped[int] = mapped_column(ForeignKey(Project.id, ondelete='cascade', onupdate='cascade'))
    email: Mapped[str] = mapped_column(nullable=True)
    telegram_id: Mapped[int] = mapped_column(nullable=True)
    vk_domain: Mapped[str] = mapped_column(nullable=True)
    website: Mapped[str] = mapped_column(nullable=True)
    _project: Mapped["Project"] = relationship(back_populates="_notification", lazy="joined")


class SubscriptionPlan(Base):
    __tablename__ = "subscription_plan"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey(User.id, ondelete='cascade', onupdate='cascade'))
    name: Mapped[str] = mapped_column(nullable=False)
    event_volume: Mapped[int] = mapped_column(nullable=False)
    current_event_volume: Mapped[int] = mapped_column(nullable=False)
    project_volume: Mapped[int] = mapped_column(nullable=False)
    data_retention: Mapped[int] = mapped_column(nullable=False)
    _user: Mapped["User"] = relationship(back_populates="_subscription", lazy="joined")

Stack_trace when I tried to request

slalchemy.exc.InvalidRequestError: When initializing mapper Mapper[User(user)], expression '_user' failed to locate a name ("name '_user' is not defined"). If this is a class name, consider adding this relationship() to the <class 'auth.database.models.User'> class after both dependent classes have been defined.

I've tried moving classes around

Answer 1

The secondary keyword argument is meant for when you don't use the association class but a regular table only. This is for simple many-2-many relationships. If you need to access data (role in this case) about the relationship between the two other classes (User and Project in this case) then you need to use this AssociationObject pattern. Ie. my_roles = [user_project.role for user_project in user._user_projects]. If traversing across the middle class becomes cumbersome you can either select with a join across it or you can add a viewonly relationship between the two outer classes.

In your case this might work:

class User(Base):
    __tablename__ = "user"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    _user_projects: Mapped["UserProject"] = relationship(back_populates="_user", lazy="joined")



class Project(Base):
    __tablename__ = "project"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    _user_projects: Mapped["UserProject"] = relationship(back_populates="_project", lazy="joined")


class UserProject(Base):
    __tablename__ = "user_project"
    __table_args__ = {"schema": "public"}
    id: Mapped[int] = mapped_column(primary_key=True)
    user_id: Mapped[int] = mapped_column(ForeignKey(column=User.id, ondelete='cascade', onupdate='cascade'))
    project_id: Mapped[int] = mapped_column(ForeignKey(column=Project.id, ondelete='cascade', onupdate='cascade'))
    _user: Mapped["User"] = relationship(back_populates="_user_projects", lazy="joined")
    _project: Mapped["Project"] = relationship(back_populates="_user_projects", lazy="joined")

You can read more about the AssociationObject pattern in the docs: AssociationObject