CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
Lowell Mower
Lowell Mower

Reputation: 313

Getting a collection of objects based on ActiveRecord assoc and values

I have two models/tables, Schools and Reviews. I would like to get a collection of School objects based on a minimum number of reviews and then the average of a column on the review table. Currently, my query works well and returns all schools in order from 5.0 avg to nulls last and looks like this:

School.joins('LEFT JOIN reviews ON schools.id = reviews.school_id')
      .group('schools.id')
      .order('AVG(reviews.overall_experience_rating) DESC NULLS LAST')

however, I'd like to be able to have a limiter in here that only selects the school object if it has 15 or more reviews, something like:

where("school.reviews.count >= 15")

The association between the two models in active record is a one to many relationship. Any thoughts?

Upvotes: 2

Views: 44

Answers (1)

Lowell Mower
Lowell Mower

Reputation: 313

I've figured out that use of having in ActiveRecord is what I need:

School.joins(:reviews)
  .group('schools.id')
  .having("COUNT(reviews) > ?", 15)
  .order('AVG(reviews.overall_experience_rating) DESC NULLS LAST')

Upvotes: 1

Related Questions