Flask - SQLAlchemy - 自关联联合查询

我们继续上一篇的用户模型，在 “关注者/粉丝” 模型的基础上加上用户动态（Post），更接近于微博的结构。

完整代码如下：

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///demo.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

db = SQLAlchemy(app)
migrate = Migrate(app, db)


followers = db.Table('followers',
    db.Column('follower_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('followed_id', db.Integer, db.ForeignKey('user.id'))
)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50), nullable=False)
    followed = db.relationship(
        # User 表示关系当中的右侧实体（将左侧实体看成是上级类）
        # 由于这是自引用关系，所以两侧都是同一个实体。
        'User', 

        # secondary 指定了用于该关系的关联表
        # 就是使用我在上面定义的 followers
        secondary=followers,

        # primaryjoin 指明了右侧对象关联到左侧实体（关注者）的条件 
        # 也就是根据左侧实体查找出对应的右侧对象
        # 执行 user.followed 时候就是这样的查找
        primaryjoin=(followers.c.follower_id == id),

        # secondaryjoin 指明了左侧对象关联到右侧实体（被关注者）的条件 
        # 也就是根据右侧实体找出左侧对象
        # 执行 user.followers 时候就是这样的查找
        secondaryjoin=(followers.c.followed_id == id),

        # backref 定义了右侧实体如何访问该关系
        # 也就是根据右侧实体查找对应的左侧对象
        # 在左侧，关系被命名为 followed
        # 在右侧使用 followers 来表示所有左侧用户的列表，即粉丝列表
        backref=db.backref('followers', lazy='dynamic'), 
        lazy='dynamic'
        )

    posts = db.relationship('Post', backref='author', lazy='dynamic')

    def is_following(self, user):
        """
        检查是否已关注 user
        """
        return self.followed.filter(
            followers.c.followed_id == user.id
            ).count() > 0

    def follow(self, user):
        if not self.is_following(user):
            self.followed.append(user)

    def unfollow(self, user):
        if self.is_following(user):
            self.followed.remove(user)

    def followed_posts(self):
        followed = Post.query.join(
            followers, (followers.c.followed_id == Post.user_id)).filter(
                followers.c.follower_id == self.id)
        own = Post.query.filter_by(user_id=self.id)
        return followed.union(own)


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    user_id = db.Column(db.Integer, db.ForeignKey('user.id'))

    def __repr__(self):
        return '<Post {}>'.format(self.body)



@app.shell_context_processor
def make_shell_context():
    return {
    'db': db, 
    'User': User, 
    'Post': Post,
    'followers': followers,
    }

数据库示意图如下：

数据库示意

开始我们实验前现为数据表添加一点数据。

followers 表有如下数据：

follower_id	followed_id
1	2
1	3
2	3
2	4

Post 表有如下数据：

id	body	user_id
1	post1 from user2	2
2	post2 from user2	2
3	post1 from user3	3
4	post1 from user4	4
5	post1 from user1	1

现在我想查询 user1 关注的用户的动态，我们的思路先是从 followers 表查询到 user1 关注用户的 id，即 2 和 3；然后在 Post 表中找出 user_id 为 2 和 3 的记录。为了实现这一目的，我们需要把 Post 表和 followers 表联合起来。

我们使用 join() 关键字建立联合查询：

Post.query.join(followers, (followers.c.followed_id == Post.user_id))

第一个参数是 followers 关联表，第二个参数是 join 的条件。

使用 join 后数据库创建一个临时表，它将用 Post 表和 followers 表中的数据结合在一起，数据将根据参数传递的条件进行合并。

联合后的临时表类似这样：

id	body	user_id	follower_id	followed_id
1	post1 from user2	2	1	2
2	post2 from user2	2	1	2
3	post1 from user3	3	1	3
3	post1 from user3	3	2	3
4	post1 from user4	4	2	4

注意 user_id 和 followed_id 列在所有数据行中都是相等的，因为这是
join 的条件。

user3 的 Post 出现了两次，因为他被 2 个人关注了。

有了联合表，现在用 filter 关键字过滤搜索条件：

filter(followers.c.follower_id == self.id)

现在要查询 user1 关注者的 post，使用 followers.c.follower_id == 1 查询条件就能容易得出。