CODEWITHSUNDEEP
pythonsqlsqlalchemy
jeffalstott
jeffalstott

Reputation: 2693

Comparing two database entries with sqlalchemy

I've a table of Users with a one-to-many relationship on a table of Visits (each User had two Visits). In Visits are multiple columns, including Score. For each User, I'd like to calculate the difference in their Score across the two Visits.

What is the most sane way to do this with SQLAlchemy? In lieu of calculating the difference directly in the database call, I'd be fine with pulling the Score for each Visit, assuming they're already matched together via having the same User. Then it's just Score1-Score2.

Note: It's straightforward to pull all the Score and Users where Visits.visit_number==1, then again where 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)

The problem is matching up the two sets of scores is a major pain (as they will not be in order by user, and there may be users who haven't yet had a second visit). I'm sure there's an elegant way to do it in SQLAlchemy, probably with aliases.

Upvotes: 0

Views: 2123

Answers (1)

Denis Otkidach
Denis Otkidach

Reputation: 33190

The following code will return the list of (User.id, Score1-Score2) pairs for users that have (at least) 2 visits:

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)

Here Visit.user_id is a column referring to User.id.

In fact it's safe to exclude the table for User from join chain, since Visit's table already has User's id and you don't request other data from its table.

Upvotes: 1

Related Questions