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

[: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)

Datetime

# datetime difference
t[:birthdate] - Time.utc(2014, 3, 3, 12, 41, 18)

# comparison
t[:birthdate] >= '2014-03-03 10:10:10'

Format and Time Zone Conversion

format has two forms:

t[:birthdate].format('%Y-%m-%d').to_sql
# => DATE_FORMAT(my_table.birthdate, '%Y-%m-%d')

Which formats the datetime without any time zone conversion. The second form accepts 2 kinds of values:

  1. String:

t[:birthdate].format('%Y/%m/%d %H:%M:%S', 'posix/Pacific/Tahiti')
# => DATE_FORMAT(CONVERT_TZ(CAST(my_table.birthdate AS datetime), 'UTC', 'posix/Pacific/Tahiti'), '%Y/%m/%d %H:%i:%S')          ## MySQL
# => TO_CHAR(CAST(my_table.birthdate AS timestamp with time zone) AT TIME ZONE 'posix/Pacific/Tahiti', 'YYYY/MM/DD HH24:MI:SS') ## PostgreSQL
# => CONVERT(datetime, my_table.birthdate) AT TIME ZONE 'UTC' AT TIME ZONE N'posix/Pacific/Tahiti'                              ## SQL Server (& truncated for clarity)
#                                                                            ^^^^^^^^^^^^^^^^^^^^ 🚨 Invalid timezone for SQL Server. Explanation below.

which will convert the datetime field to the supplied time zone. This generally means that you’re letting the RDBMS decide or infer what is the timezone of the column before conversion to the supplied timezone.

  1. Hash of the form { src_time_zone => dst_time_zone }:

t[:birthdate].format('%Y/%m/%d %H:%M:%S', { 'posix/Europe/Paris' => 'posix/Pacific/Tahiti' })

which will explicitly indicate the original timestamp that should be considered by the RDBMS.

Warning:

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)
βœ” βœ” βœ” βœ” βœ” βœ”

Version Compatibility

Ruby Rails Arel Extensions
3.1 6.1 2
3.0 6.1 2
2.7 6.1, 6.0 2
2.5 6.1, 6.0 2
2.5 5.2 1

Development

Let’s say you want to develop/test for ruby 2.7.5 and rails 5.2.

You will need to fix your ruby version:

rbenv install 2.7.5
rbenv local 2.7.5

Fix your gemfiles:

bundle config set --local gemfile ./gemfiles/rails6.gemfile

Install dependencies:

bundle install

Develop, then test:

bundle exec rake test:to_sql

Refer to the Version Compatibility section to correctly set your gemfile.