CODEWITHSUNDEEP
pythonmysqlsqlalchemy
James Lam
James Lam

Reputation: 1269

SQLAlchemy Writing Nested Query

I have two tables:

table A

id | values

and 

table B

id | foreign key to A | datestamp | val2

Given a list of ids for A, how do I get the joined result of A and B with only the rows from B that have the earliest datestamp for each matched A.

For example table B could have: 

1 | 2 | 1/10/2015
1 | 2 | 1/2/2015
1 | 2 | 1/3/2015

I am only interested in the row

id_A | id_B | 1/2/2015 | values | val2

To my understanding in SQL, you can do something like

where timestamp = (select min(timestamp) from B.X where x.id = b.id)

However, how do I nest this select inside a SQLAlchemy query?

For example, I believe I can't just use

.filter(B.timestamp == (query(func.min(B.timestamp)).filter(A.id == B.foreign_key_to_A)))

Upvotes: 1

Views: 3079

Answers (1)

van
van

Reputation: 76962

Actually, you can just use:

B2 = aliased(B, name='b2')
s = session.query(func.min(B2.datestamp)).filter(B2.a_id == A.id)
q = (session
     .query(A.id, B.id, B.datestamp, A.values, B.val2)
     .join(B)
     .filter(B.datestamp == s)
     )

Upvotes: 3

Related Questions