SQLAlchemy(ORM)でN対Nの中間テーブルに存在するカラムのデータを取得する

Flask-SQLAlchemyを使っている時に、N対Nの中間テーブル（Association Table）に存在するデータを取得するときにどうすればパッとわからなかったのでメモ。

中間テーブルに外部キー以外のカラムが無い場合

例えば、以下のようなRDBMSのN対Nの構造テーブルがある場合を想定する。

class PageTag(db.Model):

    __tablename__ = 'pages_tags'

    page_id = db.Column(db.Integer, db.ForeignKey('pages.id'), primary_key=True)
    tag_id = db.Column(db.Integer, db.ForeignKey('tags.id'), primary_key=True)


class Page(db.Model):

    __tablename__ = 'pages'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255), nullable=False)

    tags = db.relationship(
        'Tag',
        secondary=PageTag.__tablename__,
        back_populates='pages',
    )


class Tag(db.Model):

    __tablename__ = 'tags'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)

    pages = db.relationship(
        'Page',
        secondary=PageTag.__tablename__,
        back_populates='tags',
    )

このModel定義上では、SQLAlchemy(ORM版)だと以下のようにして、N対Nで紐付けられたデータの作成・保存・取得を行うことができる。

# --作成・保存--
page = Page(title='some title')
tag = Tag(name='some title')

page.tags.append(tag)

db.session.add(page)
db.session.commit()  # DBに保存

# --取得--
page.tags  # => [<Tag 1>]
tag.pages  # => [<Page 1>] 

中間テーブルに外部キー以外のカラムがある場合

中間テーブルに外部キー以外のカラム、例えば今回ではPageとTagの関係に対して「有効日時」を表すlimit_dataというデータがある場合を想定する。
（あくまでも例なのでページとタグの間にある有効日時ってなんだよってツッコミはナシで。）

この場合は、中間テーブルのオブジェクトが取得できるようにrelationshipを追加で定義しておく。

class PageTag(db.Model):

    __tablename__ = 'pages_tags'

    page_id = db.Column(db.Integer, db.ForeignKey('pages.id'), primary_key=True)
    tag_id = db.Column(db.Integer, db.ForeignKey('tags.id'), primary_key=True)
    limit_date = db.Column(db.Data, nullable=True)  # 追加

    tag = db.relationship('Tag')  # 追加
    page = db.relationship('Page')  # 追加


class Page(db.Model):

    __tablename__ = 'pages'

    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(255), nullable=False)

    tags = db.relationship(
        'Tag',
        secondary=PageTag.__tablename__,
        back_populates='pages',
    )
    page_tag = db.relationship('PageTag')  # 追加


class Tag(db.Model):

    __tablename__ = 'tags'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(255), nullable=False)

    pages = db.relationship(
        'Page',
        secondary=PageTag.__tablename__,
        back_populates='tags',
    )
    page_tag = db.relationship('PageTag')  # 追加

そしてデータの登録・取得を行うには以下のように操作する。

from datetime import date

# --作成・保存--
page = Page(title='some title')
tag = Tag(name='some title')

# page_tagオブジェクトを作成して紐付けていく
page_tag = PageTag(limit_date=date.today())
page_tag.tag = tag
page.page_tag.append(page_tag)

db.session.add(page)
db.session.commit()  # DBに保存

# --取得--
page.tags  # => [<Tag 2>]
tag.pages  # => [<Page 2>]

page.page_tag  # => [<PageTag 2, 2>]
page.page_tag[0].limit_date  # => datetime.date(2018, 2, 24)

他に、例えばlimit_dateが本日のtagを取得する場合は以下のようにする。

Tag.query.join(PageTag).filter(PageTag.limit_date == date.today()).all()  # => [<Tag 2>]

（参考）
http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object