删除保存最后标识ActiveRecord的重复记录

Question

我一直试图删除复制的记录(列shopify_order_id中相同的值)，保留最近的记录。

我用sql写的：

select orders.id from (
  select shopify_order_id, min(shopify_created_at) as min_created 
  from orders group by shopify_order_id having count(*) > 1 limit 5000
) as keep_orders 
join orders 
on 
  keep_orders.shopify_order_id = orders.shopify_order_id and 
  orders.shopify_created_at <> keep_orders.min_created

现在我试着把它记录下来，但似乎不能加入这两个部分。

第一个嵌套选择是

Order.select('shopify_order_id, MIN(shopify_created_at) as min_created').
        group(:shopify_order_id).
        having('count(*) > 1').
        limit(5000)

但是，下面的内容不起作用：

Order.select('orders.id').from(keep_orders, :keep_orders).
joins('orders ON keep_orders.shopify_order_id = orders.shopify_order_id').
where.not('orders.shopify_created_at = keep_orders.min_created')

它构建查询：

SELECT  orders.id FROM  (SELECT  shopify_order_id, MIN(shopify_created_at) as min_created FROM "orders" GROUP BY "orders"."shopify_order_id" HAVING (count(*) > 1) LIMIT $1) keep_orders  orders ON keep_orders.shopify_order_id = orders.shopify_order_id WHERE NOT (orders.shopify_created_at = keep_orders.min_created) ORDER BY "orders"."id" ASC LIMIT $2  [["LIMIT", 5000], ["LIMIT", 1]]

它缺少关键字join。

对于如何以另一种方式重构查询/完成它的任何帮助，我们将不胜感激。

Answer 1

如果使用joins调用字符串SQL片段，则需要指定所需的联接类型：

Order.select('orders.id').from(keep_orders, :keep_orders)
  .joins('JOIN orders ON keep_orders.shopify_order_id = orders.shopify_order_id')
  .where.not('orders.shopify_created_at = keep_orders.min_created')