Developing a sql where clause in ror

[ricklach]

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

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.

[Fenrir2]

# 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:

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)

[ricklach]

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%.

[Fenrir2]

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:

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

[ricklach]

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:

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:

@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.

[ricklach]

Ignore the previous message.

Well here is what I have so far in the quest for a solution

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.

[Fenrir2]

I'm ignoring everything but the residence part for now.

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:

	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.

[ricklach]

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:

 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?

[Fenrir2]

Try this:

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.

[ricklach]

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):

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.

[Fenrir2]

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 ;-).

[ricklach]

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.

[Fenrir2]

Maybe this?

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.

[ricklach]

Nope, that did not work either. I am getting the following error which is similar to the previous ones:

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.

[Fenrir2]

Maybe ;-).

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

[ricklach]

Here is the 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

Parameters: {"voter"=>{"lastname"=>"", "firstname"=>"", "ph_home"=>"000-000-0000"}, "commit"=>"Search", "residence"=>{"city"=>"", "number"=>"1241", "streetname"=>"", "postalcode"=>"", "streettype"=>""}}

[Fenrir2]

OK, now I've tested the code. I believe it works now:

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:

["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"]

[ricklach]

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.

[Fenrir2]

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.

[ricklach]

Here is the code that works for all possible input variables:

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?