On Tuesday, August 28, 2018 at 12:18:24 AM UTC-4, Robert Porter wrote:
>
> 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!
>
OK, then I assume you are using the MySQL storage option of Google Cloud
SQL. It turns out MySQL does support ASC/DESC with GROUP BY, though it
sorts on the grouping column *after *the grouping (i.e., similar to adding
an ORDER BY clause to the query), which does not do what you want. Also, in
standard SQL, you cannot select fields unless they are either in the GROUP
BY clause or are aggregated. However, by default, MySQL does allow
selecting of such fields (though if such fields contain non-unique values
per group, an arbitrary value will be selected). In your original query,
you selected all fields, including the "id" field, so after grouping by
item_id, MySQL would select an arbitrary "id" value to associate with each
item_id. Presumably by luck this turns out to be the most recent id for
each item_id in your case, which is why your query worked.
Anyway, because MySQL does allow selecting non-aggregated/non-group-by
field, and because the referenced db.item record is unique per
db.checkout.item_id field, you can simplify the query to:
rows = db((db.checkout.user_id == 1) &
(db.checkout.item_id == db.item.id)).select(db.checkout.id.max(),
db.checkout.item_id, db.item.name,
groupby=db.checkout.
item_id)
Note, this will work in MySQL but will likely fail in other databases, so
the other query will be more portable.
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.