Rails + PostgreSQL:如何获得至少订购过一次的产品+获取订单#和经理id

Question

我有这样的模型：

class Product < ActiveRecord::Base
  has_many :orders
end

class Order < ActiveRecord::Base
 belongs_to :product
end

我想得到至少订购过一次的所有产品的列表，以及订单何时(orders.created_at)、订单代码(orders.no)和处理订单(orders.processed_by_admin_id)的管理员的时间戳。最后，我想列出orders.created_at的输出。

在谷歌上搜索了一段时间之后，我想出了这样的方法：

Product.joins(:orders)
       .group("(products.id) having count(products.id) > 0")
       .select("products.*", "max(orders.created_at) as last_order_time")
       .sort_by(&:last_order_time).reverse

这使我得到了至少订购一次的产品清单，并按时间戳排序。然而，我被塞在这里，因为还不能获取orders.no和orders.processed_by_admin_id。我不知道我所遵循的程序是否正确。

所需的输出类似于：

products.* | last_order_time | order_no | order_processed_by_admin_id

任何建议都很感激。

Answer 1

我想得到至少订购过一次的所有产品的列表，以及订单何时(orders.created_at)、订单代码(orders.no)和处理订单(orders.processed_by_admin_id)的管理员的时间戳。最后，我想列出orders.created_at的输出。

在编写复杂的查询时，我喜欢先编写普通的SQL。

让我们从一个查询开始，以获得每个产品的最新订单。

select product_id, id, 
  row_number() over ( 
    partition by product_id order by created_at 
  ) as recency_ranking 
from orders
;

这叫做窗口函数。对于每个产品，最近的订单将有一个recency_ranking为1。现在我们可以将这个查询加入到我们的products表中。

select p.*,
  x.id as order_id,
  x.no as order_number,
  x.order_processed_by_admin_id,
  x.created_at as order_created_at
from products p
inner join (
  select product_id, 
    id, 
    created_at, 
    row_number() over ( 
      partition by product_id order by created_at 
    ) as recency_ranking,
    no,
    order_processed_by_admin_id 
  from orders
) x
  on x.product_id = p.id
    and x.recency_ranking = 1
order by x.created_at
;

当我们加入这样的另一个查询时，它被称为子查询。注意我们如何加入on recency_ranking = 1。

要在ActiveRecord中运行整个查询，我建议使用find_by_sql

products = Product.find_by_sql('select p.*, x.id ...')
products.first.order_created_at #=> '2019-01-01 ...'