CODEWITHSUNDEEP
sqlruby-on-railsactiverecordjoindistinct
Steve Cotner
Steve Cotner

Reputation: 505

Rails Active Record query: answered questions, ordered by answer date, not duplicates

A question has many answers.

It is easy to find all questions that have been answered and order them by newest answer:

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

In the controller, I'd do @answered = Question.answered

But this returns duplicate records if a question has been answered more than once.

It's easy, on the other hand, to find distinct questions that have been answered, but only if I don't try to order them by their answers' created_at date:

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

(Let's just assume here that question titles are validated to be unique, so this results in all unique records).

The problem:

This query can't be ordered by an answer's "created_at" date, because I've selected only the question's title in my SQL select statement...

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

results in this error (I'm using Postgres):

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

What it means, I guess, is it wants to see "answers.created_at" in that select DISTINCT statement.

The following looks like it would work if I wanted to order by questions.created_at, but that's not what I want:

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

This is where my basic SQL knowledge conks out. How do I select distinct questions (only the ones with answers) and order them in descending order by their latest answer's created_at date?

I'd like to write it using Rails 3 Active Record queries, but straight SQL is fine. I hope someone can help.

Upvotes: 3

Views: 999

Answers (4)

jangosteve
jangosteve

Reputation: 1572

@Anton's answer would be the preferred solution if we were talking about MySQL, but as you pointed out, it causes errors in PostgreSQL, since PG is a bit more restrictive in its select/grouping functions.

Here is how I was able to get it to work with 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

Or, if you want to select the entire ActiveRecord rows for the questions, you can select/group by every column in 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

Upvotes: 1

jenjenut233
jenjenut233

Reputation: 1938

This might end up being a little bit more efficient and extensible and should avoid issues where psql gripes about aggregate functions:

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

P.S. by extensible I meant, you can split it into Arel structure, and then break out scopes such as with_answers and answered to do your dirty work behind the scenes while keeping the rest clean and allowing you to add on more scopes later. :)

UPDATE: just posting the Arel way:

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')

Upvotes: 0

karthiks
karthiks

Reputation: 7309

I can write the SQL solution for it. It would go as below:

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

Hope it helps!

Upvotes: 0

Anton
Anton

Reputation: 2483

Why not use write as:

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

This will do exactly why you want :)

Upvotes: 1

Related Questions