CODEWITHSUNDEEP
ruby-on-railsactiverecord
igor_rb
igor_rb

Reputation: 1891

Active Record get parent records based on child data

I have several related models: Category has_many Question. Question has_many Answer. I need:

  1. Get all Categories, where at least one Question has Answer

  2. Count all Questions, that has at least one Answer(how many Questions inside Category has Answers)

  3. Get all Categories, where all Questions has 0 Answers

  4. Count all Questions, that has not Answers ( How many Questions inside Category, that has not Answers).

EDIT

If I want get list of categories with answers and list of categories without answers?

cats = []
@categories_with_answers = Category.all.each do |cat|
  cats << cat if cat.questions.joins(:answers).count > 0
end

How I can write this snippet in more cleaner way?

Upvotes: 1

Views: 1643

Answers (1)

Anthony E
Anthony E

Reputation: 11235

You can use includes and joins to query relationships:

  1. Category.joins(questions: :answers)

  2. Question.joins(:answers).count

  3. Category.includes(questions: :answers).where(questions: { answers: { question_id: nil }})

  4. Question.includes(:answers).where(answers: { question_id: nil })

There's a good blog article that discusses these approaches in more detail here: https://www.pagerduty.com/blog/sql-left-join-trick/.

EDIT

For an individual category, the query is similar:

@category = Category.find(params[:category_id])

@category.questions.joins(:answers).count

This would be equivalent to:

Question.joins(:answers).where(questions: { category_id: @category.id }).count

The same principle applies to includes...

@category.questions.includes(:answers).where(answers: { question_id: nil })

Upvotes: 1

Related Questions