Filter Query By Linked Object Key In Sqlalchemy

Judging by the title this would be the exact same question, but I can't see how any of the answers are applicable to my use case: I have two classes and a relationship between them

Solution 1:

First, there are two issues with table definitions:

1) In the treatment_association you have Integer column pointing to chronic_treatments.code while the code is String column.

I think it's just better to have an integer id in the chronic_treatments, so you don't duplicate the string code in another table and also have a chance to add more fields to chronic_treatments later. Update: not exactly correct, you still can add more fields, but it will be more complex to change your 'code' if you decide to rename it.

2) In the Animal model you have a relation named treatment. This is confusing because you have many-to-many relation, it should be plural - treatments.

After fixing the above two, it should be clearer why your queries did not work. This one (I replaced treatment with treatments:

sql_query = session.query(Animal.treatments).filter(
    Animal.treatments.code == "chrFlu")

The Animal.treatments represents a many-to-many relation, it is not an SQL Alchemy mode, so you can't pass it to the query nor use in a filter.

Next one can't work for the same reason (you pass Animal.treatments into the query.

The last one is closer, you actually need join to get your results.

I think it is easier to understand the query as SQL (and you anyway need to know SQL to be able to use sqlalchemy):

animals = session.query(Animal).from_statement(text(
    """
       select distinct animals.* from animals
       left join tr_association assoc on assoc.animals_id = animals.id
       left join chronic_treatments on chronic_treatments.id = assoc.chronic_treatments_id
       where chronic_treatments.code = :code
 """)
).params(code='chrFlu')

It will select animals and join chronic_treatments through the tr_association and filter the result by code.

Having this it is easy to rewrite it using SQL-less syntax:

sql_query = session.query(Animal).join(Animal.treatments).filter(
    ChronicTreatment.code == "chrFlu")

That will return what you want - a list of animals who have related chronic treatment with given code.