On Jul 24, 7:19 pm, "Michael Bayer" <[email protected]> wrote:
[..]
> finding a list of objects and the "most recent"/"highest"/"<something>est"
> related item requires joining to a subquery, where the subquery selects
> the func.max(desiredfield) and GROUP BY's the columns that relate the rows
> to the parent.
>
> An example of that is in the ORM tutorial under "Using Subqueries".
> Replace func.count() with func.max() and you're on your way.
I'm sorry about such a late follow up. I ended up working on something
else and couldn't try this out.
I spent a while trying to crack this but couldn't.
My mappers are Client and Order and I first create the subquery like
so.
stmt = session.query(Order.table.c.client_id,func.max
(Order.table.c.date).label('latest_order')).group_by
(Order.table.c.date).subquery()
Which selects the order with the maximum date.
I then build my complete query like so
for client, lo in session.query(Client, stmt.c.latest_order).filter
(Client.id == 16983).join((stmt,Client.id ==
stmt.c.client_id)).order_by(Client.id): print client,lo
(The filter is there just to limit the clients to a single one)
I get back a all the orders for the client which seems to make sense
from the SQL standpoint but it's not what I want.
I get back something like
Client#16983 date0
Client#16983 date1
Client#16983 date2
which are the 3 orders that the client has placed.
What I want is the latest one only. ie.
Client#16983 date2
Can't I get just these?
Thanks.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
