多对多关联含额外参数的查询：仅返回Writer角色的作者

方法一：查询时动态过滤关联数据（推荐，无需修改模型） #

使用with_loader_criteria查询选项，在加载书籍的作者关联时，只筛选角色为writer的记录。这种方式不需要修改现有模型，适合临时查询需求。

1. 返回所有书籍（包括无作者/无writer的书籍） #

from sqlalchemy import select
from sqlalchemy.orm import with_loader_criteria

# 执行查询
books = session.execute(
    select(Book)
    .options(
        with_loader_criteria(
            Author,
            Book_Author.role == 'writer',
            include_aliases=True
        )
    )
).scalars().all()

• with_loader_criteria指定对Author实体的过滤条件，关联中间表Book_Author的role字段
• include_aliases=True确保SQLAlchemy正确匹配中间表的关联别名，避免查询报错
• 结果中，无writer的书籍的authors列表会是空数组

2. 仅返回有writer的书籍 #

如果只需要保留有至少一位writer的书籍，可结合join和distinct去重：

books = session.execute(
    select(Book)
    .join(Book_Author, Book.book_id == Book_Author.book_id)
    .filter(Book_Author.role == 'writer')
    .distinct()  # 避免同一书籍因多个writer被重复返回
    .options(
        with_loader_criteria(
            Author,
            Book_Author.role == 'writer',
            include_aliases=True
        )
    )
).scalars().all()

方法二：新增专用关联关系（适合频繁查询场景） #

如果需要频繁查询writer角色的作者，可以在模型中新增一个专用的relationship，固定过滤条件：

修改模型定义 #

class Book(Base):
    __tablename__ = "book"

    book_id = Column(Integer, primary_key=True, index=True)
    title = Column(VARCHAR(128))
    # 原有全量作者关联
    authors = relationship("Author", secondary="book_author", back_populates='books')
    # 新增：仅关联writer角色的作者
    writers = relationship(
        "Author",
        secondary="book_author",
        primaryjoin="Book.book_id == Book_Author.book_id",
        secondaryjoin="and_(Book_Author.author_id == Author.author_id, Book_Author.role == 'writer')",
        back_populates='written_books'
    )


class Author(Base):
    __tablename__ = "author"

    author_id = Column(Integer, primary_key=True, index=True)
    name = Column(VARCHAR(128))
    # 原有全量书籍关联
    books = relationship("Book", secondary="book_author", back_populates='authors')
    # 新增：仅关联作为writer参与的书籍
    written_books = relationship(
        "Book",
        secondary="book_author",
        primaryjoin="Author.author_id == Book_Author.author_id",
        secondaryjoin="and_(Book_Author.book_id == Book.book_id, Book_Author.role == 'writer')",
        back_populates='writers'
    )

使用新关联查询 #

from sqlalchemy.orm import selectinload

# 查询所有书籍并加载对应的writer
books = session.query(Book).options(selectinload(Book.writers)).all()

# 此时每本书的writers属性就是该书籍的所有作者
for book in books:
    print(f"书名：{book.title}，作者：{[a.name for a in book.writers]}")

内容的提问来源于stack exchange，提问作者AJ Treg