Working With Rails

Criteriaquery

=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 max_at_muermann_dot_org

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

The rubyforge project page can be found at http://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:

 Equals    -           
[+] 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>< c 3

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

AND, 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’, 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. A slightly more verbose, but structurally nicer way of specifying subexpressions is through the use of blocks.

Block Notation

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

NOTE: This description has been extracted from the Plugin README and so the formatting may need updating to make browser friendly