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;
I figured it out.
correct_q = model.Session.query(model.Cart).filter(model.Cart.id.in_(sub_q))