Using the SQLAlchemy ORM to filter a query based on one object in a many-to-one relationship

Question

So I have a table carts, and another table cart_items. A cart is connected to a cart item like so (in pseudo code).

cart: cart_id = 1

cart_item: id = 12, cart_id = 1, in_stock = False

cart_item: id = 13, cart_id = 1, in_stock = False

cart_item: id = 14, cart_id = 1, in_stock = False

I want to query all carts that have at least one cart_item with an in_stock value of True.

cart: cart_id = 1

cart: cart_id = 2

cart_item: id = 12, cart_id = 1, in_stock = False

cart_item: id = 13, cart_id = 1, in_stock = False

cart_item: id = 14, cart_id = 1, in_stock = False

cart_item: id = 15, cart_id = 2, in_stock = True

cart_item: id = 16, cart_id = 2, in_stock = False

cart_item: id = 17, cart_id = 2, in_stock = False

So given this dataset a cart with cart_id = 2 would be returned.

Also, I should mention I know I could query and retrieve all the objects and then operate on that instrumented list to filter out all that don't match this requirement, but I actually need to retrieve this data in the form of a query due to how pagination is handled in this web application.

If there's some sort of property I could define in the model like below, that would be ideal. But I'm certain this doesn't work as it stands.

@property
def cart_has_in_stock(self):
    has_in_stock = False
    for item in self.cart.items:
      if item.in_stock:
        has_in_stock = True
    return has_in_stock

I just figured out a plain SQL query that does this, I just need help moving it over to SQLAlchemy now. I'm not too excellent with SQLAlchemy.

Query: select * from cart_items where cart_items.cart_id IN (select cart.id from cart_items as item INNER JOIN carts AS cart on item.cart_id = cart.id where item.stage = 'stock' group by cart.id) \G;

Answer 1

I figured it out.

sub_q = model.Session.query(model.Cart.id).\
        join(model.CartItem, model.Cart.id == model.CartItem.cart_id).\
        filter(
            model.CartItem.stage == 'stock'
        ).\
        group_by(model.Cart.id).\
        subquery()

correct_q = model.Session.query(model.Cart).filter(model.Cart.id.in_(sub_q))