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.

Reply via email to