Rails - отличное включение после присоединения

Я использую Rails 4.2 с PostgreSQL. У меня есть модель Product и модель Purchase с Product has many Purchases. Я хочу найти отдельные недавно приобретенные продукты. Сначала я попробовал:

Product.joins(:purchases)
.select("DISTINCT products.*, purchases.updated_at") #postgresql requires order column in select
.order("purchases.updated_at DESC")

Это приводит к дублированию, потому что он пытается найти все кортежи, где пара (product.id и purchases.updated_at) имеет уникальное значение. Однако я просто хочу выбрать продукты с отличным id после объединения. Если идентификатор продукта появляется несколько раз в соединении, выберите только первый. Поэтому я также пробовал:

Product.joins(:purchases)
.select("DISTINCT ON (product.id) purchases.updated_at, products.*")
.order("product.id, purchases.updated_at") #postgres requires that DISTINCT ON must match the leftmost order by clause

Это не работает, потому что мне нужно указать product.id в предложении order из-за ограничения this, которое выводит неожиданный порядок.

Каков способ рельсов для достижения этого?

5 ответов

Используйте подзапрос и добавьте другое выражение ORDER BY во внешнем SELECT:

SELECT *
FROM (
 SELECT DISTINCT ON (pr.id)
 pu.updated_at, pr.*
 FROM Product pr
 JOIN Purchases pu ON pu.product_id = pr.id -- guessing
 ORDER BY pr.id, pu.updated_at DESC NULLS LAST
 ) sub
ORDER BY updated_at DESC NULLS LAST;

Подробности для DISTINCT ON:

Или какой-либо другой метод запроса:

Но если все, что вам нужно от Purchases, updated_at, вы можете получить это дешевле с помощью простого агрегата в подзапросе перед тем, как присоединиться:

SELECT *
FROM Product pr
JOIN (
 SELECT product_id, max(updated_at) AS updated_at
 FROM Purchases 
 GROUP BY 1
 ) pu ON pu.product_id = pr.id -- guessing
ORDER BY pu.updated_at DESC NULLS LAST;

О NULLS LAST:

Или даже проще, но не так быстро, когда вы извлекаете все строки:

SELECT pr.*, max(updated_at) AS updated_at
FROM Product pr
JOIN Purchases pu ON pu.product_id = pr.id
GROUP BY pr.id -- must be primary key
ORDER BY 2 DESC NULLS LAST;

Product.id должен быть определен как первичный ключ для этого. Подробности:

Если вы выбираете только небольшой выбор (с предложением WHERE, ограничивающим только один или несколько pr.id), это будет быстрее.


Поэтому, основываясь на ответе @ErwinBrandstetter, я наконец нашел правильный способ сделать это. Запрос на поиск отдельных недавних покупок -

SELECT *
FROM (
 SELECT DISTINCT ON (pr.id)
 pu.updated_at, pr.*
 FROM Product pr
 JOIN Purchases pu ON pu.product_id = pr.id
 ) sub
ORDER BY updated_at DESC NULLS LAST;

Внутри подзапроса не требуется order_by, так как мы все равно заказываем внешний запрос.

Рельсы делают это -

inner_query = Product.joins(:purchases)
 .select("DISTINCT ON (products.id) products.*, purchases.updated_at as date") #This selects all the unique purchased products.
result = Product.from("(#{inner_query.to_sql}) as unique_purchases")
 .select("unique_purchases.*").order("unique_purchases.date DESC")

Второй (и лучший) способ сделать это, как предложено @ErwinBrandstetter, это

SELECT *
FROM Product pr
JOIN (
 SELECT product_id, max(updated_at) AS updated_at
 FROM Purchases 
 GROUP BY 1
 ) pu ON pu.product_id = pr.id
ORDER BY pu.updated_at DESC NULLS LAST;

который может быть записан в рельсах как

join_query = Purchase.select("product_id, max(updated_at) as date")
 .group(1) #This selects most recent date for all purchased products
result = Product.joins("INNER JOIN (#{join_query.to_sql}) as unique_purchases ON products.id = unique_purchases.product_id")
 .order("unique_purchases.date")


Настроить на erwin-brandstetter ответ, так вы можете сделать это с помощью ActiveRecord (должно быть как минимум по крайней мере):

Product
 .select('*')
 .joins('INNER JOIN (SELECT product_id, max(updated_at) AS updated_at FROM Purchases GROUP BY 1) pu ON pu.product_id = pr.id')
 .order('pu.updated_at DESC NULLS LAST')


Я закончил с этим -

Product.joins(:purchases)
.select("DISTINCT ON (products.id) products.*, purchases.updated_at as date")
.sort_by(&:date)
.reverse

Ищем лучший способ сделать это.


Попробуйте сделать это:

Product.joins(:purchases)
.select("DISTINCT ON (products_id) purchases.product_id, purchases.updated_at, products.*")
.order("product_id, purchases.updated_at") #postgres requires that DISTINCT ON must match the leftmost order by clause

licensed under cc by-sa 3.0 with attribution.