I'm using GoogleSQL. DAL sometimes seems to have some nuances there compared to MySQL. I think your suggestion will probably work. I was just as confused at the ~ operator working in groupby as you!
Thanks! On Monday, 27 August 2018 20:21:22 UTC-7, Anthony wrote: > > 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.
