Postgresql vs mysql request with Rails 3

Rails
1

Hello.

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?

Thanks.

2

Can you post your code/query here?

3

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).

4

On Jun 23, 8:39am, Chirag S. [email protected] wrote:

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
operations.
http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_only_full_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
default):
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;

  • Eric
5

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

http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

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.