has many + inner join + postgresql

Hi,

I tried to make query similar to Pratik's from his post:
http://m.onkey.org/2007/11/1/find-users-with-at-least-n-items

User.find :all, :joins => "INNER JOIN items ON items.user_id =
users.id", :select => "users.*, count(items.id) items_count", :group
=> "items.user_id HAVING items_count > 5"

I use postgresql and it keeps giving me errors while trying to make
such a query. I changed "count(items.id) items_count" to "count
(items.id) as items_count" and the missing thing is users.id (it must
be in group by). When I put users.id into group by it says that I must
put users.login in group by.

So I end up with query:
User.find :all, :joins => "INNER JOIN items ON items.user_id =
users.id", :select => "users.*, count(items.id) items_count", :group
=> "users.id, users.login, users.email, (.... listing all user
columns ....), items.user_id HAVING items_count > 5"

Listing all users columns is not best option... maybe someone more
familiar with SQL and postgresql could help me with this?

> User.find :all, :joins => "INNER JOIN items ON items.user_id =
> users.id", :select => "users.*, count(items.id) items_count", :group
> => "items.user_id HAVING items_count > 5"

Try:

User.find :all,
:joins => "INNER JOIN items ON items.user_id = users.id",
:select => "users.*, count(*) items_count",
:group => "items.user_id HAVING items_count > 5"

But it might fail, because I think PostgreSQL expects you to list all
selected fields in the :group option. First try selecting the user.name
only and add it to the :group option.

Yes... unfortunately count(items.id) is not a problem.

I tried selecting only one field from users and of course it works.
But most of the time I want to select more than one field.

The "hackery" workaround is to get all the columns:
columns = User.column_names.map { |n| "users.#{n}" }.join(",")

and insert such string to :group. But... .it's not prettiest
option :wink: And it's additional query....