How do I use functions like CONCAT(), etc. in ARel?

Question

Is there a way to have ARel write (sanitized, possibly aliased, etc.) column names into CONCAT() and other SQL functions?

Here's how to do it with AVG()...

?> name = Arel::Attribute.new(Arel::Table.new(:countries), :name)
=> #<struct Arel::Attributes::Attribute [...]
?> population = Arel::Attribute.new(Arel::Table.new(:countries), :population)
=> #<struct Arel::Attributes::Attribute [...]
?> Country.select([name, population.average]).to_sql
=> "SELECT `countries`.`name`, AVG(`countries`.`population`) AS avg_id FROM `countries`"

(yes, I know that avg_id would be the same in every row, just trying to illustrate my question)

So what if I wanted a different function?

?> Country.select(xyz).to_sql # Arel::Concat.new(name, population) or something?
=> "SELECT CONCAT(`countries`.`name`, ' ', `countries`.`population`) AS concat_id FROM `countries`"

Thanks!

Sequel has a way of selecting columns as `"...".lit` meaning "literal SQL" instead of being interpreted as a string for situations like this. That disables SQL escaping so you can inject whatever you want. Not sure what the AREL equivalent is, but maybe that's an idea. — tadman, Feb 16 '12 at 04:32

score 18 · Accepted Answer · edited Sep 26 '13 at 20:52

18

Use NamedFunction:

name = Arel::Attribute.new(Arel::Table.new(:countries), :name)
func = Arel::Nodes::NamedFunction.new 'zomg', [name]
Country.select([name, func]).to_sql

edited Sep 26 '13 at 20:52

graywh

9,640
2
29
27

answered Feb 16 '12 at 19:03

Aaron Patterson

2,275
1
20
11

1

It looks like NamedFunction need to be attached to an attribute? e.g. if I want to group things by dates, could I do `.group(arel_table[:created_at].date)`, without attaching the "date" function directly on "created_at" so I can reuse it elsewhere? (sql: `GROUP BY DATE(created_at)`) – Andrew Vit Apr 05 '12 at 02:38
5

@AaronPatterson Using your code, `func.to_sql` gives `zomg('countries', 'name')`. I think, the code should be `...Function.new 'zomg', [name]` (with square brackets). – RocketR Nov 15 '12 at 22:29

score 0 · Answer 2 · answered Jan 26 '21 at 13:33

You can also use the Arel Extensions gem to have simpler access to functions.

> User.where((User[:login] + User[:first_name]).length.in 2..10).to_sql
"SELECT `users`.* FROM `users` 
 WHERE LENGTH(CONCAT(CAST(`users`.`login` AS char),
                     CAST(`users`.`first_name` AS char)))
         BETWEEN (2) AND (10)"

How do I use functions like CONCAT(), etc. in ARel?

2 Answers2

Linked