Flask-SQLAlchemy联合查询

一对多关系联合查询

模型定义如下,Category 和 Post 是一对多的关系:

class Category(db.Model):
    __tablename__ = "category"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    posts = db.relationship("Post", backref="category", lazy="dynamic")


class Post(db.Model):
    __tablename__ = "post"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    category_id = db.Column(db.Integer, db.ForeignKey(Category.id), nullable=False)

查询每个 Category 对应 Post 的数量并按照 Post 的个数从大到小排列:

from sqlalchemy import func

results = Category.query \
    .join(Post) \
    .add_columns(func.count(Post.id)) \
    .group_by(Category.id) \
    .order_by(func.count(Post.id).desc()) \
    .all()

多对多关系联合查询

模型定义如下,Label 和 Post 是多对多的关系:

class Label(db.Model):
    __tablename__ = "label"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    posts = db.relationship(
        "Post",
        secondary="post_label_ref",
        backref=db.backref("labels", lazy="dynamic"),
        lazy="dynamic",
    )


class Post(db.Model):
    __tablename__ = "post"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True)


class PostLabelRef(db.Model):
    __tablename__ = "post_label_ref"
    post_id = db.Column(db.Integer, db.ForeignKey(Post.id), primary_key=True)
    label_id = db.Column(db.Integer, db.ForeignKey(Label.id), primary_key=True)

查询每个 Label 对应 Post 的数量并按照 Post 的个数从大到小排列。

results = Label.query \
    .join(PostLabelRef) \
    .join(Post) \
    .add_columns(func.count(Post.id)) \
    .group_by(Label.id) \
    .order_by(func.count(Post.id).desc()) \
    .all()

相比一对多关系的联合查询,多对多关系联合查询时需要 join 关系表

参考链接

目录