has many + inner join + postgresql

rubyonrails-talk
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.

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