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!

Accepted Answer

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 ....)

Using will_paginate with multiple models (Rails)

Solution

Problem