CODEWITHSUNDEEP
ruby-on-rails-3activerecord
earnold
earnold

Reputation: 1462

ActiveRecord Query: select record where has at least one in a has many relationship

Say you have this:

class Question < ActiveRecord::Base
 has_many :answers
end

class Answer < ActiveRecord::Base
 belongs_to :question
end

How do I search for all the questions that have answers without getting duplicate questions?

Say a question has two answers, if you do this: 

Question.joins(:answers)

because it is an inner join, you'll get the question multiple times.

Is there a way to do this through the query interface without having to do a raw sql distinct or unique?

Upvotes: 4

Views: 2280

Answers (2)

Micha&#235;l Witrant
Micha&#235;l Witrant

Reputation: 7714

This should work:

Question.joins(:answers).uniq

Upvotes: 3

Jeremy Roman
Jeremy Roman

Reputation: 16345

I would just use a counter cache column. This gives you a database column in questions which counts the number of answers attached to it. As a bonus, it's probably faster to run this query.

Here's a Railscasts video that describes how to create one: http://railscasts.com/episodes/23-counter-cache-column

Then your query becomes:

Question.where("answers_count > 0")

You could even be fancy and define this as a scope:

class Question < ActiveRecord::Base
  has_many :answers
  scope :answered, where("answers_count > 0")
end

It seems like a simple solution, but simple is good.

Upvotes: 4

Related Questions