Pagination, Joins and Includes

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

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:

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:

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs