SitePoint Sponsor

User Tag List

Results 1 to 20 of 20

Hybrid View

  1. #1
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Developing a sql where clause in ror

    I have a search form with firstname, lastname, city, postalcode, telephone, etc. One, some or all of the fields must be filled in and that means that I need to create a dynamic where clause and I suppose the best way is to use conditions like
    Code:
    Post.find_by_sql ["SELECT * FROM posts WHERE author = ? AND created > ?", author_id, start_date]
    . I am going to use the find_by_sql method because the statement is quite involved to collect all the information that I need.

    Can anyone point me to an example or tutorial on how to create a string, array or hash that would work for my example. It seems as though this would be a simple task but I can find very little by way of examples on the web and I have exhausted my attempts at success. What I need is someone to enlighten me with a blinding glimpse of the obvious.
    Ruby, Ruby when will you be mine

  2. #2
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    # I assume the params hash looks like this:
    params = {:firstname => '...', :lastname => '...' ....}
    
    vars = []
    conditions = []
    conditions << 'author = ?' and vars << :author if params[:author]
    conditions << 'creaded > ?' and vars << :created_at if params[:created_at]
    # set up more conditions...
    
    Post.find_by_sql ['SELECT * FROM posts WHERE ' + conditions.join(' AND '), *params.values_at(vars)]
    EDIT:

    This is better:

    Code:
    def build_conditions(sql,cond,params)
      [
        [sql, cond.map{|pair|
          pair[1] if params[pair[0]]
        }.compact.join(' AND ')].join(' '),
        params
      ]
    end
    
    params = {
      :firstname => 'a',
      :lastname => 'b',
      :created => '12-05-2003'
    }
      
    Post.find_by_sql build_conditions('SELECT * FROM posts WHERE',
                     [
                       [:firstname, 'firstname = :firstname'],
                       [:lastname, 'lastname = :lastname'],
                       [:created, 'created > :created']
                     ],
                     params)

  3. #3
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that. I will give it a test and see what kind of results I get. As a follow-on, is there a way I can view the results (the actual string) of the build_conditions as an output to the view - I say this because I want to get a better feeling for just what the code produces by viewing the output of the various strings that are generated by the various code elements. Is there also a way to handle sql "like" statements - eg. firstname like %ri%.
    Ruby, Ruby when will you be mine

  4. #4
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, just open a console and type: irb, then paste the build_conditions function, and press enter. Now you can test the function in real time. The returnvalue of it should be passed to the Post.find_by_sql method.

    Like:

    Code:
    Post.find_by_sql build_conditions('SELECT * FROM posts WHERE',
                     [
                       [:firstname, 'firstname like %ri%'],
                       [:lastname, 'lastname = :lastname'],
                       [:created, 'created > :created']
                     ],
                     params)

  5. #5
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it seem as though I am already beyond my scope with other problems poping up as I try to get the method working. First, here are the parameters that are returned from the search form:
    Code:
    Parameters: {"voter"=>{"lastname"=>"", "gender"=>"U", "firstname"=>"Richard", "ph_home"=>"000-000-0000"}, "commit"=>"Search", "residence"=>{"city"=>"", "number"=>"", "streetname"=>"", "postalcode"=>"", "streettype"=>""}}
    And here is a follow-on from what you have sent me:
    Code:
    @search_result = Voter.find_by_sql build_conditions('SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE',
                     [
    		   [[:voter][:firstname], 'v.firstname like %:firstname%'],
                       [[:voter][:lastname], 'v.lastname like %:lastname%'],
    		   [[:voter][:ph_home], 'v.ph_home = :ph_home'],
    		   [[:residence][:number], 'r.number = :number']
    		],
                     params)
    end
    for a few of the parameters. The first problem is my syntax - I am not sure what to use in place of [:voter][:firstname], ect because this is where it first error is occuring. The second problem is flexability. As you can see the params always passes a phone number of 000-000-0000 the default condition so I have to filter that out befor I pass the parameters, same holds for gender. I suppose I could do a check on the params first and reassign values within the params list based on the expected result (eg. if ph_home = 000-000-0000 then set it to ""). Can I impose on you for some more help.
    Ruby, Ruby when will you be mine

  6. #6
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ignore the previous message.

    Well here is what I have so far in the quest for a solution
    Code:
    firstname = '%'+params[:voter][:firstname]+'%' if params[:voter][:firstname]
    	lastname = '%'+params[:voter][:lastname]+'%' if params[:voter][:lastname]
    	if (params[:voter][:ph_home] != '000-000-0000' && params[:voter][:ph_home] != '')
    		ph_home = params[:voter][:ph_home] 
    	else
    		ph_home = ''
    	end
    	number = params[:residence][:number] if params[:residence][:number]
    	streetname = params[:residence][:streetname] if params[:residence][:streetname]
    	streettype = params[:residence][:streettype] if params[:residence][:streettype]
    	city = params[:residence][:city] if params[:residence][:city]
    	postalcode = params[:residence][:postalcode] if params[:residence][:postalcode]
    	
    	params = {
    		:firstname => firstname,
    		:lastname => lastname,
    		:ph_home => ph_home,
    		:number => number,
    		:streetname => streetname,
    		:streettype => streettype,
    		:city => city,
    		:postalcode => postalcode
    	}
    	#breakpoint
    	@search_result = Voter.find_by_sql build_conditions('SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE',
                     [
    		   [ :firstname, 'v.firstname like :firstname'],
                       [:lastname, 'v.lastname like :lastname'],
    		   [:ph_home, 'v.ph_home = :ph_home'],
    		   [:number, 'r.number = :number'],
    		   [:streetname, 'r.streetname = :streetname'],
    		   [:streettype, 'r.streettype = :streettype'],
    		   [:city, 'r.city = :city'],
    		   [:postalcode, 'r.postalcode = :postalcode']
    		],
                     params)
    Now this bit of code works to a point but there are still some serious problems. For example, if one of the search terms is missing or "" the build_conditions method still seems to add the item to the where statement and generally does not produce the desired result. I think I need to "condition" the parameters better to eliminate any sources of error. Can you help me out with the build_conditions statement or any other sources of error you might detect.
    Ruby, Ruby when will you be mine

  7. #7
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm ignoring everything but the residence part for now.

    Code:
    def build_conditions(sql,cond,params)
      [
        [sql, cond.map{|pair|
          pair[1] unless params[pair[0]].blank?
        }.compact.join(' AND ')].join(' '),
        params
      ]
    end
    
    	@search_result = Voter.find_by_sql build_conditions('SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE',
                     [
    		   [:number, 'r.number = :number'],
    		   [:streetname, 'r.streetname = :streetname'],
    		   [:streettype, 'r.streettype = :streettype'],
    		   [:city, 'r.city = :city'],
    		   [:postalcode, 'r.postalcode = :postalcode']
    		],
                     params[:residence])
    Does that work? The build_conditions method should take care of empty fields now. So this:

    Code:
    	number = params[:residence][:number] if params[:residence][:number]
    	streetname = params[:residence][:streetname] if params[:residence][:streetname]
    	streettype = params[:residence][:streettype] if params[:residence][:streettype]
    	city = params[:residence][:city] if params[:residence][:city]
    	postalcode = params[:residence][:postalcode] if params[:residence][:postalcode]
    isn't needed anymore.

  8. #8
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, we are getting closer. As long as I put a value for each variable in the list above, I get a result. However, if I omit one variable I get the following error:
    Code:
     wrong number of bind variables (5 for 1) in: SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE r.number = :number
    . In this case I just entered the house number.

    Now we only did the residence variables. Do you have something in mind for the voter variables firstname, lastname, telephone?
    Ruby, Ruby when will you be mine

  9. #9
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this:

    Code:
    def build_conditions(sql,cond,params)
      [
        [sql, cond.map{|pair|
          pair[1] unless params[pair[0]].blank?
        }.compact.join(' AND ')].join(' '),
        params.select{|p| cond.any?{|c| c[0] == p}}
      ]
    end
    This should work for the residence, even if you don't type in all values.

  10. #10
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It appears as though there is still a problem. For one or more values of the variables it throws an error (this example was for all variables):
    Code:
    Mysql::Error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':number AND r.streetname = :streetname AND r.streettype = :streettype AND r.city' at line 1: SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE r.number = :number AND r.streetname = :streetname AND r.streettype = :streettype AND r.city = :city AND r.postalcode = :postalcode
    My guess is that the variables are not available to the expression and this probably has something to do with that last part of the statement you tacked on to the build_conditions method.
    Ruby, Ruby when will you be mine

  11. #11
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    def build_conditions(sql,cond,params)
      [
        [sql, cond.map{|pair|
          pair[1] unless params[pair[0]].blank?
        }.compact.join(' AND ')].join(' '),
        params.select{|(key,val)| cond.any?{|c| c[0] == key}}
      ]
    end
    *should* work ;-).

  12. #12
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No that did not work either. Same error as the last post. It doesn't seem to be picking up the value of the variable - the sql syntax seems to be ok.
    Ruby, Ruby when will you be mine

  13. #13
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe this?

    Code:
    def build_conditions(sql,cond,params)
      filtered_params = {}
      for (k,v) in params
        filtered_params[k] = v if cond.any?{|c| c[0] == k}
      end
    
      [
        [sql, cond.map{|pair|
          pair[1] unless params[pair[0]].blank?
        }.compact.join(' AND ')].join(' '),
        filtered_params
      ]
    end
    I didn't realize that Hash#select returns an array rather than a Hash.

  14. #14
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nope, that did not work either. I am getting the following error which is similar to the previous ones:
    Code:
    wrong number of bind variables (0 for 2) in: SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE r.number = :number AND r.streetname = :streetname
    . I feel you are about to make the big discovery.
    Ruby, Ruby when will you be mine

  15. #15
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe ;-).

    Could you post the exact code that generates this error, along with a dump of the @params hash?

  16. #16
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the code:
    Code:
    def build_conditions(sql,cond,params)
      filtered_params = {}
      for (k,v) in params
        filtered_params[k] = v if cond.any?{|c| c[0] == k}
      end
    
      [
        [sql, cond.map{|pair|
          pair[1] unless params[pair[0]].blank?
        }.compact.join(' AND ')].join(' '),
        filtered_params
      ]
    end
      def search
    	
    	@search_result = Voter.find_by_sql build_conditions('SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE',
                     [
    		   [:number, 'r.number = :number'],
    		   [:streetname, 'r.streetname = :streetname'],
    		   [:streettype, 'r.streettype = :streettype'],
    		   [:city, 'r.city = :city'],
    		   [:postalcode, 'r.postalcode = :postalcode']
    		],
                     params[:residence])
    	
      end
    and here is the hash
    Code:
    Parameters: {"voter"=>{"lastname"=>"", "firstname"=>"", "ph_home"=>"000-000-0000"}, "commit"=>"Search", "residence"=>{"city"=>"", "number"=>"1241", "streetname"=>"", "postalcode"=>"", "streettype"=>""}}
    Ruby, Ruby when will you be mine

  17. #17
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, now I've tested the code. I believe it works now:

    Code:
    require 'pp'
    
    def build_conditions(sql,cond,params)
      bind = []
    
      [
        sql + ' ' + 
        cond.map do |p|
          if params[p[0]]
            bind << params[p[0]]
            p[1]
          end
        end.compact.join(' AND '),
        *bind
      ]
    end
    
    params = {
      :number => 23,
      :streetname => 'a-streetname',
      :streettype => 'a-streettype',
      :city => 'a-city',
      :postalcode => 'a-postalcode'
    }
    
    pp build_conditions('SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE',
                     {
    		   :number => 'r.number = ?',
    		   :streetname => 'r.streetname = "?"',
    		   :streettype => 'r.streettype = "?"',
    		   :city => 'r.city = "?"',
    		   :postalcode => 'r.postalcode = "?"'
                     },
                     params)
    out:

    Code:
    ["SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE r.postalcode = \"?\" AND r.number = ? AND r.streetname = \"?\" AND r.streettype = \"?\" AND r.city = \"?\"",
     "a-postalcode",
     23,
     "a-streetname",
     "a-streettype",
     "a-city"]

  18. #18
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that. I had to make a few changes to get it to work for the full set of variables and to ensure that only searched variables made it into the where statement. Would you like me to post the final product?

    The only case I have not considered is when the user does not enter any variables (in which case an error is raised). I think I should be able to do this in the model view.

    Since this is all taking place in a popup, the next problem will be to transfer the id and some of the details of the selected individual from one popup window to another. Do you have any thoughts on that issue. I was going to use javascript but perhaps there is a better way in ror.
    Ruby, Ruby when will you be mine

  19. #19
    SitePoint Guru
    Join Date
    Aug 2005
    Posts
    986
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please post your code!

    I don't know if there is a better way to transfer it, but if you refresh the popups, you can use the flash.

  20. #20
    SitePoint Zealot ricklach's Avatar
    Join Date
    Nov 2004
    Location
    Victoria BC
    Posts
    116
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the code that works for all possible input variables:
    Code:
    def build_conditions(sql,cond,params)
    		bind = []
    
    		[
    			sql + ' ' + 
    			cond.map do |p|
    			if ! params[p[0]].blank?
    			bind << params[p[0]]
    			p[1]
    			end
    		end.compact.join(' AND '),
    		*bind
    		]
    end
      def search
    	if ! params[:voter][:firstname].blank? 
    		firstname = '%' + params[:voter][:firstname] + '%'
    	else
    		firstname = ''
    	end
    	if ! params[:voter][:lastname].blank?
    		lastname = '%' + params[:voter][:lastname] + '%'
    	else
    		lastname=''
    	end
    	number = params[:residence][:number] 
    	streetname = params[:residence][:streetname]
    	streettype = params[:residence][:streettype] 
    	city = params[:residence][:city] 
    	postalcode = params[:residence][:postalcode] 
    	if (params[:voter][:ph_home] != '000-000-0000' && params[:voter][:ph_home] != '')
    		ph_home = params[:voter][:ph_home] 
    	else
    		ph_home = ''
    	end
    	params = {
    		:firstname => firstname,
    		:lastname => lastname,
    		:tele => ph_home,
    		:number => number,
    		:streetname => streetname,
    		:streettype => streettype,
    		:city => city,
    		:postalcode => postalcode
    	}
    	#breakpoint
    	@search_result = Voter.find_by_sql build_conditions('SELECT v.* FROM voters v left join residences r on (r.id=v.residence_id) WHERE',
                     {
    		   :firstname => 'v.firstname like ?',
    		   :lastname => 'v.lastname like ?',
    		   :tele => 'v.ph_home = ?',
    		   :number => 'r.number = ?',
    		   :streetname => 'r.streetname = ?',
    		   :streettype => 'r.streettype = ?',
    		   :city => 'r.city = ?',
    		   :postalcode => 'r.postalcode = ?'
                     },
                     params)
    I still need to do some fixing up here but the code as I have shown it here works. Do you have any further suggestions?
    Ruby, Ruby when will you be mine


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •