SitePoint Sponsor |
|
User Tag List
Results 1 to 20 of 20
-
Apr 29, 2006, 11:37 #1
- 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]
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
-
Apr 29, 2006, 13:49 #2
- 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)]
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)
-
Apr 30, 2006, 13:10 #3
- 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
-
Apr 30, 2006, 13:50 #4
- 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)
-
Apr 30, 2006, 14:15 #5
- 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"=>""}}
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
Ruby, Ruby when will you be mine
-
Apr 30, 2006, 21:17 #6
- 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)
Ruby, Ruby when will you be mine
-
May 1, 2006, 03:33 #7
- 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])
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]
-
May 1, 2006, 19:53 #8
- 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
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
-
May 2, 2006, 02:59 #9
- 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
-
May 2, 2006, 09:24 #10
- 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
Ruby, Ruby when will you be mine
-
May 2, 2006, 11:29 #11
- 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
-
May 2, 2006, 13:41 #12
- 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
-
May 3, 2006, 02:17 #13
- 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
-
May 3, 2006, 08:50 #14
- 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
Ruby, Ruby when will you be mine
-
May 3, 2006, 10:32 #15
- 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?
-
May 3, 2006, 11:10 #16
- 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
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
-
May 8, 2006, 08:22 #17
- 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)
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"]
-
May 10, 2006, 21:32 #18
- 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
-
May 11, 2006, 06:43 #19
- 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.
-
May 11, 2006, 20:48 #20
- 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)
Ruby, Ruby when will you be mine
Bookmarks