Simplifying complex queries with the ActiveRecord Union gem

Jared Rader
Feb 8, 2019 · 5 min read

Recently, I worked on a new feature that required some complex querying, and the ActiveRecord Union helped me come up with a great solution.

With our Real-Time Feedback product, users can give, receive, and request feedback from peers, managers, and even people outside of their company. The new feature was the Employee Profile page that consolidates all Reflektive products in one spot. The centerpiece of the page is a newsfeed of an employee’s most recently received and sent feedback. The newsfeed allows for switching between showing only received, or sent, and filtering by a few other parameters as well.

Employee Profile page

Previously, we had newsfeeds for both received and sent feedback, but on separate pages with their own endpoints, making querying for each rather simple. But now, we wanted to show both sent and received in the same place.

And the complexity doesn’t end there. Reflektive also distinguishes between different types of feedback, each with certain visibility rules. The most common type of feedback is public recognition that’s visible to everyone. But we also allow folks to give private feedback that’s only visible to the sender and recipient. Also, we allow folks to give themselves feedback that’s only visible to the sender (who is also the recipient). There are even more cases, but this gives you an idea.

You can see where I’m going here — it’s not as simple as defining and calling relations like user.received_feedbacks and user.sent_feedbacks. Depending on who is viewing whose profile, we need to query for different subsets of feedbacks, and we need to make sure we’re doing it quickly and accommodating ordering, pagination, and eager loading (data like user names and avatars, likes, comments and their authors, hashtags, etc.).

One approach I’ve seen when you need to make several different queries with eager loaded data is to pluck the record IDs out of each query using pluck(:id) and feeding them back into an overarching query that performs the eager loading. For example, to handle the scenario I mentioned, I could have done something like this:

This is a simplified example just to illustrate the problem

I had concerns about this approach. For one, it meant executing numerous database calls to pluck out the IDs of the different subsets of feedback. Now that we were combining received and sent feedbacks, this could result in needing to do close to 10 separate queries to achieve our desired result.

Enter the Union Operator

If I could union all the queries together, I could avoid multiple database hits, and allow for pagination, ordering, filtering and eager loading all in one query.

Unfortunately, there is no union query method in ActiveRecord (yet). You can create one with a deep understanding of Arel, but I haven’t yet researched all the fine details.

Fortunately, the ActiveRecord Union gem will give you this functionality, allowing you to call union just like an ActiveRecord query method. You can also use it on an array of queries, [query_1, query_2, query_3].inject(:union), which was perfect for my use case.

I created two query objects, FeedbackReceivedByUser and FeedbackSentByUser. Each class handled the logic of building queries for feedback based on the user doing the querying, who they were querying for, and the visibility rules involved.

Then how would I handle combining sent and received? The same way I joined the queries in each class — by union-ing the resulting queries of each class. I put this logic in another class, FeedbackSentAndReceivedByUser, which unions the result of the other two query objects.

With this addition, each query can be called on its own or combined with another feedback query. For the Profile page, which needs to display sent, received, or both sent and received, depending on the selected filter, I created a query object that takes in this parameter, determines which query to call, and applies the eager loading, ordering and pagination.

Composable queries

When we got this request, I was able to whip up a query object in no time:

Performance

Note that i/s means “iterations per second.”

User looking at their own profile:

Warming up — — — — — — — — — — — — — — — — — — — 
using union 8.000 i/100ms
using pluck 2.000 i/100ms
Calculating — — — — — — — — — — — — — — — — — — -
using union 84.021 (±10.7%) i/s — 416.000 in 5.024028s
using pluck 20.762 (± 4.8%) i/s — 104.000 in 5.022005s
Comparison:
using union: 84.0 i/s
using pluck: 20.8 i/s — 4.05x slower

Peer looking at coworker’s profile

Warming up — — — — — — — — — — — — — — — — — — — 
using union 7.000 i/100ms
using pluck 2.000 i/100ms
Calculating — — — — — — — — — — — — — — — — — — -
using union 87.926 (± 8.0%) i/s — 441.000 in 5.056910s
using pluck 29.043 (±10.3%) i/s — 144.000 in 5.007717s
Comparison:
using union: 87.9 i/s
using pluck: 29.0 i/s — 3.03x slower

manager looking at direct report

Manager looking at direct report’s profile

Warming up — — — — — — — — — — — — — — — — — — — 
using union 6.000 i/100ms
using pluck 4.000 i/100ms
Calculating — — — — — — — — — — — — — — — — — — -
using union 75.604 (±10.6%) i/s — 378.000 in 5.056946s
using pluck 44.176 (±13.6%) i/s — 220.000 in 5.082164s
Comparison:
using union: 75.6 i/s
using pluck: 44.2 i/s — 1.71x slower

Moving forward

ActiveRecord still doesn’t have a union method and as the README of the Union gem says, there have been efforts to add it, but these seem to be stalled at the moment.

Until then, the Union gem will probably suit your needs. Let me know if there are other libraries and techniques that you have used for situations that require complex querying.

reflektive-engineering

A place for Reflektive Engineering to help share our…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store