Can't group by result of a named function

Question

I'm getting NoMethodError: undefined method relation' for <Arel::Nodes::NamedFunction:0x7633> when trying to group by the results of a named function.

class Appointment < ActiveRecord::Base
  scope :group_by_date, -> { group(date_column) }

  def self.date_column
    Arel::Nodes::NamedFunction.new(:date, [Appointment.arel_table[:start_time]], 'date')
  end
end

Appointment.group_by_date.count
# results in NoMethodError: undefined method `relation' for #<Arel::Nodes::NamedFunction:0x7633>

This seems perfectly reasonable, so I'm not sure why it's creating an error. I'm pretty sure this was was possible in earlier versions of rails as indicated by this SO answer.

I'm expecting to get something akin to the following sql:

SELECT date(appointments.start_time) AS date, COUNT(appointments.*) AS count
FROM appointments
GROUP BY date(appointments.start_time)

Is there a way to get this to work? Is there a way to convert the Arel::Nodes::NamedFunction to Arel::Attributes::Attribute

This is obviously a naive attempt at a solution (it didn't work):

function = Arel::Nodes::NamedFunction.new('date', [Appointment[:start_time]])
attr = Arel::Attributes::Attribute.new(function)
Appointment.group(attr).to_sql
# NoMethodError: undefined method `table_alias' for #<Arel::Nodes::NamedFunction:0x4b8>

I really don't want to have to drop back to using Appointment.group( 'date(appointments.start_time)' ) as I do a lot of scope merging and joining and string cause havoc as they don't always scope to the correct table.

Environment: rails (4.1.8), activerecord (4.1.8), arel (5.0.1.20140414130214)

Answer 1

This is a tricky one. First of all, your NamedFunction definition has a couple, totally non-obvious issues: first, the first argument needs to be a string, or else you get this:

irb(main):040:0> User.group( Arel::Nodes::NamedFunction.new(:date, [User.arel_table[:created_at]], 'date') )
TypeError: no implicit conversion of Symbol into String
        from /var/lib/gems/2.1.0/gems/arel-6.0.2/lib/arel/collectors/plain_string.rb:13:in `<<'
        from /var/lib/gems/2.1.0/gems/arel-6.0.2/lib/arel/visitors/to_sql.rb:458:in `visit_Arel_Nodes_NamedFunction'

Secondly, you need to drop the third argument (the alias), or else Arel tries to shove it into the GROUP BY clause and you get an error (at least in Postgres):

irb(main):045:0> User.group( Arel::Nodes::NamedFunction.new('date', [User.arel_table[:created_at]], 'date') )
  User Load (4.9ms)  SELECT "users".* FROM "users" GROUP BY date("users"."created_at") AS date
PG::SyntaxError: ERROR:  syntax error at or near "AS"
LINE 1: ...".* FROM "users" GROUP BY date("users"."created_at") AS date
                                                                ^
: SELECT "users".* FROM "users" GROUP BY date("users"."created_at") AS date
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "AS"
LINE 1: ...".* FROM "users" GROUP BY date("users"."created_at") AS date
                                                                ^
: SELECT "users".* FROM "users" GROUP BY date("users"."created_at") AS date
        from /var/lib/gems/2.1.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:596:in `async_exec'

You haven't noticed these because the error you're getting is actually related to the .count call; without it, this actually works now:

irb(main):066:0> nf = Arel::Nodes::NamedFunction.new('date', [User.arel_table[:created_at]])
=> #<Arel::Nodes::NamedFunction:0x9dc9ca0 @expressions=[#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0xbdb689c @name="users", @engine=User(id: integer, name: string, age: integer, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>, name=:created_at>], @alias=nil, @distinct=false, @name="date">
irb(main):067:0> User.group(nf).select(nf)
  User Load (2.5ms)  SELECT date("users"."created_at") FROM "users" GROUP BY date("users"."created_at")
=> #<ActiveRecord::Relation [#<User id: nil>]>

So, why doesn't .count work? Unfortunately, following the stack trace, this just looks like a bug. The count method goes into ActiveRelation::Calculations#execute_grouped_calculation and simply goes down a bad path. There's no support in there for handling a NamedFunction from Arel.

However, you can work around it:

irb(main):017:0> User.group(nf).pluck('count(*)').first
   (2.5ms)  SELECT count(*) FROM "users" GROUP BY date("users"."created_at")
=> 1

So... yeah. You might want to open an Issue for this in Rails. I'd personally just suggest you group by the string expression and save yourself some headache!