python - SQLAlchemy 计算所有行，我想要特定行

Question

换句话说，我正在努力实现这个目标:

“获取 5 条评论，其中 comment.post_id == self.context.id 并按照 Comment_Vote.vote_type == 'like' 的最高数量对这些评论进行排序”

目前的模型是:

vote_enum = ENUM('like', 'dislike', name='vote_enum', create_type=False)

class User(Base):
    __tablename__='users'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(65), nullable=False)
    comments = relationship('Comment', backref='user')
    comment_vote = relationship('Comment_Vote', backref='user')
    posts=relationship('Post', backref='user')

class Post(Base):
    __tablename__ = 'post'
    id = Column(Integer, primary_key=True, autoincrement=True)
    body= Column(String(1500))
    comments= relationship('Comment',backref='post', order_by='desc(Comment.date_created)', lazy='dynamic')
    owner_id= Column(Integer, ForeignKey('users.id'))

class Comment(Base):
    __tablename__='comment'
    id = Column(Integer, primary_key=True, autoincrement=True)
    body= Column(String(500))
    parent_id = Column(Integer, ForeignKey('comment.id'))
    post_id= Column(Integer, ForeignKey('post.id'))
    user_id= Column(Integer, ForeignKey('users.id'))
    children = relationship("Comment",
            backref=backref('parent', remote_side=[id]),
            lazy='dynamic'
            )
    del_flag= Column(Boolean, default=False)
    date_created= Column(DateTime(), default=datetime.datetime.utcnow())
    last_edited= Column(DateTime(), default=datetime.datetime.utcnow())
    comment_vote= relationship("Comment_Vote", backref="comment", lazy='dynamic')

class Comment_Vote(Base):
    __tablename__='comment_vote'
    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id= Column(Integer, ForeignKey('users.id'))
    comment_id= Column(Integer, ForeignKey('comment.id'))
    vote_type = Column('vote_enum', vote_enum)

    @classmethod
    def total_likes(cls, comment_id, session):
        return session.query(cls).filter(cls.id == comment_id).first().comment_vote.filter(Comment_Vote.vote_type=='like').count()

我的功能查询是:

f = session.query(Comment_Vote.comment_id, funcfilter(func.count(1), Comment_Vote.vote_type == 'like').label('total_likes')).group_by(Comment_Vote.comment_id).subquery()

comments = session.query(Comment, f.c.total_likes).join(f, Comment.id==f.c.comment_id).filter(Comment.post_id == self.context.id).order_by('total_likes DESC').limit(5)

这有一个令人讨厌的副作用，即计算所有 comment_vote 的“赞”，即使是与该帖子无关的评论。

如果能提供一些关于如何重新排列它的建议，我将非常感激，这样它就不必先计算所有内容。我想要的可能无法实现，而且我主要在 ORM 中工作。

SQLAlchemy 背后的数据库是 Postgresql。

Answer 1

这可能是使用 lateral subquery 的好地方.它是 SQL 的“foreach”，也就是说横向子查询可以引用前面 FROM 项的列。 Postgresql 支持 9.3 及更高版本的横向，SQLAlchemy from versions 1.1 and up :

from sqlalchemy import true

f = session.query(func.count(1).label('total_likes')).\
    filter(Comment_Vote.comment_id == Comment.id,  # References Comment
           Comment_Vote.vote_type == 'like').\
    subquery().\
    lateral()

comments = session.query(Comment, f.c.total_likes).\
    join(f, true()).\
    filter(Comment.post_id == self.context.id).\
    order_by(f.c.total_likes.desc()).\
    limit(5)

我将基于 vote_type 的过滤移到了子查询的 WHERE 子句中，因为在这种情况下没有必要先获取所有行然后在聚合函数中过滤(也不能使用索引)。

当然在这种情况下你也可以使用 scalar subquery在 SELECT 输出中以获得相同的效果:

f = session.query(func.count(1)).\
    filter(Comment_Vote.comment_id == Comment.id,  # References Comment
           Comment_Vote.vote_type == 'like').\
    label('total_likes')

comments = session.query(Comment, f).\
    filter(Comment.post_id == self.context.id).\
    order_by(f.desc()).\
    limit(5)