>
> WITHOUT the join works perfectly, giving me the last entry for each item
> checked out by this user:
>
> db(db.checkout.user_id == 1).select(groupby=~db.checkout.item_id)
>
What database are you using? The ~ operator is for adding "DESC" to an
"ORDER BY" clause -- it is not relevant to "GROUP BY" and should be
generating an error. Also, to get the most recent record for each item_id,
you would want to order by the "id" field (or a datetime field), not the
"item_id" field (you are grouping by item_id, so you want to order records
within item_id groups and select the most recent). In any case, if you are
getting the records you want, it is probably by chance.
Instead, you probably want something like:
max_id_per_item = db(db.checkout)._select(db.checkout.id.max(), groupby=db.
checkout.item_id)
rows = db((db.checkout.user_id == 1) &
(db.checkout.item_id == db.item.id) &
db.checkout.id.belongs(max_id_per_item)).select()
Above, max_id_per_item selects the maximum id within each item_id, and it
is used in a nested select (see
http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#belongs)
to select only those records from db.checkout.
Anthony
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
