Need help constructing complex SQL Queries for search with multiple optional values

For a recent project I had to construct an advanced search page with some ranges and some select boxes, all of which are optional. The difficulty came in figuring out a way to construct a proper SQL query from the options provided. I hacked one together that works for the time being, but I would like to know how this is done properly. Does anyone have any links to references where this is explained, preferably in a Ruby/Rails setting, but other languages would be fine as well.

Thanks in advance,
–Tyler Prete

I don't know about "properly", but a year or so ago I wrote some code
that'll take a hash and convert it to a SQL snippet suitable for a
:conditions => call.

If I'm understanding your question correctly, this may be what you're
looking for. We used it to allow our users to construct a complex
search query based on all the fields in the table.

I talked about it on my blog:

http://blog.bleything.net/articles/2005/07/12/converting-form-hashes-to-where-clauses

There's also a link to the code there, and Richard Stephens commented
with a modification to use IN for arrays.

I haven't touched or used this code in over a year, but I can't think of
any reason why it wouldn't still work. If you try it and it doesn't,
let me know and I'll try to help.

Hopefully that's what you're getting at. Otherwise, good luck!

Ben

I am currently working on a plugin called CriteriaQuery which should
make constructing complex queries a lot simpler. Should be properly
released within a few days. It constructs a hierarchical tree of query
conditions, similar to the parse tree that the DB server generates
from the SQL.

What it allows you to do is things like:

pq = Person.query
pq.first_name_like(params[:first_name]) if params[:first_name]
pq.last_name_like(params[:last_name]) if params[:last_name]
results = pg.find(:limit=>10, :offset=>20)

Where the LIKE restrictions are only added if the parameters have been
entered by the user.

It supports joins and aggregate conditions as well:

pq = Person.query
# disjunction is a fancy name for "or"...
pq.disjunction.first_name_like("%#{params[:name]}%").last_name_like("%#{params[:name]}%")
pq.join('address').street_eq( params[:street] ).state_eq(params[:state])
pq.find

This will find all people whose first name or last name contain
params[:name] and who live in a particular street in a particular
state.

Current pre-release version is available from svn:
http://3columns.net/rubyplayground/projects/criteria_query/trunk/

No website yet, I haven't had time to put that together.

Have a look at the README and let me know if you get stuck somewhere.

Cheers,
Max

That is almost exactly what I had in mind. I’ll be keeping an eye on this for when you properly release it.

Thanks,
–Tyler