CODEWITHSUNDEEP
ruby-on-railsactiverecord
Roberto Pezzali
Roberto Pezzali

Reputation: 2494

Activerecord group not working

I'm trying to create a graph with the count of users registered on my website every day.

I use:

User.group('date(created_at)').count

I'm following this suggestion but I have an error:

@users.group("DATE_TRUNC('day', created_at)").count
   (0.6ms)  SELECT COUNT(*) AS count_all, DATE_TRUNC('day', created_at) AS date_trunc_day_created_at FROM "users" GROUP BY DATE_TRUNC('day', created_at) ORDER BY created_at DESC
PG::GroupingError: ERROR:  column "users.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ... GROUP BY DATE_TRUNC('day', created_at)  ORDER BY created_at...
                                                             ^
: SELECT COUNT(*) AS count_all, DATE_TRUNC('day', created_at) AS date_trunc_day_created_at FROM "users"  GROUP BY DATE_TRUNC('day', created_at)  ORDER BY created_at DESC
ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "users.created_at" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: ... GROUP BY DATE_TRUNC('day', created_at)  ORDER BY created_at...
                                                             ^
: SELECT COUNT(*) AS count_all, DATE_TRUNC('day', created_at) AS date_trunc_day_created_at FROM "users"  GROUP BY DATE_TRUNC('day', created_at)  ORDER BY created_at DESC
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `async_exec'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:768:in `exec_no_cache'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:138:in `block in exec_query'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:425:in `block in log'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activesupport-4.0.0/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract_adapter.rb:420:in `log'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql/database_statements.rb:137:in `exec_query'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/postgresql_adapter.rb:885:in `select'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/database_statements.rb:24:in `select_all'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:318:in `execute_grouped_calculation'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:222:in `perform_calculation'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:108:in `calculate'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-deprecated_finders-1.0.3/lib/active_record/deprecated_finders/relation.rb:84:in `calculate'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:111:in `calculate'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-deprecated_finders-1.0.3/lib/active_record/deprecated_finders/relation.rb:84:in `calculate'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/activerecord-4.0.0/lib/active_record/relation/calculations.rb:24:in `count'
    from (irb):21
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/railties-4.0.0/lib/rails/commands/console.rb:90:in `start'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/railties-4.0.0/lib/rails/commands/console.rb:9:in `start'
    from /Users/scrivoaroby/.rvm/gems/ruby-2.0.0-p353/gems/railties-4.0.0/lib/rails/commands.rb:64:in `<top (required)>'
    from bin/rails:4:in `require'
    from bin/rUser.group('date(created_at)')

Upvotes: 2

Views: 1055

Answers (1)

manu29.d
manu29.d

Reputation: 1568

Are you using order somewhere? (In a scope or earlier query maybe.)

User.group('date(created_at)').count #=> should return a hash with {date => (no. of users)}

But, as far as this is concerned, we don't know what @users is. Therefore, I'd suggest you to unscope it in this case since you only want the count.

@users.unscoped.group("DATE_TRUNC('day', created_at)").count

Upvotes: 2

Related Questions