SQLAlchemyにおいて、relationshipでJOINして取得するデータにORDER BY句やWHERE句を適用する

はじめに

SQLAlchemyではrelationshipを用いて簡単にJOINを行うことができますが、そこにソートや絞り込みなどの条件を追加する方法の日本語記事がなかったので執筆しました。

対象者

この記事は下記のような人を対象にしています。

• SQLAlchemyのrelationshipを使用しており、取得条件を追加したい人

結論

ProjectsテーブルとUsersテーブル、およびその中間テーブルに相当するProjectUsersを例に記述してみます。
relationship にてORDER BY句やWHERE句を指定します。ModelBaseは今回の内容とは直接関係ありませんが、実装の参考にしていただければと思います。

models.php

from sqlalchemy import Column, Integer, String, ForeignKey, DATETIME, func, Boolean
from sqlalchemy.orm import declarative_base, relationship, mapped_column

Base = declarative_base()

# どのテーブルでも基本的に使われるカラムを別途定義し、継承させています。
# sort_orderを使用するとカラムの位置を統一することができ、見栄えがよくなります。
class ModelBase:
    # UUIDを使用する場合はString型になります。
    id = mapped_column(Integer, primary_key=True, sort_order=-10)
    is_hidden = mapped_column(
        Boolean, nullable=False, server_default="0", sort_order=96
    )
    order_num = mapped_column(
        Integer, nullable=False, server_default="1000000", sort_order=97
    )
    created_at = mapped_column(
        DATETIME, nullable=False, server_default=func.now(), sort_order=98
    )
    updated_at = mapped_column(
        DATETIME,
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
        sort_order=99,
    )


class ProjectUser(Base, ModelBase):
    __tablename__ = "projects_users"
    project_id = Column(Integer, ForeignKey("projects.id"), nullable=False)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    user = relationship("User")
    project = relationship("Project")


class Project(Base, ModelBase):
    __tablename__ = "projects"
    title = Column(String(255), server_default="", nullable=False)
    users = relationship(
        "User",
        secondary=ProjectUser.__tablename__,
        back_populates="projects",
        viewonly=True,
    )
    project_user = relationship(
        "ProjectUser",
        overlaps="project",
        # ORDER BY句, WHERE句を指定
        order_by="ProjectUser.order_num",
        primaryjoin="and_(Project.id==ProjectUser.project_id, ProjectUser.is_hidden == 0)",
    )


class User(Base, ModelBase):
    __tablename__ = "users"
    name = Column(String(255), nullable=False)
    projects = relationship(
        "Project",
        secondary=ProjectUser.__tablename__,
        back_populates="users",
        viewonly=True,
    )
    project_user = relationship("ProjectUser", overlaps="user")

おわりに

SQLAlchemyにおいて、relationshipでJOINして取得するデータにORDER BY句やWHERE句を適用するについてまとめました。

参考記事

alembicで作成されるカラムの順序を指定するmapped_columnとsort_orderの使い方
SQLAlchemy公式ドキュメント