Forum: Ruby on Rails Pagination, Joins and Includes

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Af54a0871600db7fbdbb5c558a6e29a3?d=identicon&s=25 Mike Perham (mperham)
on 2005-12-10 19:47
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
Dce47278b4de2597c378881b482d9cb6?d=identicon&s=25 ij.list (Guest)
on 2005-12-11 12:20
(Received via mailing list)
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:
87e4363c61cea7fd62feee2dfcddc93a?d=identicon&s=25 Nick Coyne (nick)
on 2006-04-23 18:53
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?? :)
59ea1b450935b9d70abfec4186b7a4d5?d=identicon&s=25 Jeff Coleman (progressions)
on 2006-04-23 22:52
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
This topic is locked and can not be replied to.