How to cascade insertions into association table for many-to-many relationships

[shrir]

Hi 👋

This is my first time with advanced-alchemy, and it's been great so far 🙌.

I have a simple many-to-many relationship. When I create a record in one of the tables, I was expecting an association to be added automatically.

service = BookService(session=db_session)
service.create(Book(title="My Book", authors=[author]) # Author already exists

In this example. I want to automatically add an association to the book_author_association table. Is this supported? Or am I missing something?

Here's my schema:

book_author_association = Table(
    'book_author_association',
    metadata,
    Column('book_id', ForeignKey('books.id')),
    Column('author_id', ForeignKey('authors.id'))
)

class Book(UUIDBase):
    __tablename__ = 'books'
    title = Column(String)
    authors = relationship('Author', secondary=book_author_association, back_populates='books')

class Author(UUIDBase):
    __tablename__ = 'authors'
    name = Column(String)
    books = relationship('Book', secondary=book_author_association, back_populates='authors')

class BookRepository(SQLAlchemySyncRepository[Book]):
    model_type = User

class BookService(SQLAlchemySyncRepositoryService[Book]):
    repository_type = BookRepository

[Alc-Alc]

Heya, I tried the following example and I can see relations being added. Can you clarify what is different?

from sqlalchemy import Table, Column, ForeignKey, String, create_engine
from sqlalchemy.orm import relationship, Session

from advanced_alchemy.base import UUIDBase
from advanced_alchemy import SQLAlchemySyncRepository, SQLAlchemySyncRepositoryService

book_author_association = Table(
    "book_author_association",
    UUIDBase.metadata,
    Column("book_id", ForeignKey("books.id")),
    Column("author_id", ForeignKey("authors.id")),
)


class Book(UUIDBase):
    __tablename__ = "books"
    title = Column(String)
    authors = relationship("Author", secondary=book_author_association, back_populates="books")

class Author(UUIDBase):
    __tablename__ = "authors"
    name = Column(String)
    books = relationship("Book", secondary=book_author_association, back_populates="authors")

class BookRepository(SQLAlchemySyncRepository[Book]):
    model_type = Book

class BookService(SQLAlchemySyncRepositoryService[Book]):
    repository_type = BookRepository

class AuthorRepository(SQLAlchemySyncRepository[Author]):
    model_type = Author

class AuthorService(SQLAlchemySyncRepositoryService[Author]):
    repository_type = AuthorRepository

engine = create_engine("sqlite:///test.sqlite")

UUIDBase.metadata.create_all(engine)

with Session(engine) as session:
    author_service = AuthorService(session=session)
    author_service.create(Author(name="default author"), auto_commit=True)


with Session(engine) as session:
    author_service = AuthorService(session=session)
    service = BookService(session=session)
    author = author_service.get_one(name="default author")
    service.create(Book(title="My Book", authors=[author]), auto_commit=True)

[shrir]

Thanks @Alc-Alc, I figured it out!

Here's what's different:

1. My code is running as a domain service in Litestar.
2. I am converting a dict to a Book model using to_model() before create().

Here's the code snippet:

service = BookService(session=db_session)
author = Author("AB") # Author already exists in db
book_dict = {"title": "My Book", "authors": [author]}
book = service.to_model(book_dict, "create")
service.create(book)

book.authors is an empty list in the Book object returned by to_model().
When I handle this explicitly(book.authors = book_dict["authors"]), everything works!

Learning: Relationships need to be handled manually when using to_model() 🤔