postgresql Rails：必须出现在 GROUP BY 子句中或在聚合函数中使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17892216/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails: must appear in the GROUP BY clause or be used in an aggregate function
提问by goo
I'm following along Bate's tut on graphs & charts but am getting an error.
我正在按照 Bate 对图表和图表的介绍进行操作，但出现错误。
This is the error I'm getting
这是我得到的错误
PG::Error: ERROR: column "orders.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT created_at, sum(amount) as total_amount FROM "orders"...
^
: SELECT created_at, sum(amount) as total_amount FROM "orders" WHERE ("orders"."created_at" BETW
And this is the code I'm using in my Orders model
这是我在我的订单模型中使用的代码
def self.total_grouped_by_day(start)
orders = where(created_at: start.beginning_of_day..Time.zone.now)#.all
orders = orders.group("date(created_at)")
orders = orders.select("date(created_at), sum(amount) as total_amount")
orders.group_by { |order| order.created_at.to_date }
end
and in my helper
在我的帮手
def orders_chart_data
orders_by_day = Order.total_grouped_by_day(3.weeks.ago)
(3.weeks.ago.to_date..Date.today).map do |date|
{
created_at: date,
price: orders_by_day[date].first.try(:total_amount) || 0
}
end
end
I'm using Postgres (which I'm pretty sure is the main reason I'm getting this error) & Rails 3.x.x. Why is this error occurring & how do I fix it?
我正在使用 Postgres（我很确定这是我收到此错误的主要原因）和 Rails 3.xx 为什么会发生此错误以及如何修复它？
Thank you in advance
先感谢您
In my console:
在我的控制台中：
Order.where(created_at: 3.weeks.ago.beginning_of_day..Time.zone.now).group("date(created_at)").select("date(created_at), sum(amount) as total_amount")
& this is what I get
&这就是我得到的
[#<Order >, #<Order >, #<Order >]
weird..
奇怪的..
running this in my console: Order.where(created_at: 3.weeks.ago.beginning_of_day..Time.zone.now).group("date(created_at)").select("date(created_at), sum(amount) as total_amount").first.total_amount
在我的控制台中运行它： Order.where(created_at: 3.weeks.ago.beginning_of_day..Time.zone.now).group("date(created_at)").select("date(created_at), sum(amount) as total_amount").first.total_amount
gives me this:
给我这个：
Creating scope :page. Overwriting existing method Order.page.
Order Load (18.1ms) SELECT date(created_at), sum(amount) as total_amount FROM "orders" WHERE ("orders"."created_at" BETWEEN '2013-07-05 00:00:00.000000' AND '2013-07-26 23:50:38.876609') GROUP BY date(created_at) LIMIT 1
=> "10.0"
采纳答案by Philip Hallstrom
You are grouping on date(created_at)but not selecting that column. Change this line:
您正在分组date(created_at)但未选择该列。改变这一行：
orders = orders.select("created_at, sum(amount) as total_amount")
to this:
对此：
orders = orders.select("date(created_at), sum(amount) as total_amount")
That will also result in a change to the next line's group_by as well.
这也将导致对下一行的 group_by 的更改。
From one of my projects, using slightly different attributes, but doing the same thing as you:
从我的一个项目中，使用稍微不同的属性，但做的事情和你一样：
1.9.3p327 > User.group('date(created_at)').select('date(created_at), sum(id) as total_amount').first.attributes
User Load (1.2ms) SELECT date(created_at), sum(id) as total_amount FROM "users" GROUP BY date(created_at) LIMIT 1
=> {"date"=>"2011-09-27", "total_amount"=>"657"}
回答by Erwin Brandstetter
A cast to date would be simplest & fastest. Here the raw SQL since I am no expert with Ruby syntax (and no fan of ORMs butchering SQL in general).
迄今为止的演员表将是最简单和最快的。这里是原始 SQL，因为我不是 Ruby 语法的专家（并且一般不喜欢 ORMs 屠杀 SQL）。
SELECT created_at::date, sum(amount) AS total_amount
FROM orders
WHERE created_at ...
GROUP BY created_at::date
ORDER BY created_at::date
