A Couple Rails Find Gotchas

March 3rd, 2009 By: Daniel

We ran into a few little gotchas with ActiveRecord’s find method when upgrading from Rails 2.1.1 to 2.2.2. The solutions are pretty trivial, so the main lesson here is to test your code so things like this get caught by your test suite and not the client.

Gotcha #1


>> Rails.version
=> "2.1.1"
>> Client.find_all_by_notify_by(%w(email txt both)).size
=> 2


>> Rails.version
=> "2.2.2"
>> Client.find_all_by_notify_by(%w(email txt both)).size
ActiveRecord::StatementInvalid: Mysql::Error: Operand should contain 1 column(s): SELECT * FROM `clients` WHERE (notify_by = 'email','txt','both' AND clients.deleted_at IS NULL)
>> Client.find(:all, :conditions => ["notify_by = ? OR notify_by = ? OR notify_by = ?", *%w(email txt both)]).size
=> 2

Gotcha #2


>> Rails.version
=> "2.1.1"
>> Client.find_all_by_agent_id(nil).size
=> 26


>> Rails.version
=> "2.2.2"
>> Client.find_all_by_agent_id(nil).size
=> 0
>> Client.find(:all, :conditions => "agent_id IS NULL").size
=> 26

This entry was posted on Tuesday, March 3rd, 2009 at 10:07 am and is filed under Ruby on Rails.

1. Dan Ahern Says:
   April 29th, 2009 at 5:53 pm
   Hi Daniel,
   I would like to point out a couple of things. First off, I would tend to avoid writing code with the named finders. The reason for this is that they are really a hack, what they do is wait for the undefined method error and then catch it and build the query from there. Repetitive use of those can slow your page load down.
   Second, what I would recommend rather than using the array conditions method is to instead switch over to using a hash, much of the issue would be easily solved by using the hash. For example
   Client.find(:all, :conditions => {:notify_by => %w(email txt both)}) will generate an sql query SELECT * FROM clients WHERE notify_by in (’email’,'txt’,'both’);
   With the new Rails 2.2+ you can do neat things with the conditions hash as well like putting a condition for another table.
   Client.find(:all, :conditions => {’projects.payment_type’ => %w(paying nonpaying both)}, :join => :project)
   will generate
   SELECT * FROM clients LEFT JOIN projects on clients.id = projects.client_id WHERE projects.payment_type in (’paying’,'nonpaying’,'both’);
   You also aren’t just limited to arrays.
   Client.find(:all, :conditions => {:activated => true})
   Will generate the expected where activated = 1
   Client.find(:all, :conditions => {:created_at => (Time.now-30.days)..Time.now})
   Will generate created_at BETWEEN (’2009-04-1′, ‘2009-04-30′)
   Client.find(:all, :conditions => {:agent => nil})
   Will generate agent IS NULL
   The only time you should need to slip back to your array conditions is when you have to do less than () or when dealing with much more complex conditions statements.
2. Daniel Says:
   April 30th, 2009 at 9:02 am
   Thanks for the pointers. They should clean up my code a bit as the hash conditions are a lot more readable than the array ones. Yet all too often I still find myself needing < , > or IS NOT NULL.
3. Dan Ahern Says:
   May 12th, 2009 at 8:49 pm
   No problem, I really fell in love with the hash style syntax when I first realized it was available. It just makes a lot more sense than the Array format.
   One thing while I was digging around in to the BDD world that I found today was Squirrel by ThoughtBot.
   http://www.thoughtbot.com/projects/squirrel/
   This allows for Ruby blocks into the find method. I haven’t had a chance to use this yet but I think it would help to move completely out of the ActiveRecord Array conditions syntax.
4. Marnen Laibow-Koser Says:
   May 16th, 2009 at 12:27 am
   [Resubmitting with nonbreaking spaces for indentation.]
   Dan Ahern: AFAIK, the named finders are *not* a performance problem. I haven’t verified this in the ActiveRecord source code, but I remember reading (on the Rails list?) that AR is rather clever in this regard.
   Basically, IIRC, method_missing is invoked *only the first time* that a named finder is encountered, then it caches the method for future calls. I guess it would be something like this (in concept, not necessarily implementation):
   class Person < ActiveRecord::Base
     def method_missing(name, *args, &block)
       if name.to_s =~ /^find_by_/
         method = Proc.new { #some dynamic finder code }
         class << self.class
           self.define_method(name, method)
         end
         self.send(name, *args, block)
       end
     end
   end
   …so that the first time Person.find_by_name is called, you do incur the method_missing overhead — but the second time Person.find_by_name is called, it exists as a real method, so method_missing doesn’t come into play.
   So don’t worry about dynamic finders for performance, so long as you’re not using too many different ones.
5. Marnen Laibow-Koser Says:
   May 18th, 2009 at 9:51 am
   I just confirmed that I was correct about this. Look at .methods on any ActiveRecord class, then run .find_by_whatever and look at .methods again. You’ll see that .methods has gained a ‘find_by_whatever’ entry.