Issue "translating" raw SQL to SQLAlchemy ORM query

[shuhari2020]

FROM: https://stackoverflow.com/questions/71225408/issue-translating-raw-sql-to-sqlalchemy-orm-query

I have the following raw SQL statement that I am having trouble "translating" into a SQLAlchemy query:

(the hardcoded value 38 is just for testing)

SELECT * FROM public.data_appquestion AS question 

/* ANSWER JOIN */ 

LEFT JOIN (SELECT * FROM public.data_appanswer) AS answer 

ON ( answer.separation_app_question_id = question.id AND answer.is_active = true 

AND answer.separation_app_session_id = 38 ) 

/* OPTION XREFS JOIN */ 

LEFT JOIN (SELECT * FROM public.data_appansweroptionxref) AS options_xref 

ON ( options_xref.separation_app_answer_id = answer.id ) 

/* OPTIONs JOIN */ 

LEFT JOIN (SELECT * FROM public.data_appoption) AS answered_option 

ON ( options_xref.separation_app_option_id = answered_option.id ) 

/* UPLOAD JOIN */ 

LEFT JOIN (SELECT * FROM public.data_appfileupload) AS uploads 

ON ( uploads.separation_app_answer_id = answer.id ) 

WHERE question.is_active = true 

AND answer.is_active = true OR answer.is_active = NULL 

AND options_xref.is_active = true OR options_xref.is_active = NULL 

AND uploads.to_delete = false OR uploads.to_delete = NULL 

ORDER BY question.id;

I have tried something like this, but the "filter" statement already does not seem to work as I need it to:

db_questions = db.query(models.AppQuestion).\

filter(models.AppQuestion.is_active == True).\

outerjoin(models.AppAnswer, and_( models.AppAnswer.app_question_id == models.AppQuestion.id, models.AppAnswer.app_session_id == 38 ) ).\

outerjoin(models.AppAnswer.app_options).\

outerjoin(models.AppAnswerOptionXref.app_option).\

outerjoin(models.AppFileUpload.app_question).\

order_by(asc(models.AppQuestion.order_number)).all()

The models all have the relevant "relationship" entries, so there is no issue for the query to find the relevant models via their foreign keys. The issue is that they are not filtered as they are in the raw SQL.

My result includes a joined "AppAnswer", but it does not filter it according to ```app_session_id == 38````

I'm not very familiar with joining SQL queries and usually working with the Django ORM, which never had me run into an issue like this.

Let me know if I need to add more info and thanks a lot for any replies!

[Simon King]

By default, relationship loading is deliberately not affected by your
join conditions. If you want a relationship property to be restricted
to the rows you've selected in your query, you need to use the
"contains_eager()" query option:

https://docs.sqlalchemy.org/en/14/orm/loading_relationships.html#using-contains-eager-to-load-a-custom-filtered-collection-result

Hope that helps,

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3b1eb2d6-1736-41f3-9cd3-29f0cd9af737n%40googlegroups.com.