Arel Extensions

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

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

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 (in next version)

(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')

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 + 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() 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()
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))	✔	✔	✔	✔	✔	✔
Boolean functions	AND ( ⋀ ) column.eq(var).⋀(column.eq(var))	✔	✔	✔	✔	✔	✔
Bulk Insert	insert_manager.bulk_insert(@cols, @data)	✔	✔	✔	✔	✔	✔