Skip to content


Subversion checkout URL

You can clone with
Download ZIP
Operators |, and, - for AREL.
Branch: master
Fetching latest commit...
Cannot retrieve the latest commit at this time.
Failed to load latest commit information.


= AR Operators =

Don't let me wrong, it's not as I hate SQL. I just hate to create complex queries when ActiveRecord
could do this for me, for free.

For example, imagine you're finding people. You want everybody named "John" OR "Smith", 
BUT don't want anyone who is underaged. So, in ActiveRecord, you could do something like:

Person.all :conditions => [
    '(name LIKE ? OR name LIKE ?) AND age >= 18', '%John%', '%Smith%'

But this is tedious. One of the great advantages of ActiveRecord 3 is use of scopes, so you could:

Person.where(['(name LIKE ? OR name LIKE ?)', '%John%', '%Smith%']).where('age >= 18')

Better. But why not:

johns = Person.where(['name like ?', '%John%'])
smiths = Person.where(['name like ?', '%Smith%'])
underaged = Person.where('age < 18')
(johns | smiths) - underaged

== ENTER AR Operators ==

This library brings operators to ActiveRecord 3. So, all you have to do is:
require 'ar_operators'
class Person < ActiveRecord::Base
  extend ArelOperators

And you're ready to go. Right now, the following operators are implemented:
.or, |, + (OR)
.and (AND)
- (AND (NOT ...))
-@ (negates the query. Use like: -Person.where(:name => 'foo'), to find all people where name is not 'foo')

I cannot use & (amperstand operator) for AND, because this behaviour is already defined for ActiveRecord::Relation.

There is also the following constructions:
p1 = Person.where :name => 'Foo'
p2 = Person.where :age => 18
p1.where(p2) #Generates something like: SELECT * FROM people WHERE ((name = 'Foo') AND (id in SELECT id FROM people WHERE age = 18))

== Design Decision: ==
To not monkey patch ActiveRecord::Relation, I decided to include these operators only when you
use "where" or "scoped" to find objects. If you decide that ALL ActiveRecord operations should
have this kind of behaviour, you can:

class ActiveRecord::Relation
  include ArelOperators::Operators

== Finders ==
Ok, so we don't have to write tedious SQL operations by hand. What about trying a little further,
and not write ANY SQL at all? After all, we have blocks in Ruby, so what about:

Person.where { |p| >= 10 }

ArelOperators, right now, permits this kind of behaviour only for where clauses (no "having" support
right now). The following operations are supported:
== (Test for equality)
!= (Test for inequality, Ruby 1.9 only)
>, >= (Test for Greater or Greather or Equal)
<, <= (Test for Lower, or Lower or equal)
in? (SQL IN clause, "WHERE name IN(1, 2, 3)" )
like?, matches?, =~ (SQL LIKE clause, "WHERE name LIKE 'something'")

So, as an example:

Person.where { |p|"F%") }
Person.where { |p| =~ "F%" } #Same as above
Person.where { |p|[10, 20, 30]) } #SQL IN operator
Person.where { |p| } #SQL BETWEEN operator

Furthermore, you can OR or AND your clauses with | and &. Ruby 1.9 "!" and  is also supported, and for
Ruby 1.8, you can use the negative ("-@") operator. For example:

Person.where { |p| ( != "Fred") | (p.age > 17) } #Ruby 1.9 version
Person.where { |p| !( == "Fred") | (p.age > 17) } #Another Ruby 1.9 version
Person.where { |p| -( == "Fred") | (p.age > 17) } #All ruby versions

The block argument is optional, too, so:

Person.where { name == "Foo" }
Person.where { "F%" }
Person.where { (age <= 10) & (name =~ "F%) }

Please notice, that as & and | operators have a high precedence, don't forget the parenthesis,
otherwise undesired behaviours can occur.

== NOTICE ==
Regexp operations are NOT supported. Right now, I'm only delegating these operators
to Arel, and Arel still has no support for regexp.

== Known issues: ==
As ActiveRecord::Relation doesn't only include "where" clauses, there can be a strange behaviour if trying
to combinate more behaviours. For instance, this kind of query:
p1 = Person.where(:name => 'Foo').limit(10)
p2 = Person.where(:age => 18).order('name').limit(20)
(p1 | p2).to_sql

Will generate:
SELECT "people".* FROM "people"  WHERE ((("people"."name" = 'Foo') OR ("people"."age" = 18))) LIMIT 10

So, if you don't want to fall in undefined behaviours, please use:
(p1 | p2).limit(20).order('name')

Furthermore, beware of "OR" operator. Don't try this at home:
p1 = Person.where(:name => "Foo")
a1 = Address.where(:road => "Something")
(p1 | a1) #This will generate something like: 
SELECT     "people".* FROM       "people"  WHERE     ((("people"."name" = 'Foo') OR ("addresses"."road" = 'Something')))
So, as with & operator, you must join relations so your query would be correct.
Something went wrong with that request. Please try again.