Rails Active Record query: answers to questions sorted by date of answer, not duplicates

Question

There are many answers to the question.

It is easy to find answers to all questions and order them according to the latest answer:

def self.answered
  joins(:answers).order('answers.created_at desc')
end

In the controller, I would do @answered = Question.answered

But this returns duplicate entries if the question was received more than once.

On the other hand, it’s easy to find various questions that have been answered, but only if I’m not trying to sort them by their created_at dates:

def self.answered
  joins(:answers).select("DISTINCT questions.title")
end

(Assume that the headings of the questions are considered unique, so this leads to the appearance of all unique entries).

Problem:

This query cannot be sorted by the "created_at" date of the answer because I only selected the question title in my SQL select statement ...

def self.answered
  joins(:answers).select("DISTINCT questions.title").order('answers.created_at desc')
end

leads to this error (I use Postgres):

PGError: ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list

, , "answer.created_at" , DISTINCT.

, , questions.created_at, , :

def self.answered
  joins(:answers).select("DISTINCT(questions.title), questions.created_at").order('questions.created_at desc')
end

SQL. ( ) created_at date?

Rails 3 Active Record, SQL - . , - .

Answer 1

@ , MySQL, , , PostgreSQL, PG /.

PG:

def self.answered
 scoped.select("questions.title").group("questions.title").joins(:answers).having('MAX(answers.created_at) IS NOT NULL').order('MAX(answers.created_at) DESC')
end

, ActiveRecord , / questions:

def self.answered
 cols = self.column_names.collect{ |c| "#{table_name}.#{c}" }.join(',')
 scoped.select(cols).group(cols).joins(:answers).having('MAX(answers.created_at) IS NOT NULL').order('MAX(answers.created_at) DESC')
end

Answer 2

write as:

def self.answered
  joins(:answers).order("answers.created_at DESC").group("questions.id")
end

, :)

Answer 3

SQL-. :

select q.id "Qn-ID", q.question, q.created_at "Qn-CreatedAt", a.id "Ans-ID", a.answer, a.created_at "Ans-CreatedAt" from questions q, answers a where a.question_id = q.id and a.id in (select max(id) answer_id from answers group by question_id) order by "Ans-CreatedAt" desc

, !

Answer 4

, psql :

select questions.*, count(answers.id) as answer_count from questions join answers on answers.question_id=questions.id group by answers.question_id having answer_count > 0 order by answers.created_at desc

PS by extension, I mean, you can split it into an Arel structure, and then break down areas like with_answers and respond to do your dirty work behind the scenes, leaving everything else clean and letting you add additional areas later. :)

UPDATE: just send the Arel path:

scope :answered, select('questions.*, count(answers.id) as answer_count').joins('join answers on answers.question_id=questions.id').group('answers.question_id').having('answer_count > 0').order('answers.created_at desc')