=CriteriaQuery

CriteriaQuery is an extension to the ActiveRecord find mechanism. It allows object-oriented construction of queries.

For bug reports, questions and suggestions contact ruby_at_muermann_dot_org

=Installation

 .script/plugin install criteria_query

== From SVN

Unix:
 .script/plugin install http://3columns.net/rubyplayground/projects/criteria_query/trunk/criteria_query

Windows:
 ruby script\plugin install http://3columns.net/rubyplayground/projects/criteria_query/trunk/criteria_query

=Why is this useful?
For simple queries, ActiveRecord's sql-based finder mechanism is fine. If all you are using in your application are statements like Person.find(1) or Person.find_by_name('some name'), you won't need CriteriaQuery.

If you frequently face the problem of constructing complex dynamic queries, you will get some benefit out of this.

Consider the following search form (taken from a real application):

 Name (first or last): [               ]
 Customer Category:    [ Please Select ][^]
 Last call between:    [         ] and [         ]
 Active Contacts only: [x]
 Address
 State:                [               ]
 City:                 [               ]          
 Street Addres:        [               ]      

Or, even worse, dynamically constructed filters with a variable number of search criteria:

 [ First name ][^] [ Equals   ] [          ] [-]
 [ Last name  ][^] [ Contains ] [          ] [-]
 [+] Add condition
 [Save Filter]

The code that generates the appropriate sql clauses for these kinds of search operation is often ugly and normally prone to fencepost errors. For the first example:

  conditions = ""
  par = []
  includes = []
  
  if params[:name]
    conditions << "(firstName=? OR lastName=?)"
    par << params[:name]
    par << params[:name]
  end
  
  if params[:category]
    conditions << " AND " unless conditions.empty?
    conditions << "category_id=?"
    par << params[:category]
  end  
  
  ...
  
  if params[:address]
    includes << ["address"]
    if params[:address[:state]] 
      conditions << " AND " unless conditions.empty?
      conditions << "addresses.state=?"
    par << params[:address[:state]]
    end
  end
  ...
  
CriteriaQuery allows you to write the above as:

  pq = Person.query
  pq.disjunction.first_name_eq(params[:name]).last_name_eq(params[:name]) if params[:name]
  pq.category_id_eq(params[:category]) if params[:category]
  ...
  address = pq.join("address")
  address.state_eq(params[:address[:state]]) if params[:address[:state]]
  ...
  end
  
==Queries

A Query represents a tree of query conditions and allows execution of the query via the ActiveRecord find method:

 Person.query.find

will execute Person.find(:all).

You can pass parameters to the Query.find method similar to ActiveRecord.find:

 Person.query.find(:limit=>10, :offset:20).
 
Note that if you pass :conditions and :include parameters, those will be overwritten by those generated by Query.

==Restrictions

A query is hardly useful unless there are some conditions restricting the results. You can add restrictions to a query by using magic meta methods:

 Person.query.name_eq('some name').find

will execute
 Person.find(:all, :conditions=>['name=?'], 'some_name')

The restriction methods normally return their parent restriction, so they can be chained. Restrictions are normally combined with AND:
 Person.query.first_name_eq('firstname').last_name_eq('lastname').find

will execute
 Person.find(:all, c:conditions=>['first_name=? AND last_name=?'], 'firstname', 'lastname')
 
If you really want to, you can also construct and add the restriction objects manually:

 pq = Person.query
 pq << Eq.new('name', 'somename')
 pq << Gt.new('created_on', 'somedate')
 pq.find
 
All restrictions are included on each CriteriaContainer object:

 pq = Person.query
 pq.eq(name,value)
 pq.gt('created_on', 'somedate')
 pq.find
 
As both of these variations swap the verb and the subject, using the magic meta methods is recommended. It also required less typing.

===Available Restrictions

Equals:: attribute_eq(value)
Not equal:: attribute_ne(value)
Like:: attribute_like(value)
Greater than:: attribute_gt(value)
Greater than or equal:: attribute_gte(value)
Less than:: attribute_lt(value)
Less than or equal:: attribute_lte(value)
Is null:: attribute_is_null
Not null:: attribute_is_not_null
In:: attribute_in([value, value, value,...])

==Subrestrictions

There are some restrictions which can contain other restrictions.

=== Disjunction (OR)
A disjunction is an "OR" clause in sql. All child restrictions of a disjunction are combined with OR:
 
 Person.query.disjunction.first_name_eq('name').last_name_eq('name')
 
will execute
 Person.find(:all, :conditions=>['first_name=? OR last_name=?', 'name', 'name'])
 
The disjunction method is aliased to "or":
 Person.query.or.first_name_eq('name').last_name_eq('name')

=== Conjunction (AND)
A conjunction is an "AND" clause in sql. This restriction only needs to be used in cases where nested AND clauses are required within an OR clause (disjunction):

 pq = Person.disjunction
 pq.conjunction.date_lt('some date').date_gt('some other date')
 pq.active_eq(1)
 
will execute:
  Person.find(:all, :conditions=>['(date>? AND date<?) OR active=?','some date', 'some other date', 1])
  
The conjunction method is aliased to "and":
 pq.and.date_lt('some date').date_gt('some other date')

=== Negation
The negation restriction will wrap its children in a NOT:

 Person.query.not.name_eq('name').active_eq(0)

will execute
 Person.find(:all, :conditions=>['NOT( name=? AND active=?)', 'name', 0 ])
 
==Joins
You can use joins in your queries:
 
 Person.query.name_like('name').join('address').city_like('city')
 
will execute:
 Person.find(:all, :conditions=>['people.name LIKE ? AND addresses.city LIKE ?', 'name', 'city'], :include=>[:city])
 
Joins across multiple relationship levels are also supported:

 Person.query.name_like('name').join('address').city_like('city').join('state').name_eq('state')

will execute:
 Person.find(:all, :conditions=>['people.name LIKE ? AND addresses.city LIKE ? AND states.name=?', 'name', 'city', 'state'], :include=>[:city=>[:state]])

CriteriaQueries support joining the same table multiple times. Consider the following models:

 class Person < ActiveRecord::Base
   belongs_to :city
   belongs_to :works_in, :class_name=>'City', :foreign_key=>'works_in_id'
 end

 class City < ActiveRecord::Base
   belongs_to :state
   has_many :people
 end

 class State  < ActiveRecord::Base
   has_many :cities
 end

This will find all people who live in Sydney and work in Melbourne:

 pq = Person.query
 pq.join('city').name_eq('Sydney')
 pq.join('works_in').name_eq('Melbourne')
 pq.find

The following (contrived) example finds all people who live in the state that Sydney belongs to:

 pq = Person.query
 pq.join('city').join('state').join('cities').name_eq('Sydney')
 pq.find
 
== Block Notation
A slightly more verbose, but structurally nicer way of specifying subrestrictions is through the use of blocks.

The following

 pq = Person.query
 pq.name_like('name')
 address = pq.join('address')
 address.disjunction.street_1_like('street).street_2_like('street)
 
Can be written as:

 pq = Person.query
 pq.name_like('name')
 pq.join('address') do |address|
   address.disjunction do |streets|
     streets.street_1_like('street).street_2_like('street)
   end
 end
 
== Saving Restrictions
Because a Query is just a tree structure of Ruby objects, it can be marshalled and unmarshalled.

 pq = Person.query.name_like('name')
 marshalled = Marshal.dump(pq)
 restored = Marshal.restore(marshalled)
 restored.find