按列分组，然后在Rails中获得另一列的总和 [英] Group by a column and then get the sum of another column in Rails

问题描述

我是使用Rails 4.2.1和Postgresql来创建个人股票投资组合（如Yahoo Finance）的初学者。转到 portfolio /：id应该为每个投资组合显示下面的show.html.erb文件。每个投资组合与持股表相关联，该表包含一个投资组合拥有的股票以及每个投资组合拥有多少股票的数据。 如果用户在同一投资组合中拥有两支相同的股票，则该应用必须从这两行中获取金额列的总和，并在总和之外添加一行相同的数据

I'm a beginner using Rails 4.2.1 with Postgresql to create a personal stock portfolio like in Yahoo Finance. Going to "portfolio/:id" should show the show.html.erb file below for each portfolio. Each portfolio is associated with the holdings table which contains the data for which stock a portfolio has and how many shares of each stock a portfolio has. If a user has two of the same stocks in the same portfolio, the app must get a sum of the "amount" column from both of those two rows and and make one row with the same data in addition to the sum of the amount.

模型

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

PortfoliosController

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

我似乎无法使其与每个循环一起使用

推荐答案

花了无数小时之后，我找到了解决方案。这很难找到。我实际上在此页面上找到了答案：导轨3中的多列

After countless hours spent, I found the solution. This was incredibly difficult to find. I actually found the answer on this page: group by + sum on multiple columns in rails 3

所以我更改的是：

在控制器中：

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

并且在视图中：

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

这篇关于按列分组，然后在Rails中获得另一列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！