SQLAlchemy joins table from subquery. How to prevent it of doing this?

user_5378129 0
Viewed 44

I have this subquery:

aliasBilling = aliased(Billing)

subQueryReversesBilling = db.session.query(BillingReversal).select_from(BillingReversal).with_entities(BillingReversal.reverses_billing_id).filter(BillingReversal.billing_id == aliasBilling.id).subquery()

Which generates:

SELECT billing_reversal.reverses_billing_id
FROM billing_reversal, billing AS billing_1
WHERE billing_reversal.billing_id = billing_1.id

How to get rid of that join(comma style) I didn't specified as it makes a join for the full table and every record. Apparently it is assuming I need it as it is in the filtering part(where clause), but I take that table from the bigger query I have later on. That is why it is a subquery. What I need is this:

SELECT billing_reversal.reverses_billing_id
FROM billing_reversal
WHERE billing_reversal.billing_id = billing_1.id
flask-sqlalchemy sqlalchemy postgresql orm python
1 Answers

The cross join is coming from the aliased Billing table in the filter clause.

If BillingReversal and Billing are linked with a relationship (and a foreign key), you should leverage this relationship and join instead of filter.

subQueryReversesBilling = (
    db.session.query(BillingReversal)
        .join(Billing, BillingReversal.billing)
        .with_entities(BillingReversal.id)
        .subquery()
)
user_23079340
Related