CODEWITHSUNDEEP
ruby-on-railspostgresql
C.J. Bordeleau
C.J. Bordeleau

Reputation: 307

ActiveRecord query based on belongs_to association count

I have a User model that has may scores

Class User < ActiveRecord::Base
  has_many :scores

I want to be able to query my postgresql db to return users that have at least one score.

User.where(scores.count >= 1)

Any idea on how to accomplish this without adding a counter_cache?

Upvotes: 1

Views: 451

Answers (2)

bgates
bgates

Reputation: 2373

A more Rails-ish way would be 

User.joins(:scores).where('scores.user_id > 0')

Also, I think SELECT count(*) will return the number of users, not the user rows themselves, but I'm not sure about that.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658907

You could use EXISTS:

SELECT count(*) FROM users
WHERE EXISTS (SELECT 1 FROM scores WHERE scores.user_id = users.id)

Upvotes: 0

Related Questions