Mohammed Alenazi
2010-Aug-12 02:40 UTC
Problem with changing from mySql to PostrgreSql with rails
I have this statement running Ok in my development mode @users = User.paginate :joins => :properties ,:group => ''users.id'', :page => params[:page], :order => ''count(properties.id) DESC'',:conditions => [''users.id != 1''] when I push my app to Heroku it gives me this error column "users.email" must appear in the GROUP BY clause or be used in an aggregate function : SELECT "users".* FROM "users" INNER JOIN "properties" ON properties.user_id = users.id WHERE (users.id != 1) GROUP BY users.id,users.login,users.name ORDER BY count(properties.id) DESC LIMIT 10 OFFSET 0): by doing some research on Google, I found out that it''s a DB Engin problem. MySql allows me to do it but PostgreSql does not. How can I run the same query on PostgreSql. I am trying to get the users ordered by the users with highest properties. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Dave Aronson
2010-Aug-12 13:40 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
On Wed, Aug 11, 2010 at 22:40, Mohammed Alenazi <vb4max-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I have this statement running Ok in my development mode > @users = User.paginate :joins => :properties ,:group => > ''users.id'', :page => params[:page], :order => ''count(properties.id) > DESC'',:conditions => [''users.id != 1''] > > when I push my app to Heroku it gives me this error > > column "users.email" must appear in the GROUP BY clause or be used in > an aggregate function > : SELECT "users".* FROM "users" INNER JOIN "properties" ON > properties.user_id = users.id WHERE (users.id != 1) GROUP BY > users.id,users.login,users.name ORDER BY count(properties.id) DESC > LIMIT 10 OFFSET 0):I haven''t run into this sort of thing, but from my general SQL/database knowledge, I''d guess that the grouping is causing the problem. I assume users.id is unique. Is that correct? If so, then grouping on it (:group => ''users.id'') is useless. Worse than that, though, it''s making the DBMS think that anything else that would probably vary from row to row, must either be aggregated up to the level of your chosen group, or chosen as another grouping level. Otherwise it won''t know what to do with it. Alternately of course you could omit it, picking specific attributes rather than users.*. Rather than aggregating or grouping on the email, it would be easier and cleaner to just stop grouping by something else (i.e., users.id). Try that and let us know what happens. (So why the difference? My guess would be that MySQL realizes that grouping on something unique (I''d bet the column is even described to the database as requiring uniqueness) is a no-op, or maybe goes ahead and executes the query and then sees that it works out OK, while PostgreSQL doesn''t.) -Dave -- Specialization is for insects. -RAH | Have Pun, Will Babble! -me Programming Blog: http://codosaur.us | Work: http://davearonson.com Leadership Blog: http://dare2xl.com | Play: http://davearonson.net * * * * * WATCH THIS SPACE * * * * * | Ruby: http://mars.groupsite.com -- 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.
Fernando Perez
2010-Aug-12 14:01 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
> by doing some research on Google, I found out that it''s a DB Engin > problem. MySql allows me to do it but PostgreSql does not. > > How can I run the same query on PostgreSql.I''ve run into such problem when I changed from MySQL to PostgreSQL some time ago. basically MySQL is to databases what php is to programming languages, i.e: it''s permissive. I guess your query is not fully SQL compliant, but MySQL let''s you get away with it whereas PostgreSQL complains and wants a legit query. -- Posted via http://www.ruby-forum.com/. -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser
2010-Aug-12 14:16 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
Dave Aronson wrote:> On Wed, Aug 11, 2010 at 22:40, Mohammed Alenazi <vb4max-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > >> properties.user_id = users.id �WHERE (users.id != 1) �GROUP BY >> users.id,users.login,users.name ORDER BY count(properties.id) DESC >> LIMIT 10 OFFSET 0): > > I haven''t run into this sort of thing, but from my general > SQL/database knowledge, I''d guess that the grouping is causing the > problem. > > I assume users.id is unique. Is that correct? If so, then grouping > on it (:group => ''users.id'') is useless.It doesn''t matter if it''s unique. There''s no aggregate function in this query, so there''s no point to a GROUP BY clause. Remove it. MySQL may just be ignoring it; Postgres is quite correctly telling you that it makes no sense for it to be there. -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org Sent from my iPhone -- Posted via http://www.ruby-forum.com/. -- 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.
E. Litwin
2010-Aug-12 20:24 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
In MySQL, I believe the default SQL_MODE sets the "ONLY_FULL_GROUP_BY" option to false. This means you can do the following query: SELECT name, address, MAX(age) FROM t GROUP BY name; I am not aware of other databases allowing you to set a mode to let you run these kind of invalid queries, which can give you invalid results and are dangerous IMO. http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_only_full_group_by -- 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-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Mohammed Alenazi
2010-Aug-12 23:49 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
I removed the group by clause but I got an error in mySQL development mode. This is the statement after modification @users = User.paginate :joins => :properties ,:select => ''users.*'', :page => params[:page], :order => ''count(properties.id) DESC'',:conditions => [''users.id != 1'',''users.id=properties.user_id''] this the error message i got misuse of aggregate: count(): SELECT users.* FROM "users" INNER JOIN "properties" ON properties.user_id = users.id WHERE (users.id !1) ORDER BY count(properties.id) DESC LIMIT 10 OFFSET 0 On Aug 12, 9:16 am, Marnen Laibow-Koser <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> Dave Aronson wrote: > > On Wed, Aug 11, 2010 at 22:40, Mohammed Alenazi <vb4...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > wrote: > > >> properties.user_id = users.id WHERE (users.id != 1) GROUP BY > >> users.id,users.login,users.name ORDER BY count(properties.id) DESC > >> LIMIT 10 OFFSET 0): > > > I haven''t run into this sort of thing, but from my general > > SQL/database knowledge, I''d guess that the grouping is causing the > > problem. > > > I assume users.id is unique. Is that correct? If so, then grouping > > on it (:group => ''users.id'') is useless. > > It doesn''t matter if it''s unique. There''s no aggregate function in this > query, so there''s no point to a GROUP BY clause. Remove it. MySQL may > just be ignoring it; Postgres is quite correctly telling you that it > makes no sense for it to be there. > > -- > Marnen Laibow-Koserhttp://www.marnen.org > mar...-sbuyVjPbboAdnm+yROfE0A@public.gmane.org > > Sent from my iPhone > -- > Posted viahttp://www.ruby-forum.com/.-- 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.
Hassan Schroeder
2010-Aug-13 00:25 UTC
Re: Re: Problem with changing from mySql to PostrgreSql with rails
On Thu, Aug 12, 2010 at 4:49 PM, Mohammed Alenazi <vb4max-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> @users = User.paginate :joins => :properties ,:select => > ''users.*'', :page => params[:page], :order => ''count(properties.id) > DESC'',:conditions => [''users.id != 1'',''users.id=properties.user_id''] > > this the error message i got > misuse of aggregate: count(): SELECT users.* FROM "users" INNER > JOIN "properties" ON properties.user_id = users.id WHERE (users.id !> 1) ORDER BY count(properties.id) DESC LIMIT 10 OFFSET 0No kidding :-) `count(*)` gives you a single number, so you couldn''t possibly do an ORDER BY with it. Just an ORDER BY properties.id should do what you want. Of course, I have to wonder if this couldn''t be done much more neatly with a named scope or two, particularly that "users.id != 1" business... FWIW, -- Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org twitter: @hassan -- 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.
Mohammed Alenazi
2010-Aug-13 06:25 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
There is still a problem. @users = User.paginate :joins => :properties ,:select => ''distinct users.*'', :page => params[:page], :order => ''properties.id DESC'',:conditions => [''users.id != 1''] I got this error message. ActiveRecord::StatementInvalid (PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list : SELECT distinct users.* FROM "users" INNER JOIN "properties" ON properties.user_id = users.id WHERE (users.id != 1) ORDER BY properties.id DESC LIMIT 10 OFFSET 0): On Aug 12, 7:25 pm, Hassan Schroeder <hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Thu, Aug 12, 2010 at 4:49 PM, Mohammed Alenazi <vb4...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > @users = User.paginate :joins => :properties ,:select => > > ''users.*'', :page => params[:page], :order => ''count(properties.id) > > DESC'',:conditions => [''users.id != 1'',''users.id=properties.user_id''] > > > this the error message i got > > misuse of aggregate: count(): SELECT users.* FROM "users" INNER > > JOIN "properties" ON properties.user_id = users.id WHERE (users.id !> > 1) ORDER BY count(properties.id) DESC LIMIT 10 OFFSET 0 > > No kidding :-) `count(*)` gives you a single number, so you couldn''t > possibly do an ORDER BY with it. Just an ORDER BY properties.id > should do what you want. > > Of course, I have to wonder if this couldn''t be done much more neatly > with a named scope or two, particularly that "users.id != 1" business... > > FWIW, > -- > Hassan Schroeder ------------------------ hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > twitter: @hassan-- 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.
Hassan Schroeder
2010-Aug-13 13:52 UTC
Re: Re: Problem with changing from mySql to PostrgreSql with rails
On Thu, Aug 12, 2010 at 11:25 PM, Mohammed Alenazi <vb4max-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> There is still a problem. > @users = User.paginate :joins => :properties ,:select => ''distinct > users.*'', :page => params[:page], :order => ''properties.id > DESC'',:conditions => [''users.id != 1''] > I got this error message. > ActiveRecord::StatementInvalid (PGError: ERROR: for SELECT DISTINCT, > ORDER BY expressions must appear in select list > : SELECT distinct users.* FROM "users" INNER JOIN "properties" ON > properties.user_id = users.id WHERE (users.id != 1) ORDER BY > properties.id DESC LIMIT 10 OFFSET 0):Leaving out pagination and the "users.id != 1" stuff for the moment -- does something like @users = User.all(:include => :properties, :order => ''properties.id DESC'') give you the expected data result? -- Hassan Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org twitter: @hassan -- 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.
Mohammed Alenazi
2010-Aug-14 00:16 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
thank you for your responses. Actually what I wanted is to show the users based on how many properties they have. Something like @users = User.all(:include => :properties, :order => ''count(properties.id) DESC'') If you look it my first post, I have the a statement working for mySql but not in postgre On Aug 13, 8:52 am, Hassan Schroeder <hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> On Thu, Aug 12, 2010 at 11:25 PM, Mohammed Alenazi <vb4...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > There is still a problem. > > @users = User.paginate :joins => :properties ,:select => ''distinct > > users.*'', :page => params[:page], :order => ''properties.id > > DESC'',:conditions => [''users.id != 1''] > > I got this error message. > > ActiveRecord::StatementInvalid (PGError: ERROR: for SELECT DISTINCT, > > ORDER BY expressions must appear in select list > > : SELECT distinct users.* FROM "users" INNER JOIN "properties" ON > > properties.user_id = users.id WHERE (users.id != 1) ORDER BY > > properties.id DESC LIMIT 10 OFFSET 0): > > Leaving out pagination and the "users.id != 1" stuff for the moment -- > does something like > @users = User.all(:include => :properties, :order => ''properties.id DESC'') > give you the expected data result? > > -- > Hassan Schroeder ------------------------ hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > twitter: @hassan-- 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.
Mohammed Alenazi
2010-Aug-14 10:08 UTC
Re: Problem with changing from mySql to PostrgreSql with rails
The problem has been solved by adding a counter column to the users table On Aug 13, 7:16 pm, Mohammed Alenazi <vb4...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> thank you for your responses. Actually what I wanted is to show the > users based on how many properties they have. Something like > @users = User.all(:include => :properties, :order => > ''count(properties.id) DESC'') > If you look it my first post, I have the a statement working for mySql > but not in postgre > > On Aug 13, 8:52 am, Hassan Schroeder <hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > > > On Thu, Aug 12, 2010 at 11:25 PM, Mohammed Alenazi <vb4...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > There is still a problem. > > > @users = User.paginate :joins => :properties ,:select => ''distinct > > > users.*'', :page => params[:page], :order => ''properties.id > > > DESC'',:conditions => [''users.id != 1''] > > > I got this error message. > > > ActiveRecord::StatementInvalid (PGError: ERROR: for SELECT DISTINCT, > > > ORDER BY expressions must appear in select list > > > : SELECT distinct users.* FROM "users" INNER JOIN "properties" ON > > > properties.user_id = users.id WHERE (users.id != 1) ORDER BY > > > properties.id DESC LIMIT 10 OFFSET 0): > > > Leaving out pagination and the "users.id != 1" stuff for the moment -- > > does something like > > @users = User.all(:include => :properties, :order => ''properties.id DESC'') > > give you the expected data result? > > > -- > > Hassan Schroeder ------------------------ hassan.schroe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org > > twitter: @hassan > >-- 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.