Pagination, Joins and Includes

Rails
1

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

2

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:

3

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?? :slight_smile:

4

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?? :slight_smile:

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