Raw SQL to SQLAlchemy

Question

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?

Answer 1

you need to use outerjoin for LEFT JOIN

Answer 2

LEFT JOIN and JOIN are different operations.

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