Better Finders 27 Oct 2006
The finders component of ActiveRecord Extensions extends the functionality of the find method on ActiveRecord::Base. It includes support for Ruby Strings, Numerics, Arrays, Ranges and Regexps.

Current find Behavior

class Person < ActiveRecord::Base ; end
  1. finds the Person with an id of 1

    Person.find( 1 )

  2. finds the Person with the first name of John

    Person.find_by_first_name( ‘John’ )

  3. finds the Person with the first name of John and last name of Doe

    Person.find_by_first_name_and_last_name( ‘John’, ‘Doe’ )

The problem with this is that the built-in automagical find method will only match an equal value in the database, and if you rely on a typical conditions array you end up with partial SQL and part ruby values. This only gets uglier with the more fields you want to include in your search. This is where the better finder support for ARE comes in.

Current find options

The find method itself supports options. In fact it takes a lot of options. At the time of this writing it took:

  • :conditions
  • :order
  • :group
  • :limit
  • :offset
  • :select
  • :joins
  • :include
  • :readonly

For more information please refer to the Rails API.

Current Problem With ActiveRecord :conditions

The option that helps you achieve a search for all people whose first name has the root ‘John’ we use the :conditions option.

people = Person.find :all, :conditions => [ 'first_name LIKE 'John%' ]  

That works, but we had to hard code ‘John%’ in our conditions String. Another option would be the following:

first_name = 'John'  
first_name << '%'  
people = Person.find :all, :conditions => [ 'first\_name LIKE ?', first\_name ]  

This works. We appended the ’%’ onto our name because if we had gotten name from a form submission we would need to tack it on anyways.

This is short, and it works, but now say you want to include the last_name and the state where all John’s live:

name = 'John'  
name << '%'  
last_name = 'Doe'  
state = 'MI'  
people = Person.find :all, :conditions =>  [ 'first\_name LIKE ? AND last\_name = ? AND state = ? ', first\_name, last\_name, state ]

Our conditions array is getting long! The longer it gets the harder it more unmanageable it gets. Better finder support in ARE fixes this.

Better finders

ActiveRecord’s find method has the ability to also take a Hash. But the Hash is very limited in what it can do. It can only do a “column = ‘val’” query. The better finders component takes the Hash argument and allows you to do several cool things.

  • search with Arrays
  • search with Ranges
  • search with Regular Expressions
  • perform less than, greater than, or equal to searches</ul>
  • negate searches

Here are some examples of this functionality:

Find all projects with an id of 5, nothing new…. just shows that you can do this with a hash

Project.find :all, :conditions => { :id => 5 }  

Find all projects where the id is 1,2,3, or 4…. just pass in an array

Project.find :all, :conditions => { :id => [ 1,2,3,4 ] }  

Find all projects where the id is between 1 and 10… pass in a range

Project.find :all, :conditions => { :id => ( 1 .. 10 ) }  

Ranges work for dates to. Find all projects created in the last 30 days

Project.find :all, :conditions => { :created_on => ( Date.today – 30 .. Date.today ) }  

Find all projects where the title matches a regular expression

Projct.find :all, :conditions => { :title => /prototype/ }  

Less Than, Greater Than, Equal To, etc… simply append ‘_lt’, ‘_gt’, ‘_lte’ or ‘_gte’

Project.find :all, :conditions => { :id_lt => 10 }  
Project.find :all, :conditions => { :id_gt => 1 }  
Project.find :all, :conditions => { :id_lte => 10 }  
Project.find :all, :conditions => { :id_gte => 1 }  

Better String Searching with ‘_like’, ‘_starts_with’ and ‘ends_with’.

Project.find :all, :conditions=>{ :title\_starts\_with => 'The' }  
Project.find :all, :conditions=>{ :title\_ends\_with => 'Lake' }  
Project.find :all, :conditions=>{ :title_like => 'Shannara' }  

Find all projects whose titles contain the word ‘prototype’

Project.find :all, :conditions => { :title_like => 'prototype' }  

Find all projects whose titles start with the word ‘ABC’

Project.find :all, :conditions => { :title\_starts\_with => 'ABC' }  

Find all projects whose titles end with the word ‘XYZ’

Project.find :all, :conditions => { :title\_ends\_with => 'XYZ' }  

Negating searches – find all projects whose ids are not 1, 2, 3 or 4

Project.find :all, :conditions => { :id_ne => \[1,2,3,4\] }  

Negating searchings – OR you could use _not instead of _ne

Project.find :all, :conditions => { :id_not => \[1,2,3,4\] }

Munging search criteria

The above examples show you with simple one column search terms. You aren’t limited to that.

For example if you wanted to find all people whose first name started with the term ‘John’ that logged in to the site in the last thirty days, and their last name contains the phrase ‘ens’ you could do:

Person.find :all, :conditions => {  :first\_name\_starts\_with => 'John', :last\_name\_like => 'ens', :logged\_in_at => ( Date.today – 30 .. Date.today ) }

Ruby Class Support

Array support

This will convert a ‘:id => [1,2,3,4]’ to a ‘id IN( 1,2,3,4 )’ statement.

Range support

This will convert a => ( 1 .. 10 )” to a ” id BETWEEN 1 AND 10”. One special thing to note is that Ruby ranges support inclusion ( .. ) and exclusion ( … ). This doesn’t care which one you use, it will always use the min and max values. This plugin treats ( 1 .. 10 ) and ( 1 … 10 ) the same.

Regular expression support

This allows you to use Ruby’s regular expressions to search your database! Some DBA’s may kill you if you do this, so please find out if this is ok to do. The limitations of this are the limitations that your database vendor puts on it’s regular expression support. In MySQL’s case this is limited on things like word boundaries, but hey, you get some level of regular expression support for free.

This isn’t client side regular expression searching, this regex matching is done by the database engine itself.

Negating Searches

You can now negate searches by supplying ‘_ne’ after your key/column identifier in the search Hash.

:conditions => { :id_ne => 5 } # find records where id != 5</ul> 
:conditions => { :id_ne => \[ 1,2,3,4 \] # find records where id IS NOT 1, 2, 3, or 4 

In place of ‘_ne’ you can use ‘_not’ if you feel it provides better readability. This will work for practically anything, just append ‘_ne’ or ‘_not’ and you should be good to go. This will not work for like, starts_with or ends_with searches though.


blog comments powered by Disqus