Optional conditions in find

rubyonrails-talk
1

Hassan Kani wrote:

G'day

I am a complete newbie to programming. All I have to go on at this point is the Agile book from Pragmatic Prog. and all the reading I have been doing on the net.

I am looking (begging!) for some help with the following:

I have a form that collects multiple parameters in a view template, as such:

<form action="search" method="get">     <tr>             <td> <span class="list-employee_number" > <input name = "empno" type=text value = ""> </td>             <td> <span class="list-employee_number" > <input name = "title" type=text value = ""> </td>             <td> <span class="list-employee_number" > <input name = "givname" type=text value = ""> </td>             <td> <span class="list-employee_number" > <input name = "surname" type=text value = ""> </td>             <td> <span class="list-employee_number" > <input type = "submit" value = "Search" />     </tr>     </form>

The data entered by the user is then sent to the employee controller file which holds the following:

def search     empno = params[:empno]     title = params[:title]     givname = params[:givname]     surname = params[:surname]

      @employees = Employee.find(:all,:conditions => ["employee_number = ?                                                 and title = ?                                                 and given_name = ?                                                 and surname = ?",                                                 empno,                                                 title,                                                 givname,                                                 surname])   end

This all works fine if the user enters a value into each one of the four fields in the form, but if one of the fields is left blank (eg given_name), I get zero results, even if the data entered into all fields other than given_name does match with an existing record.

From my limited knowledge, this seems to be happening because the SQL query constructed tries to find a record where value = null for the field that was left blank (eg given_name).

How can I amend the find method to only bring field conditions into the query construct if the user has entered values against those fields?

First off, you can use the new hash based conditions if you're using Rails version 1.2.3 (I'm not sure when they were introduced).

If you then change your input names in the view to match the column names (empno should be employee_number and givname should be given_name) you can simply do:

Employee.find(:all,                :conditions => params.reject{|key,value| value.nil?})

If you're not using a recent version either update or ask again.

2

Well ... the params hash also contains a key/value pair like this: params[:action] => "search" it's from the <form action=search ...>

you should put all search params in a su-hash of params, example:

<input name = "searach[employee_number]" type=text value = ""> </td>

this will then be available in params[:search][:employee_number]

etc.pp.

controller then looks like this: def search     Employee.find(:all,                :conditions => params[:search].reject{|key,value| value.nil?})   end

hope that really works :wink:

3

That error is most likely due to the Employee.find returning no results, therefore @employee => nil.

check your development.log what the SQL looks like that is queried, check in the controller with if @employee != nil etc....

what does the params look like on your error page? are they as expected? (shown at the bottom)

4

so what was the error?