CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
vicvans20
vicvans20

Reputation: 113

Rails 4 Filter model multiple times with nested attributes

I have three main models that deals with this case. Briefly their relationships are like this:

class Person < ActiveRecord::Base
  has_many :sessions, dependent: :delete_all, inverse_of: :person
  has_many :answers, through: :sessions
end

class Session < ActiveRecord::Base
  belongs_to :person, inverse_of: :sessions
  has_many :answers
end

class Answer < ActiveRecord::Base
  belongs_to :session
end

To be brief and focus on the problem I will summarize the models attributes and focus only on the Answer model attributes as they are the only ones I need to do what I need. Answers model have the following attributes:

# == Schema Information
#
# Table name: answers 
#
#  id                 :integer          not null, primary key
#  session_id         :integer
#  question_option_id :integer
#  created_at         :datetime         not null
#  updated_at         :datetime         not null
#

What I want is to filter the people records according to the question_option_id value provided from a form, there the users have the option to filter with one or more answers question options values. The values are sent without problems, but I have tried without success to filter the people records, this is the closest I have been with partial success:

people = Person.all
people = people.joins(:answers).uniq.where(answers: {question_option_id: some_param})

With this I have been able to filter with the value of one question option, the problem comes when I try to filter more than one question option it returns nothing, this is what I have tried:

people = Person.all
people = people.joins(:answers).uniq.where(answers: {question_option_id: some_question_option_id_param})
people = people.joins(:answers).uniq.where(answers: {question_option_id: another_question_option_id_param})

The problem is that with these conditions, the query search for people with one answer that have both question_option_id values which is not possible. What I try to accomplish is to make a query to search for the people that have those question options id among all the answers they possess. Sorry if I'm hard to understand, but this is what I'm trying to do, I'm not sure if I'm doing the right approach, any suggestions are welcome.

Upvotes: 1

Views: 358

Answers (1)

rabusmar
rabusmar

Reputation: 4143

This is a case of relational division. For your case, something like this would do the job:

# question_option_ids = [some_param, some_other_param, ...]
people = Person.all
question_option_ids.each do |question_option_id|
  people = people.where('EXISTS (SELECT * FROM sessions s INNER JOIN answers a ON s.id = a.session_id WHERE s.person_id = people.id AND a.question_option_id = ?)', question_option_id)
end

Upvotes: 2

Related Questions