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.

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

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.

Dave Aronson wrote:

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

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

No kidding :slight_smile: `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,

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?

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

The problem has been solved by adding a counter column to the users table