SQLAlchemy/SQL: Filter table, then join to query

I need to filter down some results before joining them. I have three models: ModelA, ModelB, and ModelC. ModelA and ModelB both have a relationship to ModelC and they both have an organization_id, which always needs to be filtered down. ModelB doesn’t have an organization_id (the data is shared across all organizations).

I need ModelB joined onto a query that already includes ModelA. The join should only included items where ModelA.organization_id == ModelB.organization_id, ModelB.model_c_id == ModelA.model_c_id, and ModelB is not expired.

My plan is to filter ModelB down by expiration and organization_id, then use a subquery() to join the filtered results to the main query.

I have the following Python code:

        query = ModelA.query().filter_by(ModelA.organization_id == organization_id)  # simplified for this post
        sub = ModelB.objects.query()
        sub = sub.filter(
            ModelB.organization_id == organization_id
        )
        sub = ModelB.subquery()
        query = query.join(
            sub, sub.columns.model_c_id == ModelA.model_c_id
        )

This is the resulting SQL from the debugger (without most of the AS statements):

    SELECT *
    FROM parcels JOIN recipients ON recipients.id = parcels.recipient_id JOIN senders ON senders.id = parcels.sender_id
    JOIN (SELECT *
      FROM recipient_watch_lists 
      WHERE recipient_watch_lists.organization_id = %(organization_id_1)s)
    WHERE recipient_watch_lists.organization_id = %(organization_id_1)s) AS anon_1 ON anon_1.recipient_id = parcels.recipient_id 

This seems like roughly what I want. All other code is well-tested and working properly when I comment out this code, so it seems that the problem is with the above code. Any ideas what could be wrong?

Answer

It could be a problem with sub = ModelB.subquery(). The code lead to an error, but is also wrong logically because it takes out the constraint for org_id.

I replaced it with sub = sub.subquery() and the code worked for me:

query = ModelA.query.filter(ModelA.org_id == 2)
sub = ModelB.query.filter(ModelB.org_id == 2)
sub = sub.subquery()
query = query.join(sub, sub.columns.id_C == ModelA.id_C)

I also received an error for ModelB.objects so that I combined two lines of your code into one. “2” was the example of the requested org_id.