Using will_paginate with multiple models (Rails)

Question

Pretty sure that I'm missing something really simple here:

I'm trying to display a series of pages that contain instances of two different models - Profiles and Groups. I need them ordering by their name attribute. I could select all of the instances for each model, then sort and paginate them, but this feels sloppy and inefficient.

I'm using mislav-will_paginate, and was wondering if there is any better way of achieving this? Something like:

[Profile, Group].paginate(...)

would be ideal!

Cinderellacindi

Answer 1

Good question, I ran into the same problem a couple of times. Each time, I ended it up by writing my own sql query based on sql unions (it works fine with sqlite and mysql). Then, you may use will paginate by passing the results (http://www.pathf.com/blogs/2008/06/how-to-use-will_paginate-with-non-activerecord-collectionarray/). Do not forget to perform the query to count all the rows.

Some lines of code (not tested)

my_query = "(select posts.title from posts) UNIONS (select profiles.name from profiles)"
total_entries = ActiveRecord::Base.connection.execute("select count(*) as count from (#{my_query})").first['count'].to_i

results = ActiveRecord::Base.connection.select_rows("select * from (#{my_query}) limit #{limit} offset #{offset}")

Is it overkilled ? Maybe but you've got the minimal number of queries and results are consistent.

Hope it helps.

Note: If you get the offset value from a http param, you should use sanitize_sql_for_conditions (ie: sql injection ....)

Loraine

Answer 2

You can get close doing something like:

@profiles, @groups = [Profile, Group].map do |clazz|
  clazz.paginate(:page => params[clazz.to_s.downcase + "_page"], :order => 'name')
end

That will then paginate using page parameters profile_page and group_page. You can get the will_paginate call in the view to use the correct page using:

<%= will_paginate @profiles, :page_param => 'profile_page' %>
....
<%= will_paginate @groups, :page_param => 'group_page' %>

Still, I'm not sure there's a huge benefit over setting up @groups and @profiles individually.

Vidda

Answer 3

in my last project i stuck into a problem, i had to paginate multiple models with single pagination in my search functionality. it should work in a way that the first model should appear first when the results of the first model a second model should continue the results and the third and so on as one single search feed, just like facebook feeds. this is the function i created to do this functionality

def multi_paginate(models, page, per_page)

  WillPaginate::Collection.create(page, per_page) do |pager|

    # set total entries
    pager.total_entries = 0
    counts = [0]
    offsets = []
    for model in models
          pager.total_entries += model.count
          counts << model.count
          offset = pager.offset-(offsets[-1] || 0)
          offset = offset>model.count ? model.count : offset 
          offsets << (offset<0 ? 0 : offset)
    end

    result = []
    for i in 0...models.count
          result += models[i].limit(pager.per_page-result.length).offset(offsets[i]).to_a
    end

    pager.replace(result)
  end

end

try it and let me know if you have any problem with it, i also posted it as an issue to will_paginate repository, if everyone confirmed that it works correctly i'll fork and commit it to the library. https://github.com/mislav/will_paginate/issues/351

Philine

Answer 4

Have you tried displaying two different sets of results with their own paginators and update them via AJAX? It is not exactly what you want, but the result is similar.

Whole

Unfortunately that's not going to cut it - I need to have the two sets of instances together. – Cinderellacindi