Sorting between multiple Models in Ruby on Rails

Recently, I ran into a situation where I had two different Models where I needed to display both types of records in chronological order. We could grab all the records, and apply a simple sort.

comments=Comment.active.to_aryposts=Post.active.to_ary# Combine them together and sort.comments_and_posts=comments+postscomments_and_posts.sort!{|a,b|a.created_at<=>b.created_at}# Use Kaminari to paginate acrossresults=Kaminari.paginate(comments_and_posts).page(params[:page])

While this is easy to read and understand, this is problematic as Comments and Posts grows. This method would require grabbing all the records before applying a sorted order. Pagination is also affected poorly, since we still need to grab both entire sets of records.

We can leverage the database to handle the sorting for us, but Rails does not have a pretty ORM way in the documentation to perform this kind of operation. We can use plain SQL.

Raw SQL Union

We use the UNION operator in SQL to combine both records together, then select from a subquery.

Since there is a possibility that ids are shared between the models, we need to generate a unique_id in these queries.

While we can go through each row in comments and posts and find by ID, that would give us about 25 additional queries. We can do a little extra work to reduce the amount of queries down to 2.

# Store their order in the array.id_positions=comments_and_posts.map{|x|x['unique_id']}items=comments_and_posts.group_by{|x|x['group_type']}items.eachdo|group_type,item|# Get only the ids to reduce amount of queries neededids=item.collect{|x|x['id']}ifgroup_type=='posts'content_items=Post.where(id: ids).to_aryelifgroup_type=='comments'content_items=Comment.where(id: ids).to_aryend# Replace original search with new searched contentcontent_items.eachdo|content|unique_id=group_type+content.id.to_scontent_index=id_positions.index(unique_id)comments_and_posts[content_index]=contentendendcomments_and_posts

Drawbacks to Raw SQL

By diving into Raw SQL, we have direct control of what to query. For smaller queries, this is manageable.

We lose some key features, like scopes. Scopes are composable, and simplifies our understanding of queries. We would have to maintain a separate set of queries if we decide to add filtering.

Raw SQL is also reliant on having a specific flavor of SQL. If we decide to switch MySQL to Postgres, some queries would change and break.

Using Arel to access existing scopes

We’re looking to use ActiveRecord’s Arel, to improve our codebases readability. Note: Since this is a private API, it may change without warning. It is expected that Arel becomes part of public API in Rails 5.1.

We can replace the raw SQL using Arel as a Query Builder. For ActiveRecord::Relation query, we can call .arel to drop into Arel.

Rewriting SQL into Arel Components

Since Arel is used to build queries only, we’ll end up transforming the resulting query builder to a SQL String with .to_sql.