Элегантная группа PostgreSQL для Ruby on Rails / ActiveRecord

Question

Попытка получить массив объектов ActiveRecord, сгруппированных по дате, с помощью PostgreSQL.

Более конкретно я пытаюсь перевести следующий запрос MySQL:

@posts = Post.all(:group => "date(date)", 
   :conditions => ["location_id = ? and published = ?", @location.id, true], 
   :order => "created_at DESC")

Я знаю, что PostgreSQL интерпретирует стандарт SQL более строго, чем MySQL, и, следовательно, этот тип запроса не будет работать... и прочитал несколько сообщений в Stackru и в других местах по теме - но ни одна из них, похоже, не является окончательный ответ на эту тему

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

Как правильно сделать такой запрос с помощью Rails и PostgreSQL? (Не обращая внимания на тот факт, что это обязательно должно быть удалено на уровне ActiveRecord)

Answer 1

Функция PostgreSQL, которую вы хотите использовать здесь: DISTINCT ON, Есть два основных способа сделать этот запрос через ActiveRecord.

Первый способ - просто указать :select а также :order опции. Это прекрасно работает, когда у вас есть довольно простой запрос без :joins или же :include,

Post.all(
  :select => 'DISTINCT ON (date::date) *',
  :order => 'date::date DESC, created_at DESC'
)

Если у вас есть более сложный запрос, где ActiveRecord генерирует свой собственный SELECT предложение, вы можете использовать подзапрос для выбора целевых записей.

Post.all(
  :joins => 'INNER JOIN (SELECT DISTINCT ON (date::date) id FROM posts ORDER BY date::date DESC, created_at DESC) x ON x.id = posts.id'
)

Обратите внимание, что это может быть немного медленнее, чем первый метод, в зависимости от ваших данных. Я бы использовал этот метод только в случае необходимости. Обязательно сравните с данными, подобными производственным.

Answer 2

Мое решение:

def self.columns_list
   column_names.collect { |c| "#{table_name}.#{c}" }.join(",")
 end

 scope :selling, joins(:products).group(columns_list)

Простой и повторяемый.

Answer 3

В то время как SQL довольно прост, когда речь идет об ответах на вопросы типа "когда была самая последняя публикация за каждый день?" это не очень прямолинейно, когда вы спрашиваете "какая была самая последняя запись за каждый день?"

Вы не можете получить последнее сообщение за каждый день, не используя вложенный SELECT (или несколько операторов SQL). Это может работать для вас (используйте Post.find_by_sql или подобное):

SELECT P.*, M.just_day, M.max_created_at
FROM posts P
JOIN (
  SELECT date(P2.date) AS just_day, MAX(P2.created_at) AS max_created_at
  FROM posts P2
  P.location_id='12345' AND P.published=true
  GROUP BY date(P2.date)
) AS M  
   ON AND M.max_created_at = P.created_at
WHERE P.location_id='12345' AND P.published=true

Приведенного выше оператора SQL должно быть достаточно, если вы можете быть уверены, что два столбца не будут иметь одинаковое значение в столбце create_at. Если вы не можете гарантировать уникальность в созданном столбце at, то вам нужно либо отфильтровать дубликаты в Ruby (это не должно быть слишком неэффективно, потому что, по-видимому, вы все равно будете циклически перемещаться по списку), либо вам нужно будет выполнить N+1 SQL операторов. (На самом деле вы можете делать выборки для каждой строки, но AFAIK так же неэффективен, как N + 1 SQL-операторы.)

Вот как вы можете удалить дубликаты во время цикла:

last_post = nil
posts.each do |post|
  unless post.just_day == last_past.try(:just_day)
    # Do stuff
    last_post = post
  end
end

Тем не менее, вы можете написать это просто с помощью Ruby/ActiveRecord, если у вас есть достаточно дней, чтобы SELECT для каждого дня был не так уж плох:

days = Post.group("date(date)")
posts = days.each { |day| Post.order('created DESC').where("date(day) = ?", day) }

Если вы используете нумерацию страниц (скажем, 10 элементов на страницу), то для каждой страницы потребуется 11 операторов SQL. Не идеи, а простота может стоить неэффективности.

Честно говоря, если вы ожидаете, что этот запрос будет выполняться часто и с достаточно большим набором данных, тогда я предлагаю вам добавить логический столбец с именем most_recent. Последнее сообщение из прошлых дней не изменится. Вам нужно только беспокоиться о сообщениях с сегодняшнего дня. Просто настройте задачу cron для запуска через несколько минут после окончания дня, чтобы обновить значение за последний день. Если вы хотите что-то более современное, вы можете запускать задание cron каждые 5 минут. Или, если вам нужно в режиме реального времени, добавьте обратный вызов after_save, чтобы установить для Most_recent значение false для всех сегодняшних сообщений, которые не являются текущими.

Этот вопрос похож на: MySQL: получение наивысшего балла за пользователя