Rails-加入后区别开

一尘不染

Rails-加入后区别开

sql

我在PostgreSQL中使用Rails 4.2。我有一个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在加入后选择与众不同的产品。如果产品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子句中指定，因为此约束会输出意外的顺序。

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

阅读 124

2021-03-17

共1个答案

一尘不染

使用子查询并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;

但是，如果您需要的全部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;

甚至更简单，但在检索所有行时却没有那么快：

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;

如果仅获取一小部分选择（例如，WHERE子句限制为一个或几个pr.id），则速度会更快。

2021-03-17