how to paginate records from multiple models? (do I need a polymorphic join?)

Question

After quite a bit of searching, I'm still a bit lost. There are a few other similar questions out there that deal with paginating multiple models, but they are either unanswered or they pagainate each model separately.

I need to paginate all records of an Account at once.

class Account
  :has_many :emails
  :has_many :tasks
  :has_many :notes
end

So, I'd like to find the 30 most recent "things" no matter what they are. Is this even possible with the current pagination solutions out there?

Like using some combination of eager loading and Kaminari or will_paginate?

---

Or, should I first set up a polymorphic join of all these things, called Items. Then paginate the most recent 30 items, then do a lookup of the associated records of those items.

And if so, I'm not really sure what that code should look like. Any suggestions?

---

Which way is better? (or even possible)

Rails 3.1, Ruby 1.9.2, app not in production.

Kessler

Use will_paginate, this should help: #1466449 – Bobsled

Thank you. But, that's not what I'm looking for. – Kessler

Think "paging through a set of rows of data" rather than paging through multiple rows of one database table may help. It shouldn't matter how many models the data came from. You may also wish to look at kaminiri to see if it meets your needs better. – Barra

thank you @MichaelDurrant but I'm not sure how to paginate "through a set of rows of data". I looked into Kaminari, but wasn't able to find if it worked for multiple tables at once. I guess I'm left wondering how could you fetch the 30 most recent with SQL when it could be any combination of the models. Definitely no sql expert, but is there a way to limit a query to 30 without some sort of n+1 query? – Kessler

Answer 1

with will_paginate :

@records = #do your work and fetch array of records you want to paginate ( various types )

then do the following :

current_page = params[:page] || 1
per_page = 10
@records = WillPaginate::Collection.create(current_page, per_page, records.size) do   |pager|
pager.replace(@records)
end

then in your view :

<%=will_paginate @records%>

Mashburn

Answer 2

Good question... I'm not sure of a "good" solution, but you could do a hacky one in ruby:

You'd need to first fetch out the 30 latest of each type of "thing", and put them into an array, indexed by created_at, then sort that array by created_at and take the top 30.

A totally non-refactored start might be something like:

emails = Account.emails.all(:limit => 30, :order => :created_at)
tasks = Account.tasks.all(:limit => 30, :order => :created_at)
notes = Account.notes.all(:limit => 30, :order => :created_at)
thing_array = (emails + tasks + notes).map {|thing| [thing.created_at, thing] }
# sort by the first item of each array (== the date)
thing_array_sorted = thing_array.sort_by {|a,b| a[0] <=> b[0] }
# then just grab the top thirty
things_to_show = thing_array_sorted.slice(0,30)

Note: not tested, could be full of bugs... ;)

Commiserate

Thank you for your reply. However, that would be too terribly unperformant, having to fetch 60 extra records each time. Then having to keep track of how many of each were presented. Also, you things_array could be refactored to something like: (emails + tasks + notes).sort_by(&:updated_at).take(30) – Kessler

need to reverse to get the most recent: (emails + tasks + notes).sort_by(&:updated_at).reverse.take(30) – Kessler

Yep - definitely unperformant... as I said - just a hacky solution :) – Commiserate

Answer 3

emails = account.emails
tasks = account.tasks
notes = account.notes

@records = [emails + tasks + notes].flatten.sort_by(&:updated_at).reverse

@records = WillPaginate::Collection.create(params[:page] || 1, 30, @records.size) do |pager|
  pager.replace(@records)
end

Thats it... :)

Roxy