CODEWITHSUNDEEP
pythonmysqlsqlflasksqlalchemy
asyndrige
asyndrige

Reputation: 592

Raw SQL to SQLAlchemy

Part of my raw sql statement looks like this:

select /*some selects*/
if(/*condition*/, table1.price , if(/*condition*/, t2.price, t3.price)) as price
/*some joins*/
left join table2 t2 on table1.type=t2.id
left join table3 t3 on table1.type=t3.id

This statement works as expected.

SQLAlchemy ORM:

query = db_session.query(Table1,\
                         func.IF(Table1.field5 == 5, Table1.price,\
                         func.IF(Table1.new_model == 1, Table2.price, Table3.price)) 
#+some selects
#+some joins
query = query.join(Table2, Table1.type == Table2.id)\
             .join(table3, Table1.type == Table3.id)

And it doesn`t work the same way. It returns the result that only connected to the Table2. And not using this joins in query returns needed rows, but without needed fields from this Table2 and Table3, of course.

What is my mistake?

Upvotes: 1

Views: 337

Answers (2)

philipxy
philipxy

Reputation: 15156

LEFT JOIN and JOIN are different operations.

For LEFT JOIN use outerjoin. For JOIN (aka INNER JOIN) use join.

Upvotes: 0

r-m-n
r-m-n

Reputation: 15120

you need to use outerjoin for LEFT JOIN

Upvotes: 2

Related Questions