Запрос Rails Active Record: ответы на вопросы, упорядоченные по дате ответа, без дубликатов

Вопрос имеет много ответов.

Легко найти все вопросы, на которые были даны ответы, и упорядочить их по последнему ответу:

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

В контроллере я бы сделал @answered = Question.answered

Но это возвращает повторяющиеся записи, если на вопрос был дан ответ более одного раза.

С другой стороны, легко найти отдельные вопросы, на которые были даны ответы, но только если я не пытаюсь упорядочить их по ответам дате создания:

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

(Давайте просто предположим, что заголовки вопросов проверяются на уникальность, поэтому в результате получаются все уникальные записи).

Проблема:

Этот запрос нельзя упорядочить по дате ответа "created_at", потому что я выбрал только заголовок вопроса в своем операторе выбора SQL...

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

приводит к этой ошибке (я использую Postgres):

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

Я предполагаю, что это означает, что он хочет видеть «answers.created_at» в этом операторе select DISTINCT.

Следующее выглядит так, как будто это сработает, если я захочу заказать с помощью questions.created_at, но это не то, что я хочу:

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

На этом мои базовые знания SQL заканчиваются. Как выбрать отдельные вопросы (только те, на которые есть ответы) и упорядочить их в порядке убывания по дате создания последнего ответа?

Я хотел бы написать это, используя запросы Active Record Rails 3, но прямой SQL в порядке. Я надеюсь, что кто-то может помочь.


ruby-on-rails sql join distinct activerecord
person Steve Cotner    schedule 29.11.2010    source источник

Ответы (4)


arrow_upward
1
arrow_downward

Ответ @Anton был бы предпочтительным решением, если бы мы говорили о 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
person jangosteve    schedule 19.12.2011

arrow_upward
1
arrow_downward

Почему бы не использовать запись как:

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

Это будет делать именно то, что вы хотите :)

person Anton    schedule 29.11.2010
comment
Просто отметим: вы можете включить это в область, чтобы вы могли использовать ее и для набора вопросов. - person jenjenut233; 30.11.2010
comment
Я не понимаю, почему, но postgres жалуется: PGError: ERROR: столбец questions.title должен отображаться в предложении GROUP BY или использоваться в агрегатной функции LINE 1: SELECT Questions.* FROM questions INNER JOIN... ^ : SELECT вопросы.* ОТ вопросов ВНУТРЕННЕЕ ОБЪЕДИНЕНИЕ ответы ПО ответы на вопросы. - person Steve Cotner; 30.11.2010
comment
И если я изменю question.id на question.title, он жалуется с тем же сообщением, но вместо этого запрашивает question.id. Он жалуется независимо от того, какой атрибут я запрашиваю в представлении. т. е. в дальнейшем q.title может быть любым: ‹% @just_answered.each do |q| %› ‹%= q.title %›‹br /› ‹% end %› - person Steve Cotner; 30.11.2010
comment
@SteveCotner У меня возникла точно такая же проблема, как и у вас, когда я пробовал вышеуказанное решение с PG. См. мое решение ниже, чтобы узнать, как мне удалось заставить его работать. - person jangosteve; 19.12.2011

arrow_upward
0
arrow_downward

Я могу написать для него 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

Надеюсь, поможет!

person karthiks    schedule 29.11.2010

arrow_upward
0
arrow_downward

Это может оказаться немного более эффективным и расширяемым и должно избежать проблем, когда 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

P.S. под расширяемым я имел в виду, что вы можете разделить его на структуру Arel, а затем разбить области, такие как with_answers и answer, чтобы сделать свою грязную работу за кулисами, сохраняя при этом остальную часть чистой и позволяя позже добавить больше областей. :)

ОБНОВЛЕНИЕ: просто публикую способ Arel:

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')
person jenjenut233    schedule 30.11.2010
comment
Не обращайте внимания на последний комментарий; Я неправильно понял. Но если я напишу это так, он жалуется, что столбец answer_count не существует: scope :answered, find_by_sql(выбрать вопросы.*, count(answers.id) as answer_count из вопросов присоединиться к ответам в группе answer.question_id=questions.id по answer.question_id, имеющему answer_count › 0 заказ по answer.created_at desc) - person Steve Cotner; 30.11.2010
comment
Просто интересно: все это просто какая-то ерунда с PostgreSQL, с которой мне не пришлось бы иметь дело, используя MySQL? - person Steve Cotner; 30.11.2010
comment
На данный момент мой лучший компромисс — сделать модель простой: scope :answered, joins(:answers).order('answers.created_at desc') и немного ограничить контроллер: @just_answered = Question.answered.uniq[ 0..9] Мне это, конечно, не нравится, потому что это означает, что я не могу больше связывать области видимости с @just_answered - person Steve Cotner; 30.11.2010
comment
Вы можете попробовать не выбирать счетчик в виде столбца и просто иметь (count(answers.id)) › 0 - person jenjenut233; 02.12.2010
comment
Я использую MySQL, и это работает для меня: scope :answered, select('questions.*, count(answers.id) as answer_count').joins('объединить ответы на answer.question_id=questions.id').group ('answers.question_id').имеющий('answer_count › 0').order('answers.created_at desc') - person jenjenut233; 02.12.2010