用sqlalchemy比较两个数据库条目

Question

我有一个叫做 Users 的表，这个表和一个叫做 Visits 的表之间有一种一对多的关系（每个 User 都有两个 Visits）。在 Visits 表里，有多个列，其中包括 Score。我想为每个 User 计算他们在两个 Visits 中的 Score 差值。

用 SQLAlchemy 来做这件事，最简单的方法是什么？如果不直接在数据库里计算差值，我也可以先把每个 Visit 的 Score 拉出来，只要它们已经通过相同的用户关联在一起。这样我就可以用 Score1-Score2 来计算差值。

注意：拉取所有 Score 和 Users 的时候，可以先找出 Visits.visit_number==1 的数据，然后再找出 Visits.visit_number==2 的数据，这个过程是比较简单的。

data = session.query(Visit.Score, User.id).join(User).filter(Visits.visit_number==1)
data2 = session.query(Visit.Score, User.id).join(User).filter(Visits.visit_number==2)

问题在于，如何把这两组分数匹配起来是个大麻烦（因为它们不会按用户的顺序排列，而且可能会有一些用户还没有第二次访问）。我相信在 SQLAlchemy 中有一种优雅的方式可以做到这一点，可能需要用到别名。

Answer 1

下面的代码会返回一个列表，里面包含了那些至少有两次访问的用户的 (User.id, Score1-Score2) 组合。

from sqlalchemy.orm import aliased

Visit1 = aliased(Visit)
Visit2 = aliased(Visit)
session.query(User)\
    .join(Visit1, (Visit1.user_id==User.id) & (Visit1.visit_number==1))\
    .join(Visit2, (Visit2.user_id==User.id) & (Visit2.visit_number==2))\
    .values(User.id, Visit1.Score-Visit2.Score)

这里的 Visit.user_id 是一个列，它指的是 User.id。

实际上，可以安全地不把 User 表加入到连接中，因为 Visit 表已经有了 User 的 ID，而且你也不需要从 User 表中获取其他数据。