如何使用Flask-SQLAlchemy对具有一对多关系的Model进行过滤

你之前的写法报错是因为：User.tweets是SQLAlchemy的InstrumentedAttribute对象，不是实际的列表，Python的len()只能作用于实例化后的User对象的tweets属性，无法直接转换为SQL查询语句，普通的hybrid_property只实现了Python实例层面的逻辑，没有提供SQL层面的查询表达式，因此也无法用于filter条件。

方案1：使用JOIN + GROUP BY + HAVING 直接查询 #

这是最通用的实现方式，无需修改模型定义：

from sqlalchemy import func

filters = (
  User.first_name == 'John',
  User.birth_year == 1970
)
users = User.query.join(Tweet)\
        .filter(*filters)\
        .group_by(User.id)\
        .having(func.count(Tweet.id) > 20)\
        .all()

如果需要包含发推数为0的用户（比如筛选发推数小于5的用户），把join换成outerjoin即可。

方案2：完善hybrid_property的SQL表达式 #

如果你需要在多个地方复用这个计数筛选逻辑，可以给hybrid_property补充对应的SQL表达式实现：

from sqlalchemy import func
from sqlalchemy.ext.hybrid import hybrid_property

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    birth_year = db.Column(db.Integer)
    tweets = db.relationship('Tweet', backref='user', lazy=True)

    @hybrid_property
    def tweet_count(self):
        # 实例层面调用时生效，比如 user.tweet_count
        return len(self.tweets)
    
    @tweet_count.expression
    def tweet_count(cls):
        # SQL查询层面调用时生效，用于filter条件
        return db.select([func.count(Tweet.id)]).where(Tweet.user_id == cls.id).label('tweet_count')

之后就可以直接在filter里使用这个属性：

filters = (
  User.first_name == 'John',
  User.tweet_count > 20
)
users = User.query.filter(*filters).all()

方案3：使用column_property预定义计数字段 #

如果这个计数字段使用频率很高，可以直接把它定义为column_property，查询时会自动关联计算：

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    first_name = db.Column(db.String(50), nullable=False)
    last_name = db.Column(db.String(50), nullable=False)
    birth_year = db.Column(db.Integer)
    tweets = db.relationship('Tweet', backref='user', lazy=True)
    
    # 新增tweet_count字段，查询时自动计算
    tweet_count = db.column_property(
        db.select([func.count(Tweet.id)]).where(Tweet.user_id == id).label('tweet_count')
    )

使用方式和普通字段完全一致：

filters = (
  User.first_name == 'John',
  User.tweet_count > 20
)
users = User.query.filter(*filters).all()

内容的提问来源于stack exchange，提问作者Sadi