Merge Rails Queries using Kaminari

Question

I have a model in my Rails project named Task. A task also uses the ActiveRecord enum helper to have 4 statuses which are "open, checked, sent and closed". I am also using Kaminari for pagination. It there anyway I can have Rails generate one query to get the first 5 Tasks in each of those scopes? Right now I have to call Task.open.page(1).per(5) Task.checked.page(1).per(5) Task.sent.page(1).per(5) Task.closed.page(1).per(5) which seems very inefficient. Any suggestions?

Answer 1

Depends if you want it to preserve order or not, aka. does it matter if the tasks will be shuffled around during the query? If you need to preserve order, then you'll probably need to dig around with SQL:

See How to have a custom sort order for a union query in Postgres

So in your example, it would be like:

select *
from (
  SELECT 'opened' AS name, *
  FROM tasks 
  WHERE tasks.status = opened LIMIT 5 OFFSET 0

    UNION

  SELECT 'checked' AS name, *
  FROM tasks 
  WHERE tasks.status = checked LIMIT 5 OFFSET 0

    UNION

  SELECT 'sent' AS name, *
  FROM tasks 
  WHERE tasks.status = sent LIMIT 5 OFFSET 0

) t
order by
    CASE
        WHEN name='opened' THEN 1 
        WHEN name='checked' THEN 2
        WHEN name='sent' THEN 3
        ELSE 4
    END;

Now, as you can see, this may be more trouble than it's worth. As far as I know, ActiveRecord doesn't really support this natively.

Of course, if your queries don't have to be order preserving. You can just use this gem:

https://github.com/brianhempel/active_record_union

Which adds union functionality to your ActiveRecord queries which you can do Task.open.page(1).per(5).union(Task.checked.page(1).per(5)) and so on. Then you can just sort themselves in Ruby later on.