Building the SQL WHERE clause dynamically, revisited

December 9th, 2005

In Building the SQL WHERE clause dynamically in Rails, I showed how to build the WHERE clause like this: Ezra Zygmuntowicz of brainsp.at fame created a Cond module to make that even easier:

See Build AR::Base.find’s :conditions clause dynamically take one for the full source code.

Now, if that were turned into a plugin, wouldn’t that be nice ?

2 Responses to “Building the SQL WHERE clause dynamically, revisited”

  1. Ezra Zygmuntowicz Says:
    October 12th, 2007 at 10:25 PM

    Hey Francis

    I was having some weird troubles with the module version of this snippet. So I changed it back to a class. So the way to use it like you are above is like this:

    class SearchController < ApplicationController
  def search
    conditions = Cond.new do
       month '<=', params[:month]
       year '=', params[:year]
       name 'LIKE', "%#{params[:name]}%" 
    end
    @results = Model.find(:all,
        :conditions => conditions.where)
  end
end

    I am thinking about making this a plugin but I need to come up with an easier way to do this. I would like to build AR::Base.find_with_conditions like this:

    class SearchController < ApplicationController
  def search
    @results = Search.find_with_conditions do
       month '<=', params[:month]
       year '=', params[:year]
       name 'LIKE', "%#{params[:name]}%" 
    end
end

    Thats just what I would like the interface to look like. I haven’t gotten it working that way yet. Basically it would generate the Cond object behind the scenes and then call AR::Base.find with the appropriate conditions for the where clause. But AR::Base.find calls itself recursively somewhere and i haven’t gotten around that yet.
    But I will soon and then I will make a plugin.

    Cheers

  2. Ezra Zygmuntowicz Says:
    October 12th, 2007 at 10:25 PM

    Francois-

    I have done some more work on this where clause business. The newest interface adds AR::Base.find_with_conditions that you can use like this:

    q = Model.find_with_conditions do
  foo == 'bar'       # exact value
  baz <=> (1..100)   # 'between'
  woo =~ 'substri%'  # 'like'
  fiz < 10           # lt, gt, leq, geq, etc., should all "just work" 
  nik <= 23
  kez >= 65
end

#=> :conditions will look like this:
["foo = ? AND baz BETWEEN ? AND ? AND woo LIKE ? AND fiz < ? AND nik <= ? AND kez >= ?", 
"bar", 1, 100, "substri%", 10, 23, 65]

    I think this ios a much nicer interface. I will be releasing this updated plugin in the next few days. So look for it on my blog.

    Cheers

Leave a Reply

 

Search

A picture of me

I am François Beausoleil, a Ruby on Rails coder. During the day, I work on XLsuite. At night, I am interested many things. Read my biography

Tags

(4) (1) (1) (1) (1) (2) (1) (1) (1) (2) (2) (1) (2) (1) (3) (1) (2) (1) (1) (1) (1) (2) (14) (1) (1) (1) (1) (2) (1) (1) (2) (0) (1) (4) (1) (3) (1) (1) (1) (1) (1) (1) (0) (3) (2) (1) (2) (1) (3) (1) (5) (2) (10) (10) (11) (14) (2) (1) (3) (1) (1) (1) (1) (1) (0) (1) (2) (2) (2) (1) (1) (1) (4) (1) (3) (1) (4) (2) (2) (25) (2) (1) (1) (0) (1) (1) (1) (23) (25) (1) (1) (13) (1) (1) (1) (4) (5) (1) (1) (1) (4) (1) (2) (3) (4) (4) (1) (1) (1) (8) (3) (1) (5) (5) (2) (2) (2) (4) (8) (7) (1) (1) (1) (1) (2) (4) (1) (4) (12) (2) (1) (2) (4) (1) (1) (1) (2) (8) (2) (3) (2) (2) (1) (3) (1) (1)

Links

Projects I work on

Categories

Archives