SqlAlchemy 关系

SqlAlchemy 关系

一对多

class Comment(db.Model):
    # ...
    
class User(db.Model):
    # ...
    comments = db.relationship('Comment', backref='author', lazy='dynamic')
class Post(db.Model):
    # ...
    comments = db.relationship('Comment', backref='post', lazy='dynamic')

• db.relationship()，关系只用设置一次。通过 backref 设定反向逻辑
• lazy='dynamic'，保证了关系属性返回的是查询对象。还可以继续跟过滤器

多对多

registrations = db.Table('registrations',
                         db.Column('student_id', db.Integer, db.ForeignKey('students.id')),
                         db.Column('class_id', db.Integer, db.ForeignKey('classes.id'))
                         )
class Student(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    classes = db.relationship('Class',
                              secondary=registrations,
                              backref=db.backref('students', lazy='dynamic'),
                              lazy='dynamic')
class Class(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

• secondary=registrations，设置关联表，而非模型

# 关系管理
c1 = Class('c1')
s1 = Student('s1')
s1.class.append(c1)
s1.class.remove(c1)  
# 查询
s1.class.all()
c1.students.filter(Student.id>1).all()

自引用

用户的关注者还是属于用户，他们都在同一张表里建立关系。

此时，要提升关系表的地位。并且将多对多拆成两个一对多。

class Follow(db.Model):
    __tablename__ = 'follows'
    follower_id = db.Column(db.Integer, db.ForeignKey('users.id'),
                            primary_key=True)
    followed_id = db.Column(db.Integer, db.ForeignKey('users.id'),
                            primary_key=True)
    timestamp = db.Column(db.DateTime, default=datetime.utcnow)

class User(UserMixin, db.Model):
    # ...
    followed = db.relationship('Follow',
                               foreign_keys=[Follow.follower_id],
                               backref=db.backref('follower', lazy='joined'),
                               lazy='dynamic',
                               cascade='all, delete-orphan')
    followers = db.relationship('Follow',
                                foreign_keys=[Follow.followed_id],
                                backref=db.backref('followed', lazy='joined'),
                                lazy='dynamic',
                                cascade='all, delete-orphan')

• foreign_keys=[Follow.follower_id]，必须指定外键
• backref=db.backref('followed',)，backref 并不是两个关系之间的引用关系，而是回到 Follow 模型
• lazy='joined'，一次性拉取所有 user.followed 对象
• cascade='all, delete-orphan'，启用所有默认层叠选项，并且删除孤儿记录

class User(db.Model):
    # ...
    def follow(self, user):
        if not self.is_following(user):
            f = Follow(follower=self, followed=user)
            db.session.add(f)

    def unfollow(self, user):
        f = self.followed.filter_by(followed_id=user.id).first()
        if f:
            db.session.delete(f)

    def is_following(self, user):
        return self.followed.filter_by(
            followed_id=user.id).first() is not None

    def is_followed_by(self, user):
        return self.followers.filter_by(
            follower_id=user.id).first() is not None

    @property
    def followed_posts(self):
        return Post.query.join(Follow, Follow.followed_id == Post.author_id)\
                .filter(Follow.follower_id == self.id)

参考

1. Flask Web开发：基于Python的Web应用开发实战