In sqlalchemy, how can I combine two queries by having a column entry identical?
14,997
After fiddling around and reading answers to questions about subqueries, I managed to find a solution. Instead of the last, offending line, put:
q1.join(s, a1.age==s.columns.age).all()
That way, the on-clause becomes ON user_1.age = anon_1.age
, which is what we want.
Author by
Turion
Updated on June 14, 2022Comments
-
Turion about 2 years
Suppose I have a mapped class
User
, mapped to a tables of the same name, and a column "age" for his age. I'm now interested in the following problem:In the course of my application, there emerge two queries:
q1 = session.query(User).filter(lots of conditions) q2 = session.query(User).filter(lots of other conditions)
I now want to "join" q2 onto q1 upon the condition that they have the same age. But I have no idea of how this might work. I tried the following without success:
q1.join(q2.subquery(), q1.age==q2.age) # the query doesn't hold the columns of the queried class q1.join(Age).join(q2.subquery()) # Works only if age is a relationship with mapped class Age
My closest calls were something like this:
a1 = aliased(User) a2 = aliased(User) q1 = session.query(a1) q2 = session.query(a2) s = q2.subquery() q1.join(s, a1.age==a2.age).all() >>> sqlalchemy.exc.OperationalError: (OperationalError) no such column: user_2.age 'SELECT user_1.id AS user_1_id, user_1.name AS user_1_name, user_1.age AS user_1_age \nFROM user AS user_1 JOIN (SELECT user_2.id AS id, user_2.name AS name, user_2.age AS age \nFROM user AS user_2) AS anon_1 ON user_1.age = user_2.age' ()
Any ideas about how to make this run?
-
Turion over 11 yearsWhat exactly does it complain about? To me, it looks like the ON clause wants to refer to user_2, which is hidden away in the subquery. It should want to refer to anon_1 instead. Is that a bug in sqlalchemy?
-
-
Turion over 7 yearsTo whoever tried to edit this post: I don't think that
a1
should beq1
, but I haven't used SQLAlchemy in 5 years, so I might be wrong. Please comment on what the problem with the above code is. -
Nukesor almost 6 yearsThe join is actually correct, since
a1
is the aliasedUser
fromq1
. The second parameter in the join call specifies theon
clause of the join.