Rails - Sep 2006 - 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


--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On Mon, Sep 04, 2006, Tyler Prete 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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

On 9/5/06, Tyler Prete <psyonic-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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.
>
> Thanks in advance,
> --Tyler Prete
>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---

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

On 9/4/06, Max Muermann <ruby-DC/T6mWKptNg9hUCZPvPmw@public.gmane.org>
wrote:
>
>
> On 9/5/06, Tyler Prete
<psyonic-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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.
> >
> > Thanks in advance,
> > --Tyler Prete
> >
>
> 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
>
> >
>

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk
-~----------~----~----~----~------~----~------~--~---