Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Order incompatible with Rails 5 (Postgres) #160

Closed
JonasNielsen opened this issue Jan 10, 2017 · 5 comments
Closed

Order incompatible with Rails 5 (Postgres) #160

JonasNielsen opened this issue Jan 10, 2017 · 5 comments

Comments

@JonasNielsen
Copy link

Using .order() or .reverse_order breaks with Postgres 9.3.1 and Rails 5.0.1.

PG::GroupingError: ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

$ rails c
Loading development environment (Rails 5.0.1)
2.3.1 :001 > User.group_by_day(:created_at).reverse_order.count
   (1.9ms)  SELECT COUNT(*) AS count_all, (DATE_TRUNC('day', (created_at::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen') + INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen' AS date_trunc_day_created_at_timestamptz_interval_0_second_at_time FROM "users" WHERE (created_at IS NOT NULL) GROUP BY (DATE_TRUNC('day', (created_at::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen') + INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen' ORDER BY "users"."id" DESC
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...econd') AT TIME ZONE 'Europe/Copenhagen' ORDER BY "users"."i...
                                                             ^
: SELECT COUNT(*) AS count_all, (DATE_TRUNC('day', (created_at::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen') + INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen' AS date_trunc_day_created_at_timestamptz_interval_0_second_at_time FROM "users" WHERE (created_at IS NOT NULL) GROUP BY (DATE_TRUNC('day', (created_at::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen') + INTERVAL '0 second') AT TIME ZONE 'Europe/Copenhagen' ORDER BY "users"."id" DESC
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `async_exec'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `block in exec_no_cache'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:589:in `block in log'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activesupport-5.0.1/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract_adapter.rb:583:in `log'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:598:in `exec_no_cache'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql_adapter.rb:585:in `execute_and_clear'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/postgresql/database_statements.rb:103:in `exec_query'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:373:in `select'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract/database_statements.rb:41:in `select_all'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/connection_adapters/abstract/query_cache.rb:95:in `select_all'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/relation/calculations.rb:299:in `execute_grouped_calculation'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/relation/calculations.rb:205:in `perform_calculation'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/relation/calculations.rb:121:in `calculate'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/activerecord-5.0.1/lib/active_record/relation/calculations.rb:40:in `count'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/groupdate-3.1.1/lib/groupdate/magic.rb:149:in `perform'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/groupdate-3.1.1/lib/groupdate/series.rb:14:in `method_missing'
	from (irb):1
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/railties-5.0.1/lib/rails/commands/console.rb:65:in `start'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/railties-5.0.1/lib/rails/commands/console_helper.rb:9:in `start'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/railties-5.0.1/lib/rails/commands/commands_tasks.rb:78:in `console'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/railties-5.0.1/lib/rails/commands/commands_tasks.rb:49:in `run_command!'
	from /Users/jonas/.rvm/gems/ruby-2.3.1@nikt-rails-5/gems/railties-5.0.1/lib/rails/commands.rb:18:in `<top (required)>'
	from bin/rails:4:in `require'
	from bin/rails:4:in `<main>'
@ankane
Copy link
Owner

ankane commented Jan 11, 2017

Hey @JonasNielsen, thanks for reporting. I'm not sure there's a great way to make this work with Rails 5, so I updated the docs.

@ankane ankane closed this as completed Jan 11, 2017
@zedtux
Copy link

zedtux commented May 30, 2017

@ankane I'm facing the same issue. I'm using the punching_bag gem and I would like to group the hits by months but I have this:

irb(main):011:0> Punch.group_by_month(:average_time).count
   (1.2ms)  SELECT COUNT(*) AS count_all, (DATE_TRUNC('month', (average_time::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Etc/UTC') + INTERVAL '0 second') AT TIME ZONE 'Etc/UTC' AS date_trunc_month_average_time_timestamptz_interval_0_second_at_ FROM "punches" WHERE (average_time IS NOT NULL) GROUP BY (DATE_TRUNC('month', (average_time::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Etc/UTC') + INTERVAL '0 second') AT TIME ZONE 'Etc/UTC' ORDER BY punches.average_time DESC
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "punches.average_time" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ...ERVAL '0 second') AT TIME ZONE 'Etc/UTC' ORDER BY punches.av...
                                                             ^
: SELECT COUNT(*) AS count_all, (DATE_TRUNC('month', (average_time::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Etc/UTC') + INTERVAL '0 second') AT TIME ZONE 'Etc/UTC' AS date_trunc_month_average_time_timestamptz_interval_0_second_at_ FROM "punches" WHERE (average_time IS NOT NULL) GROUP BY (DATE_TRUNC('month', (average_time::timestamptz - INTERVAL '0 second') AT TIME ZONE 'Etc/UTC') + INTERVAL '0 second') AT TIME ZONE 'Etc/UTC' ORDER BY punches.average_time DESC
	from (irb):11

@ankane
Copy link
Owner

ankane commented May 30, 2017

Hey @zedtux, see #100.

@zedtux
Copy link

zedtux commented May 30, 2017

Thank you @ankane

@djdarkbeat
Copy link

Adding .unscope(:order) as recommended fixed the issue for me pushing from Rails 4.2 to Rails 5.0.1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@djdarkbeat @JonasNielsen @ankane @zedtux