ruby-on-rails - rails 如何在控制器中拥有 .group_by 关联的模型

Question

新开发者在这里。我有一个customer_controller.rb收集所有已完成的订单并按电子邮件地址对它们进行分组以获取我们的“客户”列表。

客户控制器：

class CustomerController < ApplicationController

  def index
    orders = Spree::Order.where(completed_at: from...to)
    @customers = orders.group_by(&:email)
  end

end

我有这样的视图模板：

<% @customers.each_pair do |email, orders| %>
<% total_orders = Spree::Order.where(state: "complete", email: email) %>
<% arr = orders  %>
<% amount = arr.inject(0) {|sum, hash| sum + hash[:payment_total]} %>
<tbody id="tbodyid">
  <tr>
    <td><%= email %></td>
    <th><%= orders.count %></td>
    <td><%= total_orders.count %></td>
    <td><%= total_orders.first.completed_at.strftime("%D") if total_orders.first.completed_at.present? %></td>
    <td>$<%= amount %></td>
  </tr>
</tbody>

<% end %>

为此处理模型的最佳方法是什么？将这种逻辑放在视图中是服务器的噩梦。我想有一个基于@customers实例的模型，但我不知道该怎么做。

我是否完全从这个方向着手并修改order_decorator.rb模型以获取客户集合？

编辑：为了回答下面的问题，这里有一个 Spree::Order 记录。所有订单都有电子邮件，并非所有订单都有 user_id。

#<Spree::Order id: 334, number: "R826173067", item_total: #<BigDecimal:7f9ee91516b8,'0.2198E2',18(18)>, total: #<BigDecimal:7f9ee91515f0,'0.1999E2',18(18)>, state: "complete", adjustment_total: #<BigDecimal:7f9ee91514b0,'-0.199E1',18(18)>, user_id: 45, completed_at: "2020-06-10 15:43:35", bill_address_id: 243, ship_address_id: 244, payment_total: #<BigDecimal:7f9ee9150e20,'0.1999E2',18(18)>, shipping_method_id: nil, shipment_state: "partial", payment_state: "paid", email: "[e-mail]", special_instructions: nil, created_at: "2020-06-10 15:42:28", updated_at: "2020-06-10 15:43:35", currency: "USD", last_ip_address: "127.0.0.1", created_by_id: 45, shipment_total: #<BigDecimal:7f9ee91500d8,'0.0',9(18)>, additional_tax_total: #<BigDecimal:7f9ee9150010,'0.0',9(18)>, promo_total: #<BigDecimal:7f9edd683ef8,'-0.199E1',18(18)>, channel: "spree", included_tax_total: #<BigDecimal:7f9edd683db8,'0.0',9(18)>, item_count: 2, approver_id: nil, approved_at: nil, confirmation_delivered: true, considered_risky: false, express: false, source: nil, purchased_via_subscription: false> 

标签： ruby-on-railspostgresqlspree

Answer 1

根据上面的答案，您可以进行查询，以满足您在控制器中的需求，而不需要太多其他内容。如果您的数据库是 Postgres，您可以执行以下操作：

@customers = Spree::Order.group(:email).select(:email, 'count(id) AS total_orders_count',
 'sum(payment_total) AS amount', 'array_agg(completed_at) AS completion_dates'
)

=> [#<Order:0x00007fbc8f0bd2d8 id: nil, email: '[e-mail]'>,
   #<Order:0x00007fbc8f0bd148 id: nil, email: '[e-mail]'>,
   #<Order:0x00007fbc8f0bcec8 id: nil, email: '[e-mail]'>,
   #<Order:0x00007fbc8f0bcd88 id: nil, email: '[e-mail]'>...]

请注意，这些没有 :id 因为它们是“虚拟”对象，因为每个对象实际上都是与该电子邮件地址匹配的一组订单。但它们都有以下方法：

total_orders => the count of completed orders for that email address
amount => sum of all payment_total for the orders belonging to that email
completion_dates => array of completed_on dates for that email's orders

你已经在数据库中完成了所有的选择和计算，所以它会尽可能快，你可以像这样显示它：

<% @customers.each do |customer| %>
<tbody id="tbodyid">
  <tr>
    <td><%= customer.email %></td>   <!-- email -->
    <td><%= customer.completion_dates.count{|date| 
             date > (#some date) && (#other date)}.count %></td> <!-- orders completed between dates -->
    <td><%= customer.total_orders_count %></td> <!-- lifetime orders -->
    <td><%= customer.completion_dates.sort.last %></td>  <!-- latest completed order -->
    <td>$<%= customer.amount %></td> <!-- total payments -->
  </tr>
</tbody>
<% end %>

customer.completion_dates.sort从最旧到最新排序。如果要使用最近的订单.last，请使用最旧的订单.first。

您可以添加您想要的任何字段，只要您使用 , , , 等函数聚合它们count(zzz) AS xxx。sum(zzz) AS xxx或者max(...如果min(...您希望每个订单的特定字段与该电子邮件相关联，您必须将其聚合到一个数组中array_agg(:some_column) as some_name。如果您在查询中多次使用任何聚合函数，则必须使用该AS xxxx格式，否则您将无法访问其他计数等。

@some_data = SomeClass.group(:email).select('sum(cost)', 
'count(id)')

@some_data.first.sum
#=> 23.45
@some_data.first.count
#=> 4

@some_data = SomeClass.group(:email).select('sum(cost) AS total_cost', 
'sum(tax) AS total_tax')

@some_data.first.total_cost
#=> 23.45
@some_data.first.total_tax
#=> 3.46