Rails find :conditions

Posted on April 20, 2007, 12:19 am, by schmidp, under Development.

The Rails application I’m currently working on accepts URLs like:

GET /submarks GET /submarks?lat=:latitude&lng=:longitude GET /submarks?lat=:latitude&lng=:longitude&user=:user_id GET /submarks?lat=:latitude&lng=:longitude&channel=:channel_id GET /submarks?lat=:latitude&lng=:longitude&channel=:channel_id&user=:user_id

All query parameters are optional, only lat and lng have to appear together.
On the Rails end, I have a Submark model with the attributes: lat, lng, user_id and channel_id.

So how do you implement find call on the Submark model without a long, ugly if-elsif-else statements?
First of all if you only have = as comparators you can use a Hash for the :conditions parameter of the find method:

conditions = {} conditions[:user_id] => params[:user] if params[:user] conditions[:channel_id] => params[:channel] if params[:channel] Submark.find(:all, :conditions => conditions)

But what if you want OR instead of AND in the WHERE clause? Or what if you want do something like lat < 16?
Currently, you have revert back to ugly if-elsif-else statements like:

if params[:user] and params[:channel].nil? Submark.find(:all, :conditions => ["user_id = ?", params[:user]]) elsif params[:user].nil? and params[:channel] Submark.find(:all, :conditions => ["user_id = ?", params[:user]]) elsif params[:user] and params[:channel] Submark.find(:all, :conditions => ["user_id = ? AND channel_id = ?", params[:user], params[:channel]]) else Submark.find(:all) end

A good solution for this problem is the ez_where plugin by Ezra Zygmuntowicz and Fabien Franzen.
It includes new find_where method in your ActiveRecord models which takes a code block building the conditions with a quite nifty syntax.

But I didn’t want to depend on a external plugin, mainly because we are working with edge Rails, so it’s not unlikely that ez_where breaks sometime during development.

Instead I’ve implemented a little helper, create_conditions, which is able to construct a conditions array, in the format the usual find method likes. It also works by utilizing a code block. You get a Conditions class as parameter to your code block, which has the methods and and or.

This is how you would use create_conditions in your controller:

conditions = create_conditions do | c | c.and ["channel_id = ?", params[:channel]] if params[:channel] c.and ["user_id = ?", params[:user]] if params[:user] c.or "distance < 10" end Submark.find(:all, :conditions => conditions)

The result of create_conditions looks like:

["channel_id = ? AND user_id = ? OR distance < 10", 1, 42]

As you can see, you can use both, String or Array based conditions. Hash based conditions are not supported.

Get the code here.

Comment (RSS)  |  Trackback

14 Comments

  1. kain says:
    February 24, 2009 at 8:29 pm

    hi there, very useful plugin, thanks.

    however, it would be nice to provide a method not to write a condition if none is met, eg:

    conditions = create_conditions do |c|
    c.and ["products.company_id = ?", this_user.id] if this_user.is_a_company?
    end

    this will throw an invalid sql syntax, because nothing is satisfied.

  2. Steve says:
    February 24, 2009 at 8:30 pm

    That’s really easy to solve. Just do this:

    conditions = create_conditions do |c|
    c.and ["1=1"]
    c.and ["products.company_id = ?", this_user.id] if this_user.is_a_company?
    end

  3. phil says:
    February 24, 2009 at 8:42 pm

    Excellent, that’s exactly what I need… Learning is fun!

  4. claudemiro says:
    February 24, 2009 at 8:43 pm

    hello. how can i put this helper visible for my controllers? thanks

  5. thoraxe says:
    February 24, 2009 at 8:43 pm

    This is so insanely much faster than what I was doing. I have a horrendous number of associations and this enabled me to handle everything in the find instead of rejecting things, and it was easy to code. great job.

  6. ben says:
    February 24, 2009 at 8:45 pm

    excellent helper method. it cleaned up my code a lot.

  7. Zoran says:
    April 16, 2009 at 11:48 am

    It works. Thanks!

  8. Debty says:
    July 26, 2009 at 7:28 pm

    Ahaan… I will follow.

  9. dambrosio says:
    September 27, 2009 at 2:19 pm

    Thanks man, It is very useful and clean!
    Just linked back to your article from my website: http://www.definenull.com/node/22

    Cheers

  10. jonas says:
    January 7, 2010 at 1:09 pm

    Hi,

    I get undefined method `create_conditions’ when calling the helper.

    The code is in my helpers directory and I have helpers :all in my applications controller.

    What have I missed?

  11. Philipp Schmid says:
    January 7, 2010 at 1:52 pm

    Hi,

    first, I don’t know if you still need this plugin with the current rails version or if it still works.

    said that, you have to place it in RAILS_ROOT/lib and not in the helpers directory.

    all the best, Philipp

  12. Alex says:
    February 5, 2010 at 8:21 pm

    I put it in RAILS_ROOT/lib, but didn’t work for me…
    —————-
    undefined method `create_conditions’ for #
    —————-

    I tried a lot of things, put in helpers, put the code in Helpers/HomeHelper… nothing worked…
    any idea?

    What do you mean by “if you still need this plugin with the current rails version”. Is ther any way to do the same thing you’re doing?

    Thanks you very much..

  13. Impanyphawn says:
    October 22, 2010 at 2:56 am

    http://super-new-s.info
    Certainly. I join told all above. We can communicate on this theme. Here or in PM.

  14. Melynda Usry says:
    January 24, 2011 at 7:17 pm

    Awesome blog, interesting posts and very good content you got here.

Leave a Reply

«
»