In sqlalchemy, how can I combine two queries with an identical column record?

Question

Suppose I have a mapped User class mapped to tables with the same name and age column for its age. I am currently interested in the following problem:

During my application, two requests appear:

 q1 = session.query(User).filter(lots of conditions) q2 = session.query(User).filter(lots of other conditions) 

Now I want to “attach” q2 to q1, provided that they are of the same age. But I have no idea how this can 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 on how to make this run?

Answer 1

After scrolling and reading the answers to the questions about the subqueries, I managed to find a solution. Instead of the last, offensive line, put:

 q1.join(s, a1.age==s.columns.age).all() 

Thus, on-clause becomes ON user_1.age = anon_1.age , which is what we want.