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 most 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

In the following examples t is an Arel::Table for table my_table (i.e., t = Arel::Table.new('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_]+'

The replace function supports string and regex patterns. For instance

t[:email].replace('@', ' at ').replace('.', ' dot ').to_sql
# => REPLACE(REPLACE(`my_table`.`email`, '@', ' at '), '.', ' dot ')

Captures are supported when using regex patterns. The replace string may then reference the capture groups using \1, \2, etc. For instance

t[:email].replace(/^(.*)@(.*)$/, 'user: \1, host: \2').to_sql
# => REGEXP_REPLACE(`my_table`.`email`, '(?-mix:^(.*)@(.*)$)', 'user: \\1, host: \\2')

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)

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 'UTC' 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:

• ⚠️ Time Zone names are specific to each RDBMS. While PostgreSQL and MySQL have overlaping names (the ones prefixed with posix), you should always read your vendor's documentation. SQL Server is a black sheep and has its own conventions.
• ⚠️ Daylight saving is managed by the RDBMS vendor. Choose the approptiate time zone name that enforces proper daylight saving conversions.
  • ☣️ Choosing GMT+offset will certainly bypass daylight saving computations.
  • ☣️ Choosing abbreviate forms like CET, which stands for Central European Time will behave differently on PostgreSQL and MySQL. Don't assume uniform behavior, or even a rational one.
• ⚠️ Pay attention to the type of the datetime column you're working with. For example, in Postgres, a datetime can be one of the following types:
  1. timestamp with time zone
  2. timestamp without time zone In the first case, you don't need to supply a conversion hash because postgres knows how to convert it to the desired time zone. However, if you do the same for the second case, you might get surprises, especially if your Postgres installation's default timezone is not UTC.
• ⚠️ SQLite is not supported.
• 🚨 Always test against your setup 🚨

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	✔	✔	✔
	POSIX FORMATTING column.format_number("$ %7.2f","en_US")	✔	✔	✔	✔	✔	not implemented
	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"	✔	✔	||	✔	+	✔
	FIND_IN_SET column & ("l")	✔	✔	Ruby function	✔	✔	✔
	ILIKE (in Arel6) column.imatches('%pattern')	LOWER() LIKE LOWER()	✔	✔	LOWER() LIKE LOWER()	LOWER() LIKE LOWER()	LOWER() LIKE LOWER()
	LENGTH column.length	✔	✔	✔	✔	LEN()	✔
	LOCATE column.locate("string")	✔	✔	INSTR() or Ruby function	✔	CHARINDEX()	✔
	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	✔	✔	?
	NOT_REGEXP column != "pattern"	✔	✔	require pcre.so	NOT REGEXP_LIKE	NOT LIKE	✔
	REGEXP column =~ "pattern"	✔	✔	require pcre.so	REGEXP_LIKE	LIKE	✔
	REPLACE column.replace("s","X")	✔	✔	✔	✔	✔	✔
	REPLACE column.replace(/re/,"X")	✔	✔	✔	✔	✔	✔
	SOUNDEX column.soundex	✔	require fuzzystrmatch	✔	✔	✔	✔
	SUBSTRING column[1..2] column.substring(1) column.substring(1, 1)	✔	SUBSTR()	SUBSTR()	SUBSTR()	✔	✔
	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	BLANK column.blank	✔	✔	✔	✔	✔	✔
	COALESCE column.coalesce(var)	✔	✔	✔	✔	✔	✔
	COALESCE_BLANK column.coalesce_blank(var)	✔	✔	✔	✔	✔	✔
	IF_PRESENT	✔	✔	✔	✔	✔	✔
	ISNULL column.isnull()	IFNULL()	✔	✔	NVC()	✔	✔
	NOT_BLANK column.not_blank	✔	✔	✔	✔	✔	✔
	PRESENT column.present alias to NOT_BLANK	✔	✔	✔	✔	✔	✔
	== 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