Flask-SQLAlchemy联合查询

一对多关系联合查询

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

1
2
3
4
5
6
7
8
9
10
11
12
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 的个数从大到小排列:

1
2
3
4
5
6
7
8
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 是多对多的关系:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 的个数从大到小排列。

1
2
3
4
5
6
7
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 关系表

参考链接

觉得有用可以请作者喝杯咖啡呀~
0%