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 On Wed, Feb 23, 2022 at 5:19 PM shuhari2020 <[email protected]> wrote: > > 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! > > -- > 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 [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/3b1eb2d6-1736-41f3-9cd3-29f0cd9af737n%40googlegroups.com. -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexcQQAUt184-NvdZLv1468okgkWRnuBMaSOeMx1o_8DYmA%40mail.gmail.com.
