Rails - 连接后 Distinct ON

我正在使用 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 and purchases.updated_at) 具有独特的价值。但我只想选择具有独特性的产品id加入后。如果某个产品 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 in the order条款因为this输出意外顺序的约束。

实现这一目标的 Rails 方法是什么？

因此，在@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;

The order_by子查询内部不需要，因为我们无论如何都在外部查询中排序。

执行此操作的 Rails 方法是 -

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;

可以用 Rails 写成

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