Reputation: 103
Say I have the following SQL code and I want to change it to Sqlalchemy:
SELECT amount FROM table1
JOIN table2
ON table2.id = table1.b_id
JOIN (SELECT id FROM table3 WHERE val1 = %s AND val2 = %s) inst
ON inst.id = table1.i_id
WHERE
val3 = %s
I've tried making a subquery for the SELECT id FROM table3 clause as follows:
subq = session.query(table3.id).filter(and_(table3.val1 == 'value', table3.val2 == 'value')).subquery()
And then putting everything together:
query = session.query(table1).join(table2).filter(table2.id == table1.b_id).\
join(subq).filter(table1.val3 == 'value')
When I ouput query.first().amount, this works for a few examples, but for some queries I'm getting no results when there should be something there, so I must be messing up somewhere. Any ideas where I'm going wrong? Thanks
Upvotes: 1
Views: 1308
Reputation: 77092
Query below should produce exactly the SQL
you have. It is not much different from your, but removes some unnecessary things.
So if it does not work, then also your original SQL
might not work. Therefore, I assume that your issue is not SQL
but either data or the parameters for that query. And you can always print out the query itself by engine.echo = True
.
val1, val2, val3 = 'value', 'value', 'value' # @NOTE: specify filter values
subq = (session.query(table3.id)
.filter(and_(table3.val1 == val1, table3.val2 == val2))
).subquery(name='inst')
quer = (
session.query(table1.amount) # @NOTE: select only one column
.join(table2) # @NOTE: no need for filter(...)
.join(subq)
.filter(table1.val3 == val3)
).first()
print(quer and quer.amount)
Upvotes: 1