The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

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

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

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.

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.

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.

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.

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.