ActiveRecord Extensions 0.0.4 Released! 04 Sep 2006
ActiveRecord Extensions 0.0.4 Updates

Better ActiveRecord finders have been added. You can now search by:

  • scalar values ( a=b, this is nothing new )
  • arrays
  • ranges
  • regular expressions

You can also use the typical criteria like:

  • less than
  • greater than
  • less than or equal to
  • greater than or equal to
  • negating search criteria
  • like (String contains)
  • starts with
  • ends with

This is all easier then you think also. Here’s a quick example:

class Project < ActiveRecord::Base ; end

# 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'.

# 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] }

# OR you could use _not instead of _ne
Project.find :all, :conditions => { :id_not => [1,2,3,4] }

:conditions takes a Hash now?

Yes. ActiveRecord::Base.find takes a Hash anyways. It just isn’t intelligent with how to deal with the Hash, so I made it smart. In my opinion a Hash clears up the nasty conditions array of partial SQL statements.

The key of the Hash can be a String or a Symbol. The value can be an Array, Range, Regular Expression or any other scalar value such as a String or number.

The :conditions Hash has a one-up on the :conditions Array. It will correctly quote your values based on the column that you are searching against. This is important because if you have an id column which is an integer, and you pass in the Strings ‘1’ MySQL will not guarantee that it will use your index on the column id, because it has to do a type cast/conversion. So you’ll end up with potentially a full table scan for a search that should have used indexes. The :conditions Hash is intelligent on all searches it supports.

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 “:id => ( 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
: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.

Repository Changes

My repository has been moved to rubyforge. Please feel free to access it anonymously using:

  • svn://rubyforge.org/var/svn/arext

If you have a patch please send to me privately via email, zdennis at mktec dot com or zach dot dennis at gmail dot com. If you want to help out on the project start by sending patches and having email correspondence with me, and then we’ll work on getting you commit access to the repository.

What Databases Are Supported With This Release?

MySQL 5 for sure. I am pretty sure 4.1 will work, but I haven’t verified the compatibility for things like the regular expression support.

PostgreSQL is NOT supported right now. I am still working on this. I want to better understand how PostgreSQL works before releasing updates for it. I haven’t had any need or special requests for this functionality for PostgreSQL so I will humbly use that as my excuse. If it is not needed, why spend time when there are MySQL users who care about performance and their rails apps. ;) I’m not trying to start a flamewar, but if you are a PostgrSQL user and you want some of functionality post a comment, or kick me an email, zdennis at mktec dot com or zach dot dennis at gmail dot com, otherwise I’ll assume you’re happy with the current state of affairs. I will eventually get to PostgreSQL support but your comments will just make it happy quicker.

No other DBs at this time are expected to be supported, although I do plan on adding them in.

Download Now

“Download ActiveRecord Extensions 0.0.4”:http://blogs.mktec.com/zdennis/files/activerecord_extensions-0.0.4.tar.gz

Comments, Questions, Feedback

Email me at

  • zach dot dennis at gmail dot com
blog comments powered by Disqus