Flask-SQLAlchemy 联合查询

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

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)

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()

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

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)

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()