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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/j8q-hT6ODEAJ. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Can you post your code/query here? -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/3J8xFe8GX8sJ. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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).
--
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/rubyonrails-talk/-/zJTQ8-xY8FQJ.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
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. -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/rubyonrails-talk/-/jjD_Rq-e200J. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
On Jun 23, 8:39 am, Chirag Singhal <chirag.sing...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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 -- You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.