# Pagination, Joins and Includes

**URL:** <https://www.ruby-forum.com/t/pagination-joins-and-includes/49946>\
**Category:** Rails\
**Created:** [December 10, 2005, 7:47pm UTC](https://www.ruby-forum.com/t/pagination-joins-and-includes/49946 "2005-12-10T19:47:29Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mperham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@mperham](https://www.ruby-forum.com/u/mperham)\
**Post date:** [December 10, 2005, 7:47pm UTC](https://www.ruby-forum.com/t/pagination-joins-and-includes/49946/1 "2005-12-10T19:47:29Z")

</div>

I’m struggling with a complex query that I am trying to paginate.  
Basically I have two tables: Tasks and WorkEntries. A Task has\_many  
WorkEntries and each task belongs\_to a User. Basically I want to  
display recent work entries for the current user so I want to do  
something like:

select we._, task._ from work\_entries we where we.task.user\_id =  
#{current\_user.id}

Now if I do this:

```
@entry_pages, @entries = paginate :work_entry, { :per_page => 20,
	:conditions => ["tasks.user_id = ? and work_entries.stop_at is not 

```

null", current\_user.id],  
:joins =\> “LEFT JOIN tasks ON task\_id = tasks.id”,  
:order\_by =\> “work\_entries.stop\_at DESC” }

Everything queries fine but I get the dreaded N+1 query problem since I  
need to load the task also.

```
@entry_pages, @entries = paginate :work_entry, { :per_page => 20,
	:conditions => ["tasks.user_id = ? and work_entries.stop_at is not 

```

null", current\_user.id],  
:include =\> :task,  
:order\_by =\> “work\_entries.stop\_at DESC” }

This eager loads the task but the pagination count is wrong because  
there is no join for model.count() to take into account so I get the  
cross-product of both tables.

If I try to do both, I get invalid SQL due to the tasks table being  
joined twice. Is there a way to get this to work correctly?

mike

---

<div class="post-metadata">

**Author:** ![mperham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@mperham](https://www.ruby-forum.com/u/mperham)\
**Post date:** [December 11, 2005, 12:20pm UTC](https://www.ruby-forum.com/t/pagination-joins-and-includes/49946/2 "2005-12-11T12:20:05Z")

</div>

On Dec 10, 2005, at 7:47 PM, Mike P. wrote:

> I’m struggling with a complex query that I am trying to paginate.

You could create the Paginator object and model objects separately,  
and add :include in find(), like this:

---

<div class="post-metadata">

**Author:** ![mperham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@mperham](https://www.ruby-forum.com/u/mperham)\
**Post date:** [April 23, 2006, 6:53pm UTC](https://www.ruby-forum.com/t/pagination-joins-and-includes/49946/3 "2006-04-23T18:53:59Z")

</div>

ij.list wrote:

> On Dec 10, 2005, at 7:47 PM, Mike P. wrote:
> 
> > I’m struggling with a complex query that I am trying to paginate.
> 
> You could create the Paginator object and model objects separately,  
> and add :include in find(), like this:

Like how?? 🙂

---

<div class="post-metadata">

**Author:** ![mperham](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/e47c2d/32.png) [@mperham](https://www.ruby-forum.com/u/mperham)\
**Post date:** [April 23, 2006, 10:52pm UTC](https://www.ruby-forum.com/t/pagination-joins-and-includes/49946/4 "2006-04-23T22:52:26Z")

</div>

Nick C. wrote:

> ij.list wrote:
> 
> > On Dec 10, 2005, at 7:47 PM, Mike P. wrote:
> > 
> > > I’m struggling with a complex query that I am trying to paginate.
> > 
> > You could create the Paginator object and model objects separately,  
> > and add :include in find(), like this:
> 
> Like how?? 🙂

After I posted a similar request, a helpful user responded with a custom  
find method that worked with a sql clause:

def find\_by\_sql\_pagination(items\_per\_page, sql, table,  
page\_symbol=:page)  
page = params[page\_symbol] ||= 1  
page = page.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

You can construct your sql clause however you need and use this to  
create the pagination objects from it.

In my own app I’ve overloaded a couple of functions to allow a :sql  
option with the standard paginate command:

@house\_pages, @houses = paginate :houses, :sql =\> [conditions,  
\*variables]

But I haven’t tested it enough to be sure that it works a hundred  
percent. Let me know if you’d like to see that code as well.

Jeff C.man