Optional conditions in find

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:

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?

I have spent a long time trying to figure this out, but just cannot seem
to come up with a solution.

Any help would be very much appreciated.

Hassan Kani wrote:

        <td> <span class="list-employee_number" > <input type =
givname = params[:givname]
                                            surname])

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.


Cheers,

  • Jacob

Many thanks Jacob, your method is far simpler than what i was trying.
Will try it as soon as I get out of here. :wink:

thanks again

I seem to still be having problems with this one.

My new code in the view now matches the correct column names. In the
controller, I now have the following:

def search
employee_number = params[:employee_number]
title = params[:title]
given_name = params[:given_name]
surname = params[:surname]

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

end

If I leave all criteria blank in the search, I am now getting the error:

ActiveRecord::StatementInvalid in EmployeesController#search
Mysql::Error: #42S22Unknown column ‘employees.action’ in ‘where clause’:
SELECT * FROM employees WHERE (employees.action = ‘search’ AND
employees.controller = ‘employees’)

I’m sure I have done something wrong, but just cannot see what.

ps: am using rails version 1.2.3.

Thanks for the help…but now I get:

You have a nil object when you didn’t expect it!
You might have expected an instance of Array.
The error occurred while evaluating nil.reject

when i try to submit the form with just a single parameter.

Sorry, I know it must be a really simple error…bit lost here.

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:

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:

On 5 Jun., 12:59, Complete N. [email protected]

Here’s what I can see. I do a search using just employee number (and
the number I know does exist, being 1123). So…

In the development log:

Parameters: {“search”=>{“title”=>nil, “given_name”=>nil,
“employee_number”=>“1123”, “surname”=>nil}, “action”=>“search”,
“controller”=>“employees”}
e[4;36;1mEmployee Load (0.000000)e[0m e[0;1mSELECT * FROM employees
WHERE (employees.employee_number = ‘1123’)

Looks like the SLQ query itself is OK. So far, so good.

On the error page, it says I have a problem with the line that reads:

<% for employee in @employees %>

And on the bottom of that error page, the parameters are shown as:

Parameters: {“search”=>{“title”=>nil, “given_name”=>“Jason”,
“employee_number”=>nil, “surname”=>nil}}

I could understand if no records were matched that this error may come
about, but the record does actually exist. I also tried doing a search
using just one of the other fields and get the same results…

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)

On 5 Jun., 13:34, Complete N. [email protected]

so what was the error?

On 5 Jun., 14:51, Complete N. [email protected]

just figured it out! all working now…many thanks for all the help,
much appreciated.

cheers :wink:

I had:

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

when I should have had:

def search
@employees = Employee.find(:all,
:conditions => params[:search].reject{|key,value|
value.nil?})
end

I’m now onto my next mission of trying to figure out how to allow LIKE
search using this approach (eg. looking for employees by name must cater
for slight mis-spellings of names)…time for another coffee!

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs