SQLAlchemy - Filter on Subqueryload

Question

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:

• Order to Shipment is a One-to-Many relationship.
• As per business rules, although there maybe many shipments associated with an order, there can only be one "active" shipment. ie. all other shipments will have their is_refunded status set to True
• Thus, I am only interested in referring to the shipment that is "active" when iterating over all orders.
• The standard results returned by subqueryload load and return all shipments
• I am using the latest stable version of SQLAlchemy.

Please let me know if any clarifications are needed.

Thanks in advance.

Marbleize

Answer 1

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.

Ectomere

Thank you very much! I'm hoping this might be added to SQA core in the near future, but for now, manually setting this up on the relationship works (albeit in a limited fashion) just fine. DisjointEagerLoading also seems promising for more robust use cases. – Marbleize

Interesting API suggested by @Brownbay. Do you think it would be feasible to implement something like this @zzzeek? I love the convenience of using contains_eager, but sometimes it's just too expensive to use when working with many levels of relationships. – Nepean

it could be done but it would be a large effort, in contrast to not much demand for it. SQLA's development now is just barely keeping pace with Postgresql's constant new features. – Ectomere

Answer 2

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()

Convertiplane