*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 sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/3b1eb2d6-1736-41f3-9cd3-29f0cd9af737n%40googlegroups.com.
