Arel Extensions
Gem: 
Arel Extensions adds shortcuts, fixes and new ORM mappings (ruby to SQL) to Arel. It aims to ensure pure ruby syntax for the biggest number of usual cases. It allows to use more advanced SQL functions for any supported RDBMS.
Requirements
Arel 6 (Rails 4) or Arel 7+ (Rails 5). Arel Repository
or
Rails 6 Rails Repository
Usage
Most of the features will work just by adding the gem to your Gemfiles. To make sure to get all the features for any dbms, you should execute the next line as soon as you get your connection to your DB:
ArelExtensions::CommonSqlFunctions.new(ActiveRecord::Base.connection).add_sql_functions()
It will add common SQL features in your DB to align ti with current routines. Technically, it will execute SQL scripts from init folder.
Examples
t is an Arel::Table for table my_table
Comparators
(t[:date1] > t[:date2]).to_sql # (same as (t[:date1].gt(t[:date2])).to_sql)
# => my_table.date1 > my_table.date2
(t[:nb] > 42).to_sql # (same as (t[:nb].gt(42)).to_sql)
# => my_table.nb > 42
Other operators : <, >=, <=, =~
Maths
Currently in Arel:
(t[:nb] + 42).to_sql
# => my_table.nb + 42
But:
(t[:nb].sum + 42).to_sql
# => NoMethodError: undefined method `+' for #<Arel::Nodes::Sum>
With Arel Extensions:
(t[:nb].sum + 42).to_sql
# => SUM(my_table.nb) + 42
Other functions : ABS, RAND, ROUND, FLOOR, CEIL, FORMAT
For Example:
t[:price].format_number("%07.2f €","fr_FR")
# equivalent to 'sprintf("%07.2f €",price)' plus locale management
String operations
(t[:name] + ' append').to_sql
# => CONCAT(my_table.name, ' append')
(t[:name].coalesce('default')).to_sql
# => COALESCE(my_table.name, 'default')
(t[:name].blank).to_sql
# => TRIM(TRIM(TRIM(COALESCE(my_table.name, '')), '\t'), '\n') = ''
(t[:name] =~ /\A[a-d_]+/).to_sql
# => my_table.name REGEXP '\^[a-d_]+'
Other functions : SOUNDEX, LENGTH, REPLACE, LOCATE, SUBSTRING, TRIM
String Array operations
t[:list] is a classical varchar containing a comma separated list ("1,2,3,4")
(t[:list] & 3).to_sql
# => FIND_IN_SET('3', my_table.list)
(t[:list] & [2,3]).to_sql
# => FIND_IN_SET('2', my_table.list) OR FIND_IN_SET('3', my_table.list)
Date & Time operations
(t[:birthdate] + 10.years).to_sql
# => ADDDATE(my_table.birthdate, INTERVAL 10 YEAR)
((t[:birthdate] - Date.today) * -1).to_sql
# => DATEDIFF(my_table.birthdate, '2017-01-01') * -1
t[:birthdate].week.to_sql
# => WEEK(my_table.birthdate)
t[:birthdate].month.to_sql
# => MONTH(my_table.birthdate)
t[:birthdate].year.to_sql
# => YEAR(my_table.birthdate)
t[:birthdate].format('%Y-%m-%d').to_sql
# => DATE_FORMAT(my_table.birthdate, '%Y-%m-%d')
Unions
(t.where(t[:name].eq('str')) + t.where(t[:name].eq('test'))).to_sql
# => (SELECT * FROM my_table WHERE name='str') UNION (SELECT * FROM my_table WHERE name='test')
Case clause
Arel-extensions allows to use functions on case clause
t[:name].when("smith").then(1).when("doe").then(2).else(0).sum.to_sql
# => SUM(CASE "my_table"."name" WHEN 'smith' THEN 1 WHEN 'doe' THEN 2 ELSE 0 END)
Cast Function
Arel-extensions allows to cast type on constants and attributes
t[:id].cast('char').to_sql
# => CAST("my_table"."id" AS char)
Stored Procedures and User-defined functions
To optimize queries, some classical functions are defined in databases missing any alternative native functions. Examples :
- FIND_IN_SET
BULK INSERT / UPSERT
Arel Extensions improves InsertManager by adding bulk_insert method, which allows to insert multiple rows in one insert.
@cols = ['id', 'name', 'comments', 'created_at']
@data = [
[23, 'name1', "sdfdsfdsfsdf", '2016-01-01'],
[25, 'name2', "sdfds234sfsdf", '2016-01-01']
]
insert_manager = Arel::InsertManager.new(User).into(User.arel_table)
insert_manager.bulk_insert(@cols, @data)
User.connection.execute(insert_manager.to_sql)
New Arel Functions
| Function / Example ToSql |
MySQL / MariaDB | PostgreSQL | SQLite | Oracle | MS SQL | DB2 (not tested on real DB) |
|
|---|---|---|---|---|---|---|---|
Number functions |
ABS column.abs |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ |
| CEIL column.ceil |
✔ | ✔ | CASE + CAST | ✔ | CEILING() | CEILING() | |
| FLOOR column.floor |
✔ | ✔ | CASE + CAST | ✔ | ✔ | ✔ | |
| RAND Arel.rand |
✔ | ✔ | RANDOM() | dbms_random.value() | ✔ | ✔ | |
| ROUND column.round(precision = 0) |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| SUM / AVG / MIN / MAX + x column.sum + 42 |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| POSIX FORMATTING column.format_number("$ %7.2f","en_US") |
✔ | ✔ | ✔ | ✔ | ✔ | not implemented | |
String functions |
CONCAT column + "string" |
✔ | ✔ | || | ✔ | + | ✔ |
| LENGTH column.length |
✔ | ✔ | ✔ | ✔ | LEN() | ✔ | |
| LOCATE column.locate("string") |
✔ | ✔ | INSTR() or Ruby function | ✔ | CHARINDEX() | ✔ | |
| SUBSTRING column[1..2] column.substring(1) column.substring(1, 1) |
✔ | SUBSTR() | SUBSTR() | SUBSTR() | ✔ | ✔ | |
| FIND_IN_SET column & ("l") |
✔ | ✔ | Ruby function | ✔ | ✔ | ✔ | |
| SOUNDEX column.soundex |
✔ | require fuzzystrmatch | ✔ | ✔ | ✔ | ✔ | |
| REPLACE column.replace("s","X") |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| REGEXP column =~ "pattern" |
✔ | ✔ | require pcre.so | REGEXP_LIKE | LIKE | ✔ | |
| NOT_REGEXP column != "pattern" |
✔ | ✔ |
require pcre.so | NOT REGEXP_LIKE | NOT LIKE | ✔ | |
| ILIKE (in Arel6) column.imatches('%pattern') |
LOWER() LIKE LOWER() | ✔ | ✔ | LOWER() LIKE LOWER() | LOWER() LIKE LOWER() | LOWER() LIKE LOWER() | |
| TRIM (leading) column.trim("LEADING","M") |
✔ | LTRIM() | LTRIM() | ✔ | ✔ | LTRIM() | |
| TRIM (trailing) column.trim("TRAILING","g") |
✔ | RTRIM() | RTRIM() | ✔ | ✔ | Rtrim() | |
| TRIM (both) column.trim("BOTH","e") |
✔ | TRIM() |
TRIM() | ✔ | LTRIM(RTRIM()) | TRIM() | |
| Matching Accent/Case Insensitive column.ai_imatches('blah') |
✔ | unaccent required | not supported | ✔ | ✔ | ? | |
| Matching Accent Insensitive column.ai_matches('blah') |
not supported | not supported | not supported | not supported | ✔ | ? | |
| Matching Case Insensitive column.imatches('blah') |
not supported | ✔ | ✔ | ✔ | ✔ | ? | |
| Matching Accent/Case Sensitive column.smatches('blah') |
✔ | ✔ | not supported | ✔ | ✔ | ? | |
Date functions |
DATEADD column + 2.year |
DATE_ADD() |
✔ | ✔ | ✔ | ✔ | + |
| DATEDIFF column - date |
DATEDIFF() |
✔ | JULIANDAY() - JULIANDAY() | - | ✔ | DAY() | |
| DAY column.day |
✔ | ✔ | STRFTIME() | ✔ | ✔ | ✔ | |
| MONTH column.month |
✔ | ✔ | STRFTIME() | ✔ | ✔ | ✔ | |
| WEEK column.week |
✔ | ✔ | STRFTIME() | ✔ | ✔ | ✔ | |
| YEAR column.year |
✔ | ✔ | STRFTIME() | ✔ | ✔ | ✔ | |
Comparators functions |
COALESCE column.coalesce(var) |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ |
| ISNULL column.isnull() |
IFNULL() | ✔ | ✔ | NVC() | ✔ | ✔ | |
| == column == integer |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| != column != integer |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| > column > integer |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| >= column >= integer |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| < column < integer |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| <= column <= integer |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
Boolean functions |
OR ( ⋁ ) column.eq(var).⋁(column.eq(var)) |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ |
| AND ( ⋀ ) column.eq(var).⋀(column.eq(var)) |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
Bulk Insert |
insert_manager.bulk_insert(@cols, @data) | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ |
Set Operators |
UNION (+) query + query |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ |
Set Operators |
UNION ALL query.union_all(query) |
✔ | ✔ | ✔ | ✔ | ✔ | ✔ |