Rails - Dec 2005 - 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

-- 
Posted via http://www.ruby-forum.com/.

On Dec 10, 2005, at 7:47 PM, Mike Perham 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:

In some other thread John Dell wrote:
> I had some trouble with finding good examples.
>
> Have a look here: http://api.rubyonrails.org/classes/ 
> ActionController/Pagination.html
>
> I adapted the ''custom'' pagination example to get this
example which
> paginates users by current domain:
>
>  def list
>    @user_pages = Paginator.new self,
>                   User.count(["domain_id = ?", session 
> [:user].domain_id]),
>                   20,
>                   @params[''page'']
>    @users = User.find(:all, :order => ''last_name'',
>                             :limit  =>  @user_pages.items_per_page,
>                             :offset =>  @user_pages.current.offset,
>                             :conditions => ["domain_id = ?",
session
> [:user].domain_id])
>  end

ij.list wrote:
> On Dec 10, 2005, at 7:47 PM, Mike Perham 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?? :)

-- 
Posted via http://www.ruby-forum.com/.

Nick Coyne wrote:
> ij.list wrote:
>> On Dec 10, 2005, at 7:47 PM, Mike Perham 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 Coleman

-- 
Posted via http://www.ruby-forum.com/.