many to many relationship support

[xmansyx]

is there a way to display the many-to-many relationship data in the single view?
i have these models

class Notes(Base):
    _tablename_ = "notes"
    id = Column(Integer, primary_key=True)
    author_id = Column(UUID(as_uuid=True), ForeignKey("user.id"), nullable=False)
    title = Column(String, nullable=False)
    description = Column(String, nullable=False)
    impacted_areas = relationship("Division", secondary=notes_areas)

class Areas(Base):
    _tablename_ = "areas"
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    state = Column(String, nullable=False)|

notes_areas = Table(
    "notes_areas",
    Base.metadata,
    Column(
        "notes_id",
        Integer,
        ForeignKey("notes.id"),
        primary_key=True,
    ),
    Column("area_id", Integer, ForeignKey("area.id"), primary_key=True),
)

is there a way to display the areas inside the notesview?

[hasansezertasan]

I must check it out to answer your question properly but I encourage you to use Association Object instead of your approach, it's more readable, flexible and easier to create a view for it.

[xmansyx]

i was already going to create an Association object, if i did this though would i be able to edit the areas from the notes view for example? or i have to edit it from the Association view?

[hasansezertasan]

Here is an example, it works like a charm:

from sqlalchemy import (
    ForeignKey,
    create_engine,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship, sessionmaker
from starlette.applications import Starlette
from starlette.responses import HTMLResponse
from starlette.routing import Route
from starlette_admin.contrib.sqla import Admin, ModelView
from starlette_admin.fields import IntegerField


class Base(DeclarativeBase):
    pass


class IDMixin:
    id: Mapped[int] = mapped_column(primary_key=True)


class User(Base, IDMixin):
    __tablename__ = "user"
    name: Mapped[str]


class Task(Base, IDMixin):
    __tablename__ = "task"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str]
    description: Mapped[str]
    assignees: Mapped[list[User]] = relationship(
        "User",
        secondary="assignment",
    )


class Assignment(Base, IDMixin):
    __tablename__ = "assignment"
    user_id: Mapped[int] = mapped_column(ForeignKey(User.id))
    task_id: Mapped[int] = mapped_column(ForeignKey(Task.id))


class UserView(ModelView):
    label = "Users"
    name = "User"
    fields = [
        User.id,
        User.name,
    ]


class TaskView(ModelView):
    label = "Tasks"
    name = "Task"
    fields = [
        Task.id,
        Task.title,
        Task.description,
        Task.assignees,
    ]


class AssignmentView(ModelView):
    label = "Assignments"
    name = "Assignment"
    fields = [
        IntegerField(
            name="id",
            label="ID",
            help_text="ID of the record.",
            read_only=True,
        ),
        IntegerField(
            name="user_id",
            label="User ID",
            help_text="User ID of the record.",
            read_only=True,
        ),
        IntegerField(
            name="task_id",
            label="Task ID",
            help_text="Task ID of the record.",
            read_only=True,
        ),
    ]


engine = create_engine(
    "sqlite:///db.sqlite3",
    connect_args={"check_same_thread": False},
    echo=True,
)
session = sessionmaker(bind=engine, autoflush=False)


def init_database() -> None:
    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)
    with session() as db:
        pass


app = Starlette(
    routes=[
        Route(
            "/",
            lambda r: HTMLResponse('<a href="/admin/">Click me to get to Admin!</a>'),
        )
    ],
    on_startup=[init_database],
)

# Create admin
admin = Admin(engine, title="Example: Association Objects")

# Add views
admin.add_view(AssignmentView(model=Assignment))
admin.add_view(TaskView(model=Task))
admin.add_view(UserView(model=User))

# Mount admin
admin.mount_to(app)

If you put primary_key=True to the user_id and task_id columns, you'll get an error from #226