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.
On Wed, Aug 11, 2010 at 22:40, Mohammed A. [email protected]
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. 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
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.
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 !=
- ORDER BY count(properties.id) DESC LIMIT 10 OFFSET 0
On Thu, Aug 12, 2010 at 4:49 PM, Mohammed A. [email protected]
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 !=
- 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 S. ------------------------ [email protected]
twitter: @hassan
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 S. [email protected]
Dave A. wrote:
On Wed, Aug 11, 2010 at 22:40, Mohammed A. [email protected]
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
[email protected]
Sent from my iPhone
On Thu, Aug 12, 2010 at 11:25 PM, Mohammed A. [email protected]
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 S. ------------------------ [email protected]
twitter: @hassan
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 S. [email protected]
The problem has been solved by adding a counter column to the users
table