Arel Extensions

Adds support for missing SQL operators and functions to Arel for ActiveRecord.

Installation

Add this line to your application's Gemfile:

gem 'arel-extensions', require: 'arel/extensions'

And then execute:

$ bundle

Or install it yourself as:

$ gem install arel-extensions

Usage

Just require 'arel-extensions' and use Arel/ActiveRecord as you normally would! arel-extensions extends ActiveRecord's query methods in both Arel and ActiveRecord.

Querying PostgreSQL datatypes

## Arrays

@> - Array Contains operator
<@ - Array Contained By operator
&& - Array Overlap operator

### && - Array Overlap operator

PostgreSQL implements the && operator, known as the overlap operator, for arrays. The overlap operator returns t (true) when two arrays have one or more elements in common.

 ARRAY[1,2,3] && ARRAY[4,5,6]
 -- f

 ARRAY[1,2,3] && ARRAY[3,5,6]
 -- t

Postgres_ext extends the ActiveRecord::Relation.where method similar to the Rails 4.0 not clause. The easiest way to make a overlap query would be:

 User.where.overlap(:nick_names => ['Bob', 'Fred'])

Postgres_ext defines overlap, an Arel predicate for the && operator. This is utilized by the where.overlap call above.

 user_arel = User.arel_table

 # Execute the query
 User.where(user_arel[:tags].overlap(['one','two']))
 # => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"tags\" && '{one,two}'

### @> - Array Contains operator

PostgreSQL has a contains (@>) operator for querying whether all the elements of an array are within another.

 ARRAY[1,2,3] @> ARRAY[3,4]
 -- f

 ARRAY[1,2,3] @> ARRAY[2,3]
 -- t

Postgres_ext extends the ActiveRecord::Relation.where method by adding a contains method. To make a contains query, you can do:

 User.where.contains(:nick_names => ['Bob', 'Fred'])

Postgres_ext overrides contains, an Arel predicate, to use the @> operator for arrays. This is utilized by the where.contains call above.

 user_arel = User.arel_table

 # Execute the query
 User.where(user_arel[:tags].contains(['one','two']))
 # => SELECT "users".* FROM "users" WHERE "users"."tags" @> '{"one","two"}'

### ANY or ALL functions

When querying array columns, you have the ability to see if a predicate apply's to either any element in the array, or all elements of the array. The syntax for these predicates are slightly different then the normal where syntax in PostgreSQL. To see if an array contains the string 'test' in any location, you would write the following in SQL

 SELECT *
 FROM users
 WHERE 'test' = ANY(users.tags)

Notice that the column is on the right hand side of the predicate, instead of the left, because we have to call the ANY function on that column.

Postgres_ext provides a ActiveRecord::Relation.where.any() method. The easiest way to make a ANY query would be:

 User.where.any(:nick_names => 'Bob')

There is also an ActiveRecord::Relation.where.all() call as well. This method utilizes the following code to create the query:

We can generate the above query using Arel and generating the Node manually. We would use the following to accompish this:

 user_arel = User.arel_table

 # Execute the query
 User.where(user_arel[:tags].any('test'))
 #=> SELECT \"users\".* FROM \"users\" WHERE 'test' = ANY(\"users\".\"tags\")

The ALL version of this same predicate can be generated by swapping #any() for #all().

## INET/CIDR Queries

PostgreSQL defines the <<, or contained within operator for INET and CIDR datatypes. The << operator returns t (true) if a INET or CIDR address is contained within the given subnet.

 inet '192.168.1.6' << inet '10.0.0.0/24'
 -- f

 inet '192.168.1.6' << inet '192.168.1.0/24'
 -- t

In addition to contained within, there is also:

<<= - Contained within or equals
>> - Contains
>>= - Contains or equals

Postgres_ext extends the ActiveRecord::Relation.where method similar to the Rails 4.0 not clause. The easiest way to make a overlap query would be:

 User.where.contained_within(:ip => '192.168.1.1/24')
 User.where.contained_within_or_equals(:ip => '192.168.1.1/24')
 User.where.contains(:ip => '192.168.1.14')
 User.where.contains_or_equals(:ip => '192.168.1.14')

Postgres_ext defines contained_within, an Arel predicate for the << operator. This is utilized by the methods above.

 user_arel = User.arel_table

 # Execute the query
 User.where(user_arel[:ip_address].contained_within('127.0.0.1/24'))
 # => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" << '127.0.0.1/24'
 User.where(user_arel[:ip_address].contained_within_or_equals('127.0.0.1/24'))
 # => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" <<= '127.0.0.1/24'
 User.where(user_arel[:ip_address].contains('127.0.0.1'))
 # => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" >> '127.0.0.1'
 User.where(user_arel[:ip_address].contains_or_equals('127.0.0.1'))
 # => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" >>= '127.0.0.1'

Developing

To work on postgres_ext locally, follow these steps:

Run bundle install, this will install (almost) all the development dependencies
Run gem install byebug (not a declared dependency to not break CI)
Run bundle exec rake db:setup, this will set up the .env file necessary to run the tests and set up the database
Run bundle exec rake db:create, this will create the test database
Run bundle exec rake db:migrate, this will set up the database tables required by the test
Run BUNDLE_GEMFILE='gemfiles/Gemfile.activerecord-4.0.x' bundle install --quiet to create the Gemfile.lock for 4.0.
Run BUNDLE_GEMFILE='gemfiles/Gemfile.activerecord-4.1.x' bundle install --quiet to create the Gemfile.lock for 4.1.
Run bundle exec rake test:all to run tests against all supported versions of Active Record

Authors

Dan McClain twitter github

Change.arel_table[:diff].has_key?(:in_review) Change.arel_table[:diff].has_keys?(:in_review, :cached_at) Change.arel_table[:diff].has_any_key?(:in_review, :nokey)