mysql - Ordering questions by most recently answered and filtering those unanswered by a particular user -

i'm having trouble writing active record query returns results want. have following setup:

abridged user model:

class user < activerecord::base   has_many :answers end 

abridged answer model:

class answer < activerecord::base   belongs_to :question   belongs_to :user end 

abridged question model:

class question < activerecord::base   has_many :answers    def self.unanswered_by(user)     where(       'id not in (select question_id answers user_id = ?)',       user.id     )   end    def self.recently_answered     includes(:answers).order('answers.updated_at desc')   end end 

i'm trying activerecord::relation orders questions have been answered , filters result contains questions current_user has yet answer.

ideally, i'd write

question.recently_answered.unanswered_by current_user 

but doesn't appear work , i'm struggling understand why limited understanding of sql.

this result when run in rails console:

me = user.find(8) question.recently_answered.unanswered_by me =>   sql (0.5ms)  select `questions`.`id` t0_r0,      `questions`.`question_text` t0_r1,      `questions`.`example_answer` t0_r2,      `questions`.`created_at` t0_r3,      `questions`.`updated_at` t0_r4,      `answers`.`id` t1_r0,      `answers`.`question_id` t1_r1,      `answers`.`user_id` t1_r2,      `answers`.`answer_text` t1_r3,      `answers`.`created_at` t1_r4,      `answers`.`updated_at` t1_r5      `questions` left outer join `answers`      on `answers`.`question_id` = `questions`.`id`      (id not in (select question_id answers user_id = 8))      order answers.updated_at desc      #<activerecord::relation:0x3fd42e362a80> 

running question.recently_answered.unanswered_by(me).to_sql gives me this:

=> "select `questions`.*    `questions`    (id not in (select question_id           answers user_id = 8))    order answers.updated_at desc" 

i'm working around right doing

question   .recently_answered   .reject { |q| q.answers.map(&:user_id).include? current_user.id } 

but returns array of question objects instead of activerecord::relation i'd prefer.

could me understand why can't chain recently_answered , unanswered_by written , how go rewriting can result want? thanks.

you should add table's name in sql query of unanswered_by method:

  def self.unanswered_by(user)     where('questions.id not in (select question_id answers user_id = ?)', user.id)           #^^^^^^^^^ table's name added here   end 

because if use combined joins/includes, db adapter not know table select id (error message column id ambiguous).

also, should use scope instead these 2 methods.