Rails-加入后区别开 [英] Rails - Distinct ON after a join

问题描述

我在PostgreSQL中使用Rails 4.2。我有一个 Product 模型和一个 Purchase 模型以及 Product 有很多 购买。我想找到不同的最近购买的产品。最初，我尝试过：

I am using Rails 4.2 with PostgreSQL. I have a Product model and a Purchase model with Product has many Purchases. I want to find the distinct recently purchased products. Initially I tried:

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在联接中多次出现，则仅选择第一个。所以我也尝试过：

This however results in duplicates because it tries to find all tuples where the pair (product.id and purchases.updated_at) has a unique value. However I just want to select the products with distinct id after the join. If a product id appears multiple times in the join, only select the first one. So I also tried:

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

这不起作用，因为我需要在<$ c中指定 product.id $ c> order 子句，因为此约束会输出意外订单。

This doesn't work because I need to specify product.id in the order clause because of this constraint which outputs unexpected order.

实现此目标的方法是什么？

What is the rails way to achieve this?

推荐答案

使用子查询并在外部 SELECT 中添加其他 ORDER BY 子句：

Use a subquery and add a different ORDER BY clause in the outer 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 的详细信息：

• 在每个GROUP BY组中选择第一行？

• Select first row in each GROUP BY group?

还是其他一些查询技术：

Or some other query technique:

• 优化GROUP BY查询以检索每个用户的最新记录

• Optimize GROUP BY query to retrieve latest record per user

但是，如果您从购买所需的全部是 updated_at ，则可以在子查询中通过简单的汇总获得此便宜的商品，然后再进行加入：

But if all you need from Purchases is updated_at, you can get this cheaper with a simple aggregate in a subquery before you join:

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 ：

• PostgreSQL按日期时间asc排序，是否为null？

• PostgreSQL sort by datetime asc, null first?

或更简单，但检索时速度不如以前所有行：

Or even simpler, but not as fast while retrieving all rows:

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 需要定义为为此工作的主键。详细信息：

Product.id needs to be defined as primary key for this to work. Details:

• PostgreSQL-GROUP BY子句


• 使用Rails和PostgreSQL返回包含出现次数的分组列表

• PostgreSQL - GROUP BY clause
• Return a grouped list with occurrences using Rails and PostgreSQL

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

If you fetch only a small selection (with a WHERE clause restricting to just one or a few pr.id for instance), this will be faster.

这篇关于Rails-加入后区别开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！