2008/9/4 Chris Curvey <[EMAIL PROTECTED]>: > > I have a query that gets me all the orders placed from NJ. It looks > like this, and it works fine > > q = PurchaseOrder.query.filter_by(state_code = 'NJ') > > But now I realize that what I really want is the latest order for each > customer that was placed in NJ. So in SQL, I want something like > > select po.* > from purchase_order po > where po.state_code = 'NJ' > and po.order_date = > ( select max(po2.order_date) > from purchase_order po2 > where po2.customer_id = po.customer_id) > > but I can't figure out a way to do this with filter_by() or filter(). > > Is there a way, or do I need to change my strategy?
I'm not sure you need a subquery for that. Wouldn't a join with an aggregate be OK? In SQL something like: select max(purchase_orders.order_date) from customers, purchase_orders where customers.id = purchase_orders.customer_id and purchase_orders.state_code = 'NJ' group by customers.id; Using SQLAlchemy that would be something like: q = session.query(PurchaseOrder, func.max(PurchaseOrder.order_date)) q = q.join(PurchaseOrder.customer).filter(PurchaseOrder.state_code=='NJ') q = q.group_by(Customer.id) That would return a list of tuples which first element would be your PurchaseOrder instances. HTH Alex --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "SQLElixir" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlelixir?hl=en -~----------~----~----~----~------~----~------~--~---
