Find all associated objects by specific condition

Question

class QuestionGroup < ActiveRecord::Base
  has_many :questions
end

class Question < ActiveRecord::Base
  belongs_to :question_group
  has_many :question_answers
  has_many :question_users_answers, :through => :question_answers, :source => :user_question_answers

  def self.questions_without_answers(user_id)
    select {|q| q.question_users_answers.where(:user_id=>user_id).empty?}
  end
end

class QuestionAnswer < ActiveRecord::Base
  belongs_to :question
  has_many :user_question_answers
end

I need find all Questions if they have no user answers I did it by class method self.questions_without_answers(user_id)

But how can I find all QuestionGroups where present questions_without_answers and for particular user?

P.S: I need to find all unanswered questions and all groups that own these questions, can I do it by find or named-scope?

UPDATED:

  def self.groups_without_answers(user_id)
    questions_ids = Question.questions_without_answers(user_id).map {|q| q.id}
    all(:conditions => "id in (select distinct question_group_id from questions where id in (#{questions_ids.join(',')}))")
  end

But I think it is not good or maybe I wrong?

Marlin Pierce · Accepted Answer · 2012-09-28T12:58:42.483

0

class QuestionGroup < ActiveRecord::Base
  has_many :questions

  def self.without_answers(user_id)
    joins(%"inner join questions on question_groups.id = questions.question_group_id
            inner join question_answers
              on question_answers.question_id = questions.id
            inner join question_groups
              on question_answers.question_users_answers_id = question_users_answers.id").where("user_question_answers.user_id" => user_id).select { |qq| ... }
    end
  end
end

You can change some of the inner joins to left out join to pick up records where the table you are joining to doesn't have a match, for instance where there is no answer. The fields of the table you are joining to will have NULL values for all the fields. Adding a where id is null will even filter to just the questions with no answers.

Keep in mind that this is just an alternative technique. You could programmatically solve the problem simple with:

class QuestionGroup
  def self.question_groups_without_answers(user_id)
    select {|qq| qq.question_users_answers.where(:user_id=>user_id).empty?}.map{ |qq| qq.question_group }
  end
end

An advantage of doing the joins is that the database does all the work, and you don't send several SQL queries to the database, so it can be much faster.

edited Sep 28 '12 at 12:58

answered Sep 27 '12 at 15:54

Marlin Pierce

9,931
4
30
52

Thanks, wait a minute I try it ;) – zolter Sep 27 '12 at 16:16
Joins work but where throw this exception: Mysql2::Error: Unknown column 'bio_params_groups.user_id'. User_id column know only user_question_answers table. – zolter Sep 27 '12 at 16:48
The problem with my answer was that I got confused with where you are using the select. Select can either be a method to set the SQL select phrase, or it can treat the relation as an array, and select the elements as Array#select does. You are getting every question object, doing the array select on each one, then calling the association to query with a where clause, and checking if the result is empty. – Marlin Pierce Sep 27 '12 at 16:58
The joins clause will work, but its working on a whole different approach. You may have to play with it a little, but the joins is probably needed if you want the database to do the work, because of the multiple associations in different classes. You could do it programmatically, but then you should be able to figure that out on your own. – Marlin Pierce Sep 27 '12 at 16:58
Needed "user_question_answers.user_id" instead of :user_id. – Marlin Pierce Sep 27 '12 at 17:04
Yes you are right, but now I received only groups with answers. Or now I need describe all logic how find groups without answers in select? – zolter Sep 27 '12 at 19:21
If you change an inner join to a left outer join, then if something is missing in the table you are joining to, the record will be included in the result, but will NULL for all fields. Also, you can then query for just the ones with no answers by adding a where *id is null* to get the ones without answers. – Marlin Pierce Sep 28 '12 at 10:14

Find all associated objects by specific condition

1 Answers1