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