Search code examples
ruby-on-railsrails-activerecordarel

Is there a clean way to use ORDER BY inside an Arel NamedFunction Node?

I'm using ActiveRecord 4.2 / Arel 6.0 / Postgres and have the following inputs:

  • An Arel::Attributes::Attribute from an Arel::Table (column)
  • Several Arel::Nodes::Ordering nodes (orders)

I want to build an Arel::Nodes::NamedFunction with an aggregate function that includes the column specified by the Attribute and is ordered by the Ordering nodes.

The resulting SQL could look something like:

array_agg("posts"."id" ORDER BY "posts"."published_at" DESC)

My current solution is to first build an Arel::Nodes::SelectStatement, add the column and orders to it, convert it to SQL, strip the leading SELECT keyword, wrap it in an Arel::Nodes::SqlLiteral and pass that to the NamedFunction node:

select = Arel::Nodes::SelectStatement.new
select.cores.last.projections << column
select.orders = orders

sql = select.to_sql.sub(/^SELECT /, '')
literal = Arel::Nodes::SqlLiteral.new(sql)

array_agg = Arel::Nodes::NamedFunction.new('array_agg', [literal])

Obviously, this is a huge hack.

Keeping the ORDER BY outside the aggregate function is not an option, because it would conflict with the GROUP BY used to aggregate.

So is there a cleaner way to achieve this without abusing SelectStatement / SelectManager?

Solution

  • The solution is to use an Arel::Nodes::InfixOperation to build the "ORDER BY" node and an Arel::Nodes::StringJoin to build a comma separated list of Arel::Nodes::Ordering:

    ordering = Arel::Nodes::StringJoin.new(orders)
order_by = Arel::Nodes::InfixOperation.new('ORDER BY', column, ordering)
array_agg = Arel::Nodes::NamedFunction.new('array_agg', [order_by])