CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3activerecord
nattfodd
nattfodd

Reputation: 1900

Select model based on amount of related models with certain conditions

I have a Post that has_many :comments. Let's say Comment has the following field: another_model_id. I would like to select Posts, that have from 2 to 5 comments with another_model_id = 10 (for example). I tried several constructions but with no success :(

Example of what I tried:

# Invalid SQL syntax error
Post
  .joins(:comments)
  .select("count(comment.another_model_id = 10) as comments_count)
  .where("comments_count BETWEEN 2 AND 5")

I have literally no idea where to dig. Is it possible to achieve that in a single query? Thanks!

Upvotes: 1

Views: 43

Answers (2)

Moamen Naanou
Moamen Naanou

Reputation: 1713

Using counter_cache is the best practice for your scenario:

In your Comment model, you need to set the counter_cache:

belongs_to :post, counter_cache: true

Then simply you can use this query:

Post.joins(:comments)
    .where(comments: { another_model_id: 10 })
    .where('comments_count > ? AND comments_count < ?', 2, 5)

Upvotes: 0

Kartikey Tanna
Kartikey Tanna

Reputation: 1459

Post
  .joins(:comments)
  .where(comments: { another_model_id: 10 })
  .group('posts.id')
  .having('count(comments.id) > 2 AND count(comments.id) < 5')

Upvotes: 1

Related Questions