Need help constructing complex SQL Queries for search with m

Rails
1

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

2

On Mon, Sep 04, 2006, Tyler P. wrote:

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.

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

3

On 9/5/06, Tyler P. [email protected] wrote:

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

4

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