Rails 中 ActiveRecord 中的多个 sum()

发布于 2024-11-01 00:13:04 字数 428 浏览 0 评论 0原文

我想将多个 sum() 链接到一个组，同时选择其他字段。我还更喜欢使用 ActiveRecord 方法来执行此操作，而不是手动构造 sql 字符串，因为稍后我可能会修改 ActiveRecord 继承类的行为。

例如，我想

select user_id, sum(cost) as total_cost, sum(quantity) as total_quantity from line_items group by user_id

用以下内容来表示该语句（作为示例）：

LineItem.select(:user_id).group(:user_id).sum(:cost).sum(:quantity)

原因是我稍后可能会添加额外的分组和 where 子句，所有总和将具有这些共同点。

原文

I'd like to link multiple sums() to a single group by while selecting other fields at the same time. I'd also prefer to use the ActiveRecord methods to do this rather than construct a sql string manually, as I may modify the behavior of inherited classes of ActiveRecord later.

For example I'd like to represent the statement (as an example)

select user_id, sum(cost) as total_cost, sum(quantity) as total_quantity from line_items group by user_id

with something like:

LineItem.select(:user_id).group(:user_id).sum(:cost).sum(:quantity)

The reason being I may add additional group-bys and where-clauses later, which all the sums will have in common.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

耀眼的星火 2024-11-08 00:13:04

这对我有用：

require "active_record"
require "logger"

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection :adapter => "postgresql", :database => "francois"

ActiveRecord::Base.connection.execute "DROP TABLE IF EXISTS values"
ActiveRecord::Base.connection.execute "CREATE TABLE values(user_id INTEGER NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, cost INTEGER NOT NULL DEFAULT 2)"

class Value < ActiveRecord::Base
end

2.times do
  5.times do |n|
    Value.create!(:user_id => n)
  end
end

Value.group(:user_id).select('user_id, SUM(cost) AS total_cost, SUM(quantity) AS total_quantity').each do |value|
  p [value.user_id, value.total_quantity, value.total_cost]
end

我尝试了 sum(:cost, :quantity)，但 #sum 并不期望以这种方式定义参数。我也尝试了 sum(:cost => :total_cost, :quantity => :total_quantity)，但没有成功。

This works for me:

require "active_record"
require "logger"

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.establish_connection :adapter => "postgresql", :database => "francois"

ActiveRecord::Base.connection.execute "DROP TABLE IF EXISTS values"
ActiveRecord::Base.connection.execute "CREATE TABLE values(user_id INTEGER NOT NULL, quantity INTEGER NOT NULL DEFAULT 1, cost INTEGER NOT NULL DEFAULT 2)"

class Value < ActiveRecord::Base
end

2.times do
  5.times do |n|
    Value.create!(:user_id => n)
  end
end

Value.group(:user_id).select('user_id, SUM(cost) AS total_cost, SUM(quantity) AS total_quantity').each do |value|
  p [value.user_id, value.total_quantity, value.total_cost]
end

I tried sum(:cost, :quantity), but #sum doesn't expect arguments defined this way. I also tried sum(:cost => :total_cost, :quantity => :total_quantity), to no avail.

回复收藏 0 原文

~没有更多了~