CODEWITHSUNDEEP
pythonpython-3.xsqlalchemy
Brownbay
Brownbay

Reputation: 5500

SQLAlchemy - Filter on Subqueryload

Assuming the following models, how exactly would one go about loading the shipment associated with the order via a subqueryload, filtered by the status of is_refunded?

class Order(db.Model):
    id = db.Column(db.Integer, primary_key=True)

class Shipment(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    shipment = db.Column(MutableDict.as_mutable(JSONB))
    is_refunded = db.Column(db.Boolean, server_default="false")
    order_id = db.Column(db.Integer, db.ForeignKey('order.id'))
    order = db.relationship('Order', backref='shipments')

I was hoping for something along the following lines, but this is not valid syntax:

orders = db.session.query(Order).options(
    db.subqueryload(Order.shipments).filter(Shipment.is_refunded==False))

Some background:

Please let me know if any clarifications are needed.

Thanks in advance.

Upvotes: 5

Views: 3783

Answers (2)

Bekzod
Bekzod

Reputation: 96

with SQLAlchemy 2 async session you could do it like:

from sqlalchemy import select
from sqlalchemy.orm import selectinload


statement = select(Order).options(selectinload(Order.shipments.and_(Shipment.is_refunded.is_(False))))

result = await db.session.execute(statement)
orders = result.scalars().all()

Upvotes: 0

zzzeek
zzzeek

Reputation: 75117

interestingly I've been wondering about an easy "options" way to do this kind of thing so the syntax you have above is intriguing.

However, at the moment, the contract of attributes set up via a relationship() is that they only use the criteria that is set up in the relationship() directly. There's no simple way to change the criteria on the fly while still making use of the loader services provided.

The two options to make direct use of loader services with custom criteria are to either write out the query using joins that you then combine with contains_eager (which applies to joinedload, not really subqueryload), or to use a new relationship() that satisfies the criteria you're looking for (which you'd establish via primaryjoin).

Another option exists for subqueryload, which is to emit the same queries yourself but not actually use the subqueryload option. The special technique here is being able to populate the collections with the results of a query such that these aren't recorded as change events. The set_committed_value function is used for this, and an example which illustrates the "original" form of subqueryload (before it was built in) illustrates this, over at DisjointEagerLoading. Rolling your own "subqueryload" against fixed entities is not very hard for the vast majority of simple cases, and the technique is illustrated in that example.

Upvotes: 7

Related Questions