导轨求和多个字段 [英] rails sum multiple fields

问题描述

我正在尝试在Rails中执行此简单的操作：

I'm trying to do this supposingly simple operation in rails:

self.timesheets.select("sum(total) as total, sum(quantity) as quantity").first where self is a project

当我在控制台模式下执行此操作，它可以工作，但会向我显示3列：

When I do it in console mode, it works but it renders me 3 columns:

[#<Timesheet id: nil, quantity: 120.1, total: 6245.2>]

在应用程序中运行它时，出现此错误消息：

When I run it in the app, I get this error message:

PG::GroupingError - ERROR:  column "timesheets.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ..."  WHERE "timesheets"."project_id" = $1  ORDER BY "timesheet...
                                                             ^
:
  activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:811:in `prepare_statement'
  activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:772:in `exec_cache'
  schema_plus (1.3.1) lib/schema_plus/active_record/connection_adapters/postgresql_adapter.rb:231:in `exec_cache_with_schema_plus'
  activerecord (4.0.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:139:in `block in exec_query'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
  activesupport (4.0.0) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
  activerecord (4.0.0) lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
  activerecord (4.0.0) lib/active_record/connection_adapters/postgresql_adapter.rb:885:in `select'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `block in select_all'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/query_cache.rb:76:in `cache_sql'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/query_cache.rb:61:in `select_all'
  activerecord (4.0.0) lib/active_record/querying.rb:36:in `find_by_sql'
  activerecord (4.0.0) lib/active_record/relation.rb:585:in `exec_queries'
  activerecord (4.0.0) lib/active_record/relation.rb:471:in `load'
  activerecord (4.0.0) lib/active_record/relation.rb:220:in `to_a'
  activerecord (4.0.0) lib/active_record/relation/finder_methods.rb:325:in `find_first'
  activerecord (4.0.0) lib/active_record/relation/finder_methods.rb:90:in `first'
  activerecord-deprecated_finders (1.0.3) lib/active_record/deprecated_finders/relation.rb:129:in `first'
  app/models/project.rb:27:in `recalc_spendings'
  app/models/timesheet.rb:44:in `escalate_sums'
  activesupport (4.0.0) lib/active_support/callbacks.rb:452:in `_run__1475279931726113629__save__callbacks'
  activesupport (4.0.0) lib/active_support/callbacks.rb:80:in `run_callbacks'
  activerecord (4.0.0) lib/active_record/callbacks.rb:299:in `create_or_update'
  activerecord (4.0.0) lib/active_record/persistence.rb:106:in `save'
  activerecord (4.0.0) lib/active_record/validations.rb:51:in `save'
  activerecord (4.0.0) lib/active_record/attribute_methods/dirty.rb:32:in `save'
  activerecord (4.0.0) lib/active_record/transactions.rb:270:in `block (2 levels) in save'
  activerecord (4.0.0) lib/active_record/transactions.rb:326:in `block in with_transaction_returning_status'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `block in transaction'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/database_statements.rb:210:in `within_new_transaction'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/database_statements.rb:202:in `transaction'
  activerecord (4.0.0) lib/active_record/transactions.rb:209:in `transaction'
  activerecord (4.0.0) lib/active_record/transactions.rb:323:in `with_transaction_returning_status'
  activerecord (4.0.0) lib/active_record/transactions.rb:270:in `block in save'
  activerecord (4.0.0) lib/active_record/transactions.rb:281:in `rollback_active_record_state!'
  activerecord (4.0.0) lib/active_record/transactions.rb:269:in `save'
  app/controllers/timesheets_controller.rb:73:in `block in create'
  actionpack (4.0.0) lib/action_controller/metal/mime_responds.rb:363:in `retrieve_collector_from_mimes'
  actionpack (4.0.0) lib/action_controller/metal/mime_responds.rb:189:in `respond_to'
  app/controllers/timesheets_controller.rb:72:in `create'
  actionpack (4.0.0) lib/action_controller/metal/implicit_render.rb:4:in `send_action'
  actionpack (4.0.0) lib/abstract_controller/base.rb:189:in `process_action'
  actionpack (4.0.0) lib/action_controller/metal/rendering.rb:10:in `process_action'
  actionpack (4.0.0) lib/abstract_controller/callbacks.rb:18:in `block in process_action'
  activesupport (4.0.0) lib/active_support/callbacks.rb:463:in `_run__3822865716334379624__process_action__callbacks'
  activesupport (4.0.0) lib/active_support/callbacks.rb:80:in `run_callbacks'
  actionpack (4.0.0) lib/abstract_controller/callbacks.rb:17:in `process_action'
  actionpack (4.0.0) lib/action_controller/metal/rescue.rb:29:in `process_action'
  actionpack (4.0.0) lib/action_controller/metal/instrumentation.rb:31:in `block in process_action'
  activesupport (4.0.0) lib/active_support/notifications.rb:159:in `block in instrument'
  activesupport (4.0.0) lib/active_support/notifications/instrumenter.rb:20:in `instrument'
  activesupport (4.0.0) lib/active_support/notifications.rb:159:in `instrument'
  actionpack (4.0.0) lib/action_controller/metal/instrumentation.rb:30:in `process_action'
  actionpack (4.0.0) lib/action_controller/metal/params_wrapper.rb:245:in `process_action'
  activerecord (4.0.0) lib/active_record/railties/controller_runtime.rb:18:in `process_action'
  actionpack (4.0.0) lib/abstract_controller/base.rb:136:in `process'
  actionpack (4.0.0) lib/abstract_controller/rendering.rb:44:in `process'
  actionpack (4.0.0) lib/action_controller/metal.rb:195:in `dispatch'
  actionpack (4.0.0) lib/action_controller/metal/rack_delegation.rb:13:in `dispatch'
  actionpack (4.0.0) lib/action_controller/metal.rb:231:in `block in action'
  actionpack (4.0.0) lib/action_dispatch/routing/route_set.rb:80:in `dispatch'
  actionpack (4.0.0) lib/action_dispatch/routing/route_set.rb:48:in `call'
  actionpack (4.0.0) lib/action_dispatch/journey/router.rb:71:in `block in call'
  actionpack (4.0.0) lib/action_dispatch/journey/router.rb:59:in `call'
  actionpack (4.0.0) lib/action_dispatch/routing/route_set.rb:655:in `call'
  newrelic_rpm (3.6.9.171) lib/new_relic/rack/error_collector.rb:50:in `call'
  newrelic_rpm (3.6.9.171) lib/new_relic/rack/agent_hooks.rb:28:in `call'
  newrelic_rpm (3.6.9.171) lib/new_relic/rack/browser_monitoring.rb:23:in `call'
  newrelic_rpm (3.6.9.171) lib/new_relic/rack/developer_mode.rb:42:in `call'
  warden (1.2.3) lib/warden/manager.rb:35:in `block in call'
  warden (1.2.3) lib/warden/manager.rb:34:in `call'
  rack (1.5.2) lib/rack/etag.rb:23:in `call'
  rack (1.5.2) lib/rack/conditionalget.rb:35:in `call'
  rack (1.5.2) lib/rack/head.rb:11:in `call'
  remotipart (1.0.5) lib/remotipart/middleware.rb:30:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/params_parser.rb:27:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/flash.rb:241:in `call'
  rack (1.5.2) lib/rack/session/abstract/id.rb:225:in `context'
  rack (1.5.2) lib/rack/session/abstract/id.rb:220:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/cookies.rb:486:in `call'
  activerecord (4.0.0) lib/active_record/query_cache.rb:36:in `call'
  activerecord (4.0.0) lib/active_record/connection_adapters/abstract/connection_pool.rb:626:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/callbacks.rb:29:in `block in call'
  activesupport (4.0.0) lib/active_support/callbacks.rb:373:in `_run__1689713881852047757__call__callbacks'
  activesupport (4.0.0) lib/active_support/callbacks.rb:80:in `run_callbacks'
  actionpack (4.0.0) lib/action_dispatch/middleware/callbacks.rb:27:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/reloader.rb:64:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/remote_ip.rb:76:in `call'
  better_errors (1.0.1) lib/better_errors/middleware.rb:84:in `protected_app_call'
  better_errors (1.0.1) lib/better_errors/middleware.rb:79:in `better_errors_call'
  better_errors (1.0.1) lib/better_errors/middleware.rb:56:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/debug_exceptions.rb:17:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/show_exceptions.rb:30:in `call'
  railties (4.0.0) lib/rails/rack/logger.rb:38:in `call_app'
  railties (4.0.0) lib/rails/rack/logger.rb:21:in `block in call'
  activesupport (4.0.0) lib/active_support/tagged_logging.rb:67:in `block in tagged'
  activesupport (4.0.0) lib/active_support/tagged_logging.rb:25:in `tagged'
  activesupport (4.0.0) lib/active_support/tagged_logging.rb:67:in `tagged'
  railties (4.0.0) lib/rails/rack/logger.rb:21:in `call'
  quiet_assets (1.0.2) lib/quiet_assets.rb:18:in `call_with_quiet_assets'
  actionpack (4.0.0) lib/action_dispatch/middleware/request_id.rb:21:in `call'
  rack (1.5.2) lib/rack/methodoverride.rb:21:in `call'
  rack (1.5.2) lib/rack/runtime.rb:17:in `call'
  activesupport (4.0.0) lib/active_support/cache/strategy/local_cache.rb:83:in `call'
  rack (1.5.2) lib/rack/lock.rb:17:in `call'
  actionpack (4.0.0) lib/action_dispatch/middleware/static.rb:64:in `call'
  railties (4.0.0) lib/rails/engine.rb:511:in `call'
  railties (4.0.0) lib/rails/application.rb:97:in `call'
  rack (1.5.2) lib/rack/content_length.rb:14:in `call'
  thin (1.6.1) lib/thin/connection.rb:82:in `block in pre_process'
  thin (1.6.1) lib/thin/connection.rb:80:in `pre_process'
  thin (1.6.1) lib/thin/connection.rb:55:in `process'
  thin (1.6.1) lib/thin/connection.rb:41:in `receive_data'
  eventmachine (1.0.3) lib/eventmachine.rb:187:in `run'
  thin (1.6.1) lib/thin/backends/base.rb:73:in `start'
  thin (1.6.1) lib/thin/server.rb:162:in `start'
  rack (1.5.2) lib/rack/handler/thin.rb:16:in `run'
  rack (1.5.2) lib/rack/server.rb:264:in `start'
  railties (4.0.0) lib/rails/commands/server.rb:84:in `start'
  railties (4.0.0) lib/rails/commands.rb:78:in `block in <top (required)>'
  railties (4.0.0) lib/rails/commands.rb:73:in `<top (required)>'
  bin/rails:4:in `<top (required)>'
  ruby-debug-ide (0.4.22) lib/ruby-debug-ide.rb:86:in `debug_program'
  ruby-debug-ide (0.4.22) bin/rdebug-ide:110:in `<top (required)>'
  -e:1:in `<main>'

我并不感到惊讶

但是为什么它是由activerecord添加的，我该如何解决呢？

But why is it added by activerecord and how can I solve this?

谢谢！

编辑

感谢PriteshJ，我知道ID是在查询末尾添加的第一语句中包含的，它告诉ActiveRecord它是单个记录，而不是集合。

Thanks to PriteshJ, I understood that the ID was included by the "first" statement added at the end of my query to tell activerecord that it is a single record, not a collection.

所以我知道我必须摆脱第一个子句，但是当我这样做时，我收到另一个错误消息：

So I know that I must get rid of first clause but when I do this, I get another error message:

NoMethodError: undefined method `total' for #<ActiveRecord::Relation::ActiveRecord_Relation_Timesheet:0x007fe73da50470>
from /usr/local/rvm/gems/ruby-2.0.0-p0/gems/activerecord-4.0.0/lib/active_record/relation/delegation.rb:121:in `method_missing' 

这很正常，因为它正在查找关系而不是单个记录，但我不知道如何解决

This is normal because it is looking into a relation instead of a single record but I don't know how to solve it.

虽然应该很简单...

Should be simple though...

推荐答案

就像您是在 timesheets.id 下订购结果，或者您有一些默认订单范围或要添加订单的地方。

It looks like you are ordering the results by timesheets.id or you have some default order scope or somewhere you are adding a order.

删除顺序或添加列以选择查询的排序依据。

Remove the ordering or add the column to select by which you are ordering the query.

更新：

尝试使用

self.timesheets.select("sum(total) as total, sum(quantity) as quantity").to_a.first

.first 将尝试对查询进行排序并选择第一个元素，因此首先使用 .to_a 首先获取结果，然后执行 .fi第一个

.first in activerecord will try to sort the query and pick first element so first by using .to_a take the result first then do .first

这篇关于导轨求和多个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！