# arel-extensions **Repository Path**: mirrors_akimd/arel-extensions ## Basic Information - **Project Name**: arel-extensions - **Description**: Extending Arel - **Primary Language**: Unknown - **License**: MIT - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2020-09-24 - **Last Updated**: 2026-05-23 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # Arel Extensions [![Travis Build Status](https://img.shields.io/travis/Faveod/arel-extensions.svg?label=Travis%20build)](http://travis-ci.org/Faveod/arel-extensions) [![AppVeyor Build Status](https://img.shields.io/appveyor/ci/yazfav/arel-extensions.svg?label=AppVeyor%20build)](https://ci.appveyor.com/project/yazfav/arel-extensions) [![Security](https://hakiri.io/github/Faveod/arel-extensions/master.svg)](https://hakiri.io/github/Faveod/arel-extensions/master) ![](http://img.shields.io/badge/license-MIT-brightgreen.svg) Gem: [![Latest Release](https://img.shields.io/gem/v/arel_extensions.svg)](https://rubygems.org/gems/arel_extensions) [![Gem](https://ruby-gem-downloads-badge.herokuapp.com/arel_extensions?type=total)](https://rubygems.org/gems/arel_extensions) [![Gem](https://ruby-gem-downloads-badge.herokuapp.com/arel_extensions?label=downloads-current-version)](https://rubygems.org/gems/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](http://github.com/rails/arel) or Rails 6 [Rails Repository](http://github.com/rails/rails) ## 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: ```ruby 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 ```ruby (t[:date1] > t[:date2]).to_sql # (same as (t[:date1].gt(t[:date2])).to_sql) # => my_table.date1 > my_table.date2 ``` ```ruby (t[:nb] > 42).to_sql # (same as (t[:nb].gt(42)).to_sql) # => my_table.nb > 42 ``` Other operators : <, >=, <=, =~ ## Maths Currently in Arel: ```ruby (t[:nb] + 42).to_sql # => my_table.nb + 42 ``` But: ```ruby (t[:nb].sum + 42).to_sql # => NoMethodError: undefined method `+' for # ``` With Arel Extensions: ```ruby (t[:nb].sum + 42).to_sql # => SUM(my_table.nb) + 42 ``` Other functions : ABS, RAND, ROUND, FLOOR, CEIL, FORMAT For Example: ```ruby t[:price].format_number("%07.2f €","fr_FR") # equivalent to 'sprintf("%07.2f €",price)' plus locale management ``` ## String operations ```ruby (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") ```ruby (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 ```ruby (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 ```ruby (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 ```ruby 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 ```ruby 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)		 ✔ ✔ ✔ ✔ ✔ ✔