# Complex SQL Query with Pagination

**URL:** <https://www.ruby-forum.com/t/complex-sql-query-with-pagination/128150>\
**Category:** Rails\
**Created:** [February 5, 2008, 12:59pm UTC](https://www.ruby-forum.com/t/complex-sql-query-with-pagination/128150 "2008-02-05T12:59:32Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [February 5, 2008, 12:59pm UTC](https://www.ruby-forum.com/t/complex-sql-query-with-pagination/128150/1 "2008-02-05T12:59:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [February 7, 2008, 9:24am UTC](https://www.ruby-forum.com/t/complex-sql-query-with-pagination/128150/2 "2008-02-07T09:24:54Z")

</div>

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, “removed\_email\_address@domain.invalid”