按名称、计数和总数分组，并使用Rails将其显示在视图中

Question

我以为我不再是菜鸟了，直到我偶然发现了这一点。

我正在尝试加载一个html列表，对每个用户的商店进行分组，并显示每个商店的收据数量和总金额。

在Sql中，我可以通过group by轻松实现这一点，所以我尝试在我的Shop Model中将以下代码加载到我的Rails控制台中。

 def self.group_receipts(user, search_hash=nil)

    shop_ids = Shop.pluck(:id) & Receipt.from_companies(user, search_hash).pluck(:shop_id)

    #greceipt means Grouped Receipt
    greceipt = Struct.new(:name, :number_of_receipts, :total)

    query = Shop.joins(:receipts).where('shops.id in (?)',shop_ids).select('shops.name,count(receipts.id) as number_of_receipts,sum(receipts.total) as total').group('shops.id')

    query

  end

这是我的输出

>> Shop.group_receipts(302).all
   (2.0ms)  SELECT id FROM "shops" 
   (3.0ms)  SELECT shop_id FROM "receipts" WHERE (receipts.id IN (SELECT receipts.id
 FROM receipts
 INNER JOIN shops on shops.id=receipts.shop_id
 INNER JOIN companies on companies.id = shops.company_id
 INNER JOIN user_companies on user_companies.company_id = companies.id
 WHERE user_companies.user_id = 302)) AND (receipts.is_manual is null or receipts.is_manual=false) ORDER BY receipts.created_at DESC
  Shop Load (2.0ms)  SELECT shops.name,count(receipts.id) as number_of_receipts,sum(receipts.total) as total FROM "shops" INNER JOIN "receipts" ON "receipts"."shop_id" = "shops"."id" WHERE (shops.id in (16)) GROUP BY shops.id
[#<Shop name: "Kirlin Osinski and Dooley">]

如果我的查询看起来没问题，为什么我的输出不像name, 10, 1000？

您在方法定义中找到的greceipt结构旨在创建一个结构，以便我们以后可以访问gs.name、gs.number_of_receipts、gs.total，但我不明白如何从上面提供的输出中加载此结构类型的对象列表:-S。

有人来救我吗？

Answer 1

在这个forum的帮助下，我了解到问题出在rails控制台中。

我最终得到了

型号：

def self.group_receipts(user, search_hash=nil)

    shop_ids = Shop.pluck(:id) & Receipt.from_companies(user, search_hash).pluck(:shop_id)
    query = Shop.joins(:receipts).where('shops.id in (?)',shop_ids).select('shops.name,count(receipts.id) as number_of_receipts,sum(receipts.total) as total').group('shops.id')
    query

end

控制器：

@receipts_per_shop = Shop.group_receipts(current_user)

局部视图_receipts_per_shop：

<table class="table table-striped">
  <thead>
  <th><%=t 'activerecord.attributes.shop.name'%></th>
  <th>#</th>
  <th>Total</th>
  </thead>
  <% if @shops.present? %>
      <%= render partial: '/shops/receipt_per_shop', collection: @receipts_per_shop %>
  <% else %>
      <tbody><tr><td colspan="3"><%= t('no_records') %></td></tr></tbody>
  <% end %>
</table> 

部分视图_receipt_per_shop

<% @receipts_per_shop.each do |rs| %>
    <tr>
      <td><%= rs.name %></td>
      <td><%= rs.number_of_receipts %></td>
      <td><%= rs.total %></td>
    </tr>
<% end %>