I wrote a similar query in SQLObject - in one query it retrieves the most
recent discount for every order in the list orderIDs. The code is below; it
should be pretty easy to translate to SQLAlchemy.
The technique is to use nested SQL queries - the innermost query uses MAX to
determine the most recent change time, the middle query selects the highest-ID
record with change time, and the outermost query selects the interesting data
from the resulting set of records.
@classmethod
def get_discounts(klass, orderIDs):
'''From a list of orderIDs, returns a dictionary keyed by order ID
containing the discount for the orders. Runs a query, so should
be much faster than working with objects.
'''
# Build the discount dictionary. If no discount record found, discount
is 0.
discount = { }
for o in orderIDs:
discount[o] = 0
# Work around a bug in some databases. Apparently some versions of
# PostgreSQL cannot handle an empty "IN" clause.
if len(orderIDs) == 0:
return discount
# Innermost query: Highest change time among the discounts for each
order.
od2 = Alias(OrderDiscount)
q1 = Select(func.MAX(od2.q.changeTime), where=[od2.q.orderID ==
OrderDiscount.q.orderID])
# Second query: In case of a tie on change time, multiple records will
# be selected per order. Select the max ID in case of a tie.
q2 = Select(
func.MAX(OrderDiscount.q.id),
where=AND(OrderDiscount.q.changeTime == q1,
IN(OrderDiscount.q.orderID, orderIDs)),
groupBy=OrderDiscount.q.orderID)
q3 = Select(
[ OrderDiscount.q.orderID, OrderDiscount.q.percent_off ],
where=IN(OrderDiscount.q.id, q2))
conn = OrderDiscount._connection
for od in conn.queryAll(conn.sqlrepr(q3)):
discount[od[0]] = od[1]
return discount
----- Original Message ----
From: Noam <[EMAIL PROTECTED]>
To: sqlalchemy <[email protected]>
Sent: Wednesday, May 16, 2007 1:21:37 AM
Subject: [sqlalchemy] SQL newbie question: How to select the last message for
each user
Hello,
I'm pretty much an SQL newbie, so I don't know how to do this in SQL,
but I want to do it in SQLAlchemy, so I hope you'll agree to help.
I have a users table and a messages table. Each message belongs to a
user. Each message has a date. I want to select the last message for
each user, according to the date (actually I'm only interested in the
date). I know I can first select all users and then issue a select for
each user to get the last message. But is it possible to do it with
only one select, to save DB queries?
Thanks,
Noam
____________________________________________________________________________________Looking
for a deal? Find great prices on flights and hotels with Yahoo! FareChase.
http://farechase.yahoo.com/
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" 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/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
