Tuesday, March 25, 2008

ActiveRecord #find syntax and you

I've recently been adding eager-loading of ActiveRecord associations into a project at work, and I've run into a few problems because of how the original #find queries were wrtten. I did a little research, and found out how you can help out AR a bit and save yourself (and me) some hassle.

The easiest way to query AR is to simply use an id, like this:

User.find(10)

This uses this sql, nicely referenced by the table name:

SELECT * FROM users WHERE (users.`id` = 10)

The downside to this is that a plain #find call will throw an exception if no record exists, and that’s not good (unless caught, the client will see a 500 at that point).

We’ve been getting around this by using find calls like this:

User.find( :all, :conditions => ['id = ?', 10] )

Which generates this sql:

SELECT * FROM users WHERE (id = 10)

This way is good because it will return nil if no record is found, instead of throwing an exception. The subtle downside is that the string in the conditions parameter is copied directly into the sql string, because AR doesn’t know how to do anything with it.

If instead you use a hash for the conditions parameter, like this:

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

AR knows that you’re referencing a column from that model’s table, and gives you the safe sql with no bad exception aftertaste:

SELECT * FROM users WHERE (users.`id` = 10)

As an added bonus, because AR is allowed to do it’s magic, if you decide to pass it an array instead of a single integer, it can do the right thing without you having to change your #find code:

User.find( :all, :conditions => { :id => [10,12] } )

Which automatically comes out as:

SELECT * FROM users WHERE (users.`id` IN (10,12))

Summary:

Don’t do this:
User.find(10)
or this: User.find( :all, :conditions => ['id = ?', 10] ).

Do this:
User.find( :all, :conditions => { :id => 10 } )

4 comments:

Flexpert said...

I recently discussed this exact ActiveRecord functionality with some coworkers. Some of them felt as you do, while I tend to agree that the AR implementation is correct.

User.find(10) should throw an exception if there is not User record with an ID of 10. It is an exceptional case if you're loading an exact AR record that you have the ID for. If you knew the ID, then you probably expected it to be there also.

It is probably a typo, but you are incorrect in that #find(:all, :conditions => {:id => 10}) returns nil if no record is found. #find(:all) returns an empty array [] if no query results are found and #find(:first) returns returns nil if no query results are found.

I think that unlike the #find(10) where you're using a known ID, when you use #find(:first/:all, :conditions => {:id => 10}) AR treats that as a variable result query where there may or may not be any results. The :first/:all assist in the handling of result set.

Thanks for the writeup on the topic. I think it's a commonly misunderstood problem.

pete said...

@flexpert:
You're right about the User.find(10) case being correct to throw an exception, I meant that it is unexpected and can result in error messages being presented to the visitor of your site. Instead of not using that form of find, you just need to be careful.

Oh, and thanks for the error correction, I was thinking of #find(:first).

Michael said...

I think I must be missing something here...

Why not just use:
User.find_by_id(10)
which will give you either the user you were looking for, or nil if they don't exist.

Jonah said...

Just use .first rather than .find - it's essentially the same, but doesn't raise an exception