book: GROUP BY example not working with Postgres

[alfonsodg]

From ehochi...@gmail.com on February 06, 2011 05:50:51

The example currently at http://www.web2py.com/book/default/chapter/06#Grouping-and-Counting >>> count = db.person.id.count()

for row in db(db.person.id==db.dog.owner).select(db.person.name, count, groupby=db.person.id): print row.person.name, row[count] Alex 2 Bob 1

is translated by the DAL into SELECT person.name, count(person.id) FROM person, dog WHERE (person.id = dog.owner) GROUP BY person.id;

This works with SQLite, but throws a SQL error with Postgres, since person.name is neither an argument to an aggregate function nor appearing in the GROUP BY clause. What is the expected output? What do you see instead? From the ticket (error message from Postgres 9 below is in German):

ProgrammingError: FEHLER: Spalte »person.id« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden LINE 1: SELECT person.id, count(person.id) FROM person, dog WHERE (... What version of the product are you using? On what operating system? web2py Version 1.91.6 (2011-01-03 17:55:14) Windows 7 Professional (64bit) ActivePython 2.7.0.2 (ActiveState Software Inc.) based on Python 2.7 ( r27 :82500, Aug 23 2010, 17:17:51) [MSC v.1500 64 bit (AMD64)] PostgreSQL 9.0.1 Please provide any additional information below. Postgres is picky about SQL and SQL has restrictions on grouped queries. Imho the trivial fix would be to add person.name to the GROUP BY clause. Unfortunately the current DAL does not support grouping on multiple fields. :-( DAL support for sequences (tuples, lists) passed as "groupby" parameter is a nice-to-have.

Original issue: http://code.google.com/p/web2py/issues/detail?id=181

[alfonsodg]

From massimo....@gmail.com on February 06, 2011 07:34:06

Thanks for reporting this. The error is in the book example, not in web2py

Status: Invalid

[alfonsodg]

From caleb.ha...@gmail.com on February 06, 2011 23:40:08

Fixed the book like so:

for row in db(db.person.id==db.dog.owner).select(db.person.name, count, groupby=db.person.name): print row.person.name, row[count]

Please confirm if correct.

[alfonsodg]

From ehochi...@gmail.com on February 12, 2011 07:49:52

This fixes the PostgreSQL error and unbreaks the book example - but it is not a correct solution of the stated problem. Consider the case where you have two or more persons with equal name "Alex" and different ids. The query would report only one "Alex" with a dog count that is the sum of all "Alex" person's dog counts.

The restriction that the current DAL cannot group by (id, name) remains. You could work around it e.g. with for row in db(db.person.id == db.dog.owner).select(db.person.id, count, groupby=db.person.id): print db.person(row.person.id).name, row[count] but that is ugly and generates an extra query for each dog owner.

[alfonsodg]

From caleb.ha...@gmail.com on February 12, 2011 09:18:09

My primary concern is the accuracy of the book. Other people focus on the code. FWIW I tend to agree with you that "groupby" should probably be amended to take a sequence, if for no reason other than it is simple to do, makes immediate sense, and it is easy to understand the effect on the resulting SQL.

[alfonsodg]

From caleb.ha...@gmail.com on February 12, 2011 11:59:45

Please test attached patch. It gives groupby the same handling as we currently have for orderby. I have not tested it yet.

Attachment: groupby_list.diff

[alfonsodg]

From massimo....@gmail.com on February 12, 2011 14:58:07

Thanks, I included your patch. Sorry for the misunderstanding.:-)

Status: Fixed

[alfonsodg]

From ehochi...@gmail.com on February 13, 2011 01:01:20

I tried the patch. The snippet

count = db.person.id.count() for row in db(db.person.id == db.dog.owner).select(db.person.name, count, groupby=(db.person.id, db.person.name)): print row.person.name, row[count]

works with PostgreSQL 9 and translates to

SELECT person.name, count(person.id) FROM person, dog WHERE (person.id = dog.owner) GROUP BY person.id, person.name;

[alfonsodg]

From simonjc...@gmail.com on August 07, 2013 04:03:24

so if I only want data from the person table and want to group by person.name what do I do? If I include person.id in the groupby statement, then I effectively remove the groupby. If I don't add person.id to the groupby then I cant use groupby, except with very limited use cases.

[alfonsodg]

From massimo....@gmail.com on August 07, 2013 04:07:03

This patch has been removed from trunk already. It was a mistake.