Has many + inner join + postgresql

Rails
1

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?

2

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.

3

On Feb 7, 12:42 pm, Fernando P. [email protected]
wrote:

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…