I’ve encountered a common problem that appears when switching from MySQL to PostgreSQL, related to the following error:
“column X must appear in the GROUP BY clause or be used in an aggregate function”
I’ve found some posts that explain why it does happen and how to correct it.
But I wonder why Rails does not handle it through its DB adapters. I’m using query methods like #group, #join etc.
Isn’t it the reason why we use ORM?
Or maybe I’m doing something wrong?
Can you post your code/query here?
Entry.joins(:program => :user).group(‘programs.user_id’) -> works in MySQL, not Postgresql
Entry.joins(:program => :user).select(‘distinct on (programs.user_id) entries.*’) -> works in pg, not mysql
and an entry belongs to a program.
I’d like all entries with only one occurrence by user (program_id could also do the trick I guess, but that is not the point).
Did some search on it and it’s a revelation for me too.
Here’s what I understand based on all the articles I’ve read:
It’s not the problem with DB adapters, it’s because of the difference in how MySQL implements “group by” clause
Strictly speaking, both the adapters are implementing what’s right as per each database.
And since MySQL chose to implement “group by” clause differently and most rails devs have been using mysql as default database, we are used to use “group by” the mysql way.
Actually, MySQL defaults to allowing you to do non standard group by
As the link points out, the following query is allowed when the
ONLY_FULL_GROUP_BY option is not enabled (which I think is the
SELECT name, address, MAX(age) FROM t GROUP BY name;
I've always thought this was a really bad default for MySQL, and
PostgreSQL is rightfully complaining that the query should be:
SELECT name, address, MAX(age) FROM t GROUP BY name, address;