# Has many + inner join + postgresql

**URL:** <https://www.ruby-forum.com/t/has-many-inner-join-postgresql/159700>\
**Category:** Rails\
**Created:** [February 7, 2009, 11:06am UTC](https://www.ruby-forum.com/t/has-many-inner-join-postgresql/159700 "2009-02-07T11:06:43Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Drogomir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5fc32e/32.png) [@Drogomir](https://www.ruby-forum.com/u/Drogomir)\
**Post date:** [February 7, 2009, 11:06am UTC](https://www.ruby-forum.com/t/has-many-inner-join-postgresql/159700/1 "2009-02-07T11:06:43Z")

</div>

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](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?

---

<div class="post-metadata">

**Author:** ![Drogomir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5fc32e/32.png) [@Drogomir](https://www.ruby-forum.com/u/Drogomir)\
**Post date:** [February 7, 2009, 12:42pm UTC](https://www.ruby-forum.com/t/has-many-inner-join-postgresql/159700/2 "2009-02-07T12:42:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Drogomir](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/5fc32e/32.png) [@Drogomir](https://www.ruby-forum.com/u/Drogomir)\
**Post date:** [February 7, 2009, 1:30pm UTC](https://www.ruby-forum.com/t/has-many-inner-join-postgresql/159700/3 "2009-02-07T13:30:59Z")

</div>

On Feb 7, 12:42 pm, Fernando P. [removed\_email\_address@domain.invalid](mailto: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…