ruby-on-rails - 按列分组，然后在 Rails 中获取另一列的总和

Question

我是初学者，使用 Rails 4.2.1 和 Postgresql 来创建个人股票投资组合，就像在 Yahoo Finance 中一样。转到“portfolio/:id”应该显示下面每个投资组合的 show.html.erb 文件。每个投资组合都与 holdings 表相关联，该表包含投资组合拥有的股票以及投资组合拥有的每只股票的多少股份的数据。 如果用户在同一个投资组合中有两只相同的股票，则该应用必须从这两行中获取“数量”列的总和，并且除了金额。

模型

User
has_many :portfolios
has_many :holdings

Portfolio
belongs_to :user
has_many :holdings

Holding
belongs_to :user
belongs_to :stock
belongs_to :portfolio

Stock
has_many :holdings

数据库架构

table "holdings"
t.integer  "user_id"
t.integer  "stock_id"
t.integer  "portfolio_id"
t.integer  "amount"
end

table "portfolios"
t.integer  "user_id"
t.string   "name"
end

table "stocks"
t.string   "symbol"
end

投资组合 Controller

def show
@holdings = @portfolio.holdings # can't figure out what to do next.
end

# As it is, this just shows rows for every new holding transaction.

show.html.erb

 <% @holdings.each do |holdings| %>
 <tr>
    <td><%= holdings.portfolio.user.name %></td>
    <td><%= holdings.portfolio.name %></td>
    <td><%= holdings.stock.symbol %></td>
    <td><%= holdings.amount %></td>
  </tr>
 <% end %>

错误

@holdings = @portfolio.holdings.sum(:amount, :group => "stock_id" :order=> "sum(amount) DESC")

导致此错误:

NoMethodError at /portfolios/22
undefined method `each' for 171:Fixnum

# And by the way, 171 is the correct sum but it needs to work with the looped table

这个:

@holdings = @portfolio.holdings.group(:portfolio_id).sum(:amount)

导致此错误:

NoMethodError at /portfolios/22
undefined method `portfolio' for [22, 171]:Array

我似乎无法让它与每个循环一起工作。

Answer 1

在花费了无数个小时之后，我找到了解决方案。这很难找到。我实际上在这个页面上找到了答案:group by + sum on multiple columns in rails 3

所以我改变的是:

在 Controller 中:

@holdings = @portfolio.holdings.select(:stock_id, :portfolio_id, 
"SUM(amount) as sum_amount").group(:stock_id,
:portfolio_id).order("sum_amount DESC")

在 View 中:

<tr>
  <td><%= holdings.portfolio.user.name %></td>
  <td><%= holdings.portfolio.name %></td>
  <td><%= holdings.stock.symbol %></td>
  <td><%= holdings.sum_amount %></td>
  </tr>
<% end %>