group by error with postgres and pomm orm

Question

I want to execute the following SQL query :

SELECT date, COUNT(id_customers) 
FROM event 
WHERE event_id = 3  
GROUP BY date

When I try this query in my database, it works perfectly. But in my code I get an error which I can't resolve.

I use symfony2 with the orm pomm. It's Postgresql.

Here is my code :

$sql = "SELECT e.date, COUNT(id_customers) FROM event e WHERE event_id = $* GROUP BY e.date";

return $this->query($sql, [$eventId])->extract();

Here is the error :

request.CRITICAL: Uncaught PHP Exception InvalidArgumentException:
"No such field 'id'. Existing fields are {date, count}"
at /home/vagrant/sourcefiles/vendor/pomm-project/model-manager/sources/lib/Model/FlexibleEntity/FlexibleContainer.php line 64
{"exception":" [object] (InvalidArgumentException(code: 0): No such field 'id'.
Existing fields are {date, count}
at /home/vagrant/sourcefiles/vendor/pomm-project/model-manager/sources/lib/Model/FlexibleEntity/FlexibleContainer.php:64)"} []

So I tried to had the id in my select, by I get this error :

request.CRITICAL: Uncaught PHP Exception
PommProject\Foundation\Exception\SqlException: "
SQL error state '42803' [ERROR] ==== ERROR: column "e.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT e.id, e.date, COUNT(id_customers) FROM event e WHERE ... ^

==== «PREPARE === SELECT e.id, e.date, COUNT(id_customers) FROM event e WHERE event_id = $1 GROUP BY e.date ===»." at /home/vagrant/sourcefiles/vendor/pomm-project/foundation/sources/lib/Session/Connection.php line 327 {"exception":"[object] (PommProject\Foundation\Exception\SqlException(code: 0): \nSQL error state '42803' [ERROR]\n====\nERROR: column \"e.id\" must appear in the GROUP BY clause or be used in an aggregate function\nLINE 1: SELECT e.id, e.date, COUNT(id_customers) FROM event e WHERE ...\n ^\n\n====\n«PREPARE ===\nSELECT e.id, e.date, COUNT(id_customers) FROM event e WHERE event_id = $1 GROUP BY e.date\n ===». at /home/vagrant/sourcefiles/vendor/pomm-project/foundation/sources/lib/Session/Connection.php:327)"} []

The only thing that works is when I had the id in the group by, but this is not the result I want.

Someone can explain me why this is working in the database and not in the php ?

score 1 · Accepted Answer · answered Dec 28 '16 at 12:48

this is because you are fetching flexible entities without their primary key. There is an identity mapper behind the scene that ensure fetching twice the same entity will return the same instance.

In this case, you do not need to fetch entities (hence the extract after the query). So you can just use the QueryManager pooler to return converted arrays like the following:

$sql = "SELECT e.date, COUNT(id_customers) FROM event e WHERE event_id = $* GROUP BY e.date";

// Return an iterator that fetches converted arrays on demand:
return $this
    ->getSession()
    ->getQueryManager()
    ->query($sql, [$eventId])
;

score 0 · Answer 2 · answered Dec 27 '16 at 13:42

0

i think its because the alias,

try this

$sql = "SELECT e.date, COUNT(e.id_customers) FROM event e WHERE event_id = $* GROUP BY e.date";

return $this->query($sql, [$eventId])->extract();

answered Dec 27 '16 at 13:42

user3600910

2,839
4
22
36

score 0 · Answer 3 · answered Dec 27 '16 at 14:31

0

This is exactly how GROUP BY works in PostgreSQL:

When GROUP BY is present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions, since there would be more than one possible value to return for an ungrouped column.

It means that each field in your query either must be present in GROUP BY statement or handled by any of the aggregation functions. This is one of the differences between GROUP BY in MySQL and PostreSQL.

In other words you can add id at GROUP BY statement and do not worry about it ;)

answered Dec 27 '16 at 14:31

Andrii Mishchenko

2,626
21
19

thanks for your answer. but i don't understand why it works in the database. Furthermore i can't add id in group by because it change the result i want. And i don't understand why i have to put id while i don't even use it in select. where does this come from ? – t-n-y Dec 27 '16 at 15:14
hmm not sure, I guess you need to dig deeper and debug FlexibleContainer::fields method – Andrii Mishchenko Dec 27 '16 at 15:33

group by error with postgres and pomm orm

3 Answers3