CriteriaQuery

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

For bug reports, questions and suggestions please contact ruby_at_muermann_dot_org

The CriteriaQuery homepage is at www.muermann.org/ruby/criteria_query

The rubyforge project page can be found at rubyforge.org/projects/criteriaquery/

Installation

 script/plugin install criteria_query

From SVN

Unix:

 .script/plugin install svn://rubyforge.org/var/svn/criteriaquery

Windows:

 ruby script\plugin install svn://rubyforge.org/var/svn/criteriaquery

Why is this useful?

For simple queries, ActiveRecord‘s sql-based finder mechanism is great. 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, as you have to write special case code when concatenating optional OR or AND statements. 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

Query Expression Trees

When a sql statement is executed by a database server, the server will first parse the statement into a tree structure. Each node in the tree represents a SQL expression (AND, OR, +, =, LIKE, IN, etc..), and each leaf represents a field or a constant. The following where clause:

WHERE (a=1 AND (b=2 OR c<3))

yields a parse tree like this:

If you think of the operands (=,<) and expressions like OR, AND as functions, the execution of the query looks like this:

AND( =(a,b), OR(=(b,2), <(c,3)) )

In the criteria_query plugin, compound expressions like AND, OR are specified in a similar prefixed manner:

pq = Person.query pq.or << pq.b_eq(2) << pq.c_lt(3)

Queries

A Query represents a tree of query conditions and joins 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, they will be overwritten by those generated by Query.

Bring your own model

If you do not wish to use the find() method available on a query object, the conditions and parameters generated by the query can be used separately:

 pq = Person.query
 pq.name_like '%name%'

 pq.conditions => '(people.name LIKE ?)'
 pq.parameters => ['%name%']
 pq.find_options => ['(people.name LIKE ?)', '%name%']

You can pass the output of find_options directly to any ActiveRecord finder method.

Expressions

A query is hardly useful unless there are some conditions restricting the results. You can add restricting expressions 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 expression methods normally return their parent restriction, so they can be chained. Expressions are combined with AND by default:

 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 expression objects manually:

 pq = Person.query
 pq << Eq.new('name', 'somename')
 pq << Gt.new('created_on', 'somedate')
 pq.find

All expressions are included on each object which includes the Filterable module - which are all elements that can be added to the query tree:

 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 (equals name ‘some_name’) instead of the more natural way (name equals ‘some_name’), using the magic meta methods is recommended. It also requires less typing.

Available Expressions

Equals:	attribute_eq(value)
Not equal:	attribute_ne(value)
Like:	attribute_like(value)
Case insensitive Like (Ilike):	attribute_ilike(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 expressions.

Disjunction (OR)

A disjunction is an "OR" clause in sql. All child expressions 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 expression 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 expression 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]])

If a join is defined for a query, a method is created on the parent expression. The method name defaults to the name of the association, but can be overidden by passing an :as option to join():

 pq = Person.query
 pq.join :address
 pq.address.street_eq('some_street')

And

 pq = Person.query
 pq.join :address, :as=>:lives_in
 pq.lives_in.street_eq('some_street')

Via the magic method_missing method, all TableAlias objects (joins and queries) will attempt to create a join when an undefined method is called:

 pq = Person.query
 pq.city.name_eq('some_name')

Note that the object returned by

 Person.query.city.name_eq

is the join on city, NOT the query object! This is necessary to enable chaining of expression methods.

Compound Expression across multiple tables

If you need to construct a query that combines expressions on different joins in the same AND or OR clause, you can use the following:

 pq = Person.query
 pq.join(:lives_in)
 pq.join(:works_in)
 pq.or << pq.lives_in.street_eq('some_street') << pq.works_in.street_eq('some_street')

Will execute:

 Person.find(:all, :conditions=>['(adresses.street=? OR people_addresses=?', 'some_street', 'some_street'], :include=>['lives_in','works_in'])

Multiple joins on the same table

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

Polymorphic joins

Polymorphic joins are supported on the "has_one" or "has_many" side of the association. For the following models

 class Address < ActiveRecord::Base
   belongs_to :addressable, :polymorphic=>true
 end

 class Company < ActiveRecord::Base
   has_many :addresses, :as=>:addressable
 end

this query will work:

 Company.query.join(:addresses).street_eq('some_street')

This, however, will not:

 Address.query.join(:addressable).name_eq('some_name')

This is not a limitation of CriteriaQuery, but a fundamental design decision of ActiveRecord. It is also impossible to execute this query via a sql-based finder method.

Block Notation

A slightly more verbose, but structurally nicer way of specifying subexpressions is through the use of blocks.

The following

 pq = Person.query
 pq.name_like('name')
 address = pq.join('address')
 address.or.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.or do |streets|
     streets.street_1_like('street).street_2_like('street)
   end
 end

Saving Queries

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

README

CriteriaQuery

Installation

From SVN

Why is this useful?

Query Expression Trees

Queries

Bring your own model

Expressions

Available Expressions

Subrestrictions

Disjunction (OR)

Conjunction (AND)

Negation

Joins

Compound Expression across multiple tables

Multiple joins on the same table

Polymorphic joins

Block Notation

Saving Queries