Arel - How to coalesce a field and a string literal in an Arel query?

Question

I inherited a big, complicated Arel query that pulled from a number of tables. A new requirement says that if one of those tabels doesn't have a value for a particular field ("district"), I should default it to, "Global".

Because of high levels of abstraction in the query building and in the view, there is no good way for me to insert that default before or after the query. So, I need to insert a default value into a field in the Arel query if a field is nil or if there is no matching row.

How can I default a field to a string value in an Arel query?

Answer 1

I found all the pieces for this searching the net, but didn't find all the parts glued together, so I'm sharing that here, so I can find it again next time!

SQL's coalesce is used to supply the default value.

To get coalesce into the query, I use Arel::Nodes::NamedFunction. NamedFunction lets you refer to any SQL function that Arel doesn't know about.

Note the single quotes within the SqlLiteral string.

supplier_table = Supplier.arel_table

district = Arel::Nodes::NamedFunction.new(
  'coalesce',
  [supplier_table[:district], Arel::Nodes::SqlLiteral.new("'Global'") ]
).as('district')

ProductHistoryResult.joins(some_join, some_other_join).select(
  [this_arel, that_arel, the_other_arel, district]
).where(product_history_request_id: id)