Rails - Feb 2008 - Complex SQL Query with Pagination

I am trying to write a relatively complex searching mechanism that
searches for items in a product database that has a where clause that
needs many joins (including joins that pass through two tables, and
joins to different tables from the primary items table) to function
properly.  Because the construction of this query depends on options a
user selects, its syntax will need to be dynamically generated per the
user''s input, for example (sql is approximate):

A user is searching for items less than $15:
SELECT * FROM items WHERE price < 15;

A user is searching for items less than $15 from a user named "Bob":
SELECT * FROM items WHERE items.price < 15 and user.name = "Bob"
INNER
JOIN users ON items.owner_id = users.id;

A user is searching for items less than $15 from a user named "Bob"
that have pictures of turkeys:
SELECT * FROM items WHERE items.price < 15 and users.name = "Bob"
and
pictures.content = "turkey" INNER JOIN users ON items.owner_id
users.id INNER JOIN pictures ON pictures.item_id = item.id;

A user is searching for items less than $15 from a user named "Bob"
that have pictures of turkeys from the country of Turkey:
SELECT * FROM items WHERE items.price < 15 and users.name = "Bob"
and
pictures.content = "turkey" and locations.name = "Turkey"
INNER JOIN
users ON items.owner_id = users.id INNER JOIN (pictures ON
pictures.item_id = item.id INNER JOIN (locations ON
picture.location_id = location.id))

And I need to paginate my results (preferably using will_paginate,
since we already have it installed and used in other cases).

Obviously, this isn''t a case where I want to write the world''s
largest
switch statement.  Does anyone know of any plugins or methodologies to
use in a case like this where I might have an arbitrarily large number
of joins and where conditions that need to be built into a SQL where
and join clause, and ultimately paginated?

Thanks,
Bill Harding
--~--~---------~--~----~------------~-------~--~----~
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?hl=en
-~----------~----~----~----~------~----~------~--~---

If any future generations are curious about this, I''ve written my own
class that handles creating an arbitrary number of joins and includes
that handle all of the situations I describe below.  It has some
overlap with ez-where, but I''ve found it a bit simpler for interfacing
with pagination parameters.

On Feb 5, 3:58 am,
"william.hard...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org"
<william.hard...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I am trying to write a relativelycomplexsearching mechanism that
> searches for items in a product database that has a where clause that
> needs many joins (including joins that pass through two tables, and
> joins to different tables from the primary items table) to function
> properly.  Because the construction of this query depends on options a
> user selects, its syntax will need to be dynamically generated per the
> user''s input, for example (sqlis approximate):
>
> A user is searching for items less than $15:
> SELECT * FROM items WHERE price < 15;
>
> A user is searching for items less than $15 from a user named
"Bob":
> SELECT * FROM items WHERE items.price < 15 and user.name =
"Bob" INNER
> JOIN users ON items.owner_id = users.id;
>
> A user is searching for items less than $15 from a user named
"Bob"
> that have pictures of turkeys:
> SELECT * FROM items WHERE items.price < 15 and users.name =
"Bob" and
> pictures.content = "turkey" INNER JOIN users ON items.owner_id
> users.id INNER JOIN pictures ON pictures.item_id = item.id;
>
> A user is searching for items less than $15 from a user named
"Bob"
> that have pictures of turkeys from the country of Turkey:
> SELECT * FROM items WHERE items.price < 15 and users.name =
"Bob" and
> pictures.content = "turkey" and locations.name =
"Turkey" INNER JOIN
> users ON items.owner_id = users.id INNER JOIN (pictures ON
> pictures.item_id = item.id INNER JOIN (locations ON
> picture.location_id = location.id))
>
> And I need to paginate my results (preferably using will_paginate,
> since we already have it installed and used in other cases).
>
> Obviously, this isn''t a case where I want to write the
world''s largest
> switch statement.  Does anyone know of any plugins or methodologies to
> use in a case like this where I might have an arbitrarily large number
> of joins and where conditions that need to be built into aSQLwhere
> and join clause, and ultimately paginated?
>
> Thanks,
> Bill Harding
--~--~---------~--~----~------------~-------~--~----~
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?hl=en
-~----------~----~----~----~------~----~------~--~---