# Complex SQL in paginate command?

**URL:** <https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893>\
**Category:** Rails\
**Created:** [April 13, 2006, 8:50am UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893 "2006-04-13T08:50:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 13, 2006, 8:50am UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/1 "2006-04-13T08:50:23Z")

</div>

Hi all,

Is there a way to create pagination with a complex SQL, more complex  
than the :conditions option will support?

I have two databases, houses and images. Houses has\_many images, and  
each Image belongs\_to house.

I’m creating a search engine for the house records and I’d like to be  
able to filter out all the houses without any associated images.

So far I’m doing this by constructing a SQL query that goes something  
like this–an example using a maximum price:

“select distinct h.\* from houses h, images i where price \< 1000000 and  
i.house\_id = h.id order by price desc”

I’m not sure how to paginate this query. Is it possible? Is there a  
simpler way to filter out all the houses without an associated image  
from a search?

Thanks,  
Jeff C.man

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 13, 2006, 9:30am UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/2 "2006-04-13T09:30:50Z")

</div>

Mr Coleman:

On 4/12/06, Jeff C.man [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I’m not sure how to paginate this query. Is it possible? Is there a  
> simpler way to filter out all the houses without an associated image  
> from a search?

Use the following (untested) code in application.rb:  
def find\_by\_sql\_pagination(items\_per\_page,sql,table)  
page = (params[:page] ||= 1).to\_i  
offset = (page - 1) \* items\_per\_page  
@results=table.find\_by\_sql(sql)  
@pages=Paginator.new(self, @results.length, items\_per\_page, page)  
@results=@results[offset…(offset + items\_per\_page - 1)]  
return @pages,@results  
end

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 13, 2006, 9:37am UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/3 "2006-04-13T09:37:16Z")

</div>

Hasan D. wrote:

> Mr Coleman:
> 
> On 4/12/06, Jeff C.man [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > I’m not sure how to paginate this query. Is it possible? Is there a  
> > simpler way to filter out all the houses without an associated image  
> > from a search?
> 
> Use the following (untested) code in application.rb:  
> def find\_by\_sql\_pagination(items\_per\_page,sql,table)  
> page = (params[:page] ||= 1).to\_i  
> offset = (page - 1) \* items\_per\_page  
> @results=table.find\_by\_sql(sql)  
> @pages=Paginator.new(self, @results.length, items\_per\_page, page)  
> @results=@results[offset…(offset + items\_per\_page - 1)]  
> return @pages,@results  
> end

That seems to do the trick! Thanks. I’m going to study this example  
closely.

Jeff

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 14, 2006, 2:30am UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/4 "2006-04-14T02:30:10Z")

</div>

Hey Philip,

I know you from SeaBUG. Nice to see a familiar “face” around here. 🙂

Everything would indicate that you are correct, but without the  
actual definition of the variable sql, there’s no way of saying for  
certain.

Of course if the code does rely on the sql to limit the return query,  
then the method has some inherent design problems, I’d say.

But I guess RAM is cheap these days… 🙂

Tres

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 14, 2006, 1:44am UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/5 "2006-04-14T01:44:26Z")

</div>

> def find\_by\_sql\_pagination(items\_per\_page,sql,table)  
> page = (params[:page] ||= 1).to\_i  
> offset = (page - 1) \* items\_per\_page  
> @results=table.find\_by\_sql(sql)  
> @pages=Paginator.new(self, @results.length, items\_per\_page, page)  
> @results=@results[offset…(offset + items\_per\_page - 1)]  
> return @pages,@results  
> end

I’m new to rails and have a question about this… let’s say there’s  
1,000  
rows in his table, but he wants to show 10 at a time.

Isn’t @results=table.find\_by\_sql(sql) going to return all 1,000 rows?

Thanks!

-philip

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 14, 2006, 2:49pm UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/6 "2006-04-14T14:49:50Z")

</div>

While it may return all of the results into @results (I haven’t tested  
it  
myself), he can then break out the number of results he actually needs.

On 4/13/06, Tres Wong-Godfrey \<

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 14, 2006, 5:48pm UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/7 "2006-04-14T17:48:30Z")

</div>

On 4/12/06, Jeff C.man [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> So far I’m doing this by constructing a SQL query that goes something  
> like this–an example using a maximum price:
> 
> “select distinct h.\* from houses h, images i where price \< 1000000 and  
> i.house\_id = h.id order by price desc”
> 
> I’m not sure how to paginate this query. Is it possible? Is there a  
> simpler way to filter out all the houses without an associated image  
> from a search?

Try ‘paginate\_collection’ out. I’ve found this to be a handy snippet  
when dealing with strange ‘distinct’ queries and whatnot.  
Scroll down into the comments for an updated version with some block  
syntax.

> **[BigBold - Informasi Tentang Bisnis dan Marketing](http://www.bigbold.com)**
>
> BigBold ialah situs yang memberikan segala macam Informasi Tentang Bisnis dan Marketing

–Wilson.

---

<div class="post-metadata">

**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\
**Post date:** [April 17, 2006, 12:37am UTC](https://www.ruby-forum.com/t/complex-sql-in-paginate-command/93893/8 "2006-04-17T00:37:36Z")

</div>

Hasan D. wrote:

> Mr Coleman:
> 
> On 4/12/06, Jeff C.man [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > I’m not sure how to paginate this query. Is it possible? Is there a  
> > simpler way to filter out all the houses without an associated image  
> > from a search?
> 
> Use the following (untested) code in application.rb:  
> def find\_by\_sql\_pagination(items\_per\_page,sql,table)  
> page = (params[:page] ||= 1).to\_i  
> offset = (page - 1) \* items\_per\_page  
> @results=table.find\_by\_sql(sql)  
> @pages=Paginator.new(self, @results.length, items\_per\_page, page)  
> @results=@results[offset…(offset + items\_per\_page - 1)]  
> return @pages,@results  
> end

I studied this example and then went through the source code for  
paginate, and found a way to overload paginate and add a :sql option. I  
put this in my application.rb file:

# this is the paginate object’s default options hash, setting defaults

DEFAULT\_OPTIONS[:sql] = nil

def find\_collection\_for\_pagination(model, options, paginator)  
unless options[:sql].nil?  
options[:sql][0] += " order by #{options[:order\_by] ||  
options[:order]} " unless (options[:order\_by].nil? &&  
options[:order].nil?)  
options[:sql][0] += " limit #{options[:per\_page]} offset  
#{paginator.current.offset} "  
model.find\_by\_sql(options[:sql])  
else  
super  
end  
end

This probably isn’t complete, but it allows a basic use of the paginate  
function with a :sql option, like so:

paginate :houses, :sql =\> [“select distinct h.\* from houses h, images i  
where h.price \< ? and i.house\_id = h.id”, params[:price]], :per\_page =\>  
10

I even found the little-documented paginate option :parameter, which  
lets you define a different parameter name besides :page to use for the  
page number. That’s useful because I have a second pagination object on  
the page, which is an image display, and you can page through the images  
associated with a house using Ajax.

Anyone have any thoughts? What could I have done better?

I’m curious if there’s a reason Rails’ default paginate method doesn’t  
have a :sql option in the first place, it seems fairly straightforward  
to implement.

Jeff C.man