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.
Arel 6 (Rails 4) or Arel 7+ (Rails 5).
t is an Arel::Table for table my_table
(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 > 42Other operators : <, >=, <=, =~
Currently in Arel:
(t[:nb] + 42).to_sql # => my_table.nb + 42But:
(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) + 42Other functions : ABS, RAND, ROUND, FLOOR, CEIL, FORMAT
(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, TRIM
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)(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')(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')To optimize queries, some classical functions are defined in databases missing any alternative native functions. Examples :
- FIND_IN_SET
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) | 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 + ROUND | ✔ | CEILING() | CEILING() | |
| FLOOR column.floor | ✔ | ✔ | CASE + ROUND | ✔ | ✔ | ✔ | |
| RAND Arel.rand | ✔ | ✔ | RANDOM() | dbms_random.value() | ✔ | ✔ | |
| ROUND column.round(precision = 0) | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| SUM / AVG / MIN / MAX + x column.sum + 42 | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
String functions | CONCAT column + "string" | ✔ | ✔ | || | ✔ | + | ✔ |
| LENGTH column.length | ✔ | ✔ | ✔ | ✔ | LEN() | ✔ | |
| LOCATE column.locate("string") | ✔ | ✔ | INSTR() | ✔ | CHARINDEX() | ✔ | |
| FIND_IN_SET column & ("l") | ✔ | ✔ | db.create_function( "find_in_set", 1 ) do |func, value1, value2|, func.result =value1.index(value2) end | ✔ | ✔ | ✔ | |
| 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() | |
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) | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ |