Forum: Ruby on Rails has many + inner join + postgresql

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Drogomir (Guest)
on 2009-02-07 12:06
(Received via mailing list)
Hi,

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

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?
Fernando P. (Guest)
on 2009-02-07 13:42
> 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.
Drogomir (Guest)
on 2009-02-07 14:30
(Received via mailing list)
On Feb 7, 12:42 pm, Fernando P. <removed_email_address@domain.invalid>
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 ;-) And it's additional query....
This topic is locked and can not be replied to.