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