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

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

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

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

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

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

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

Ответ 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'
)

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

Ответ 2

Мое решение:

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

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

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

Ответ 3

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

Вы не можете получить последнюю почту за каждый день, не используя sub 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 должен быть достаточно , если, вы можете быть уверены, что два столбца не будут иметь одинаковое значение в столбце created_at. Если вы не можете гарантировать уникальность в созданном столбце, вам нужно либо отфильтровать дубликаты в Ruby (это не должно быть слишком неэффективным, потому что, по-видимому, вы все равно будете перебирать список), или вам нужно будет сделать N +1 SQL-операторов. (На самом деле вы можете делать выборки для каждой строки, но AFAIK, который так же неэффективен, как и SQL-запросы N + 1.)

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

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: получение наивысшего балла для пользователя