Simplifying complex queries with the ActiveRecord Union gem

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

I wondered if we could reduce the number of queries by somehow chaining them. Some research led me to the UNION SQL operator, which operates pretty much like it sounds — joining the results of multiple SELECT statements.

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

The ActiveRecord Union gem has made these queries quite composable and it’s already paying off. Recently, there was an ask to create a newsfeed for managers that would show the sent and received feedbacks of all of their direct reports. No longer are we looking at just one user, but several! How can we get the sent and received feedbacks of a manager’s direct reports, ordered by most recent, and paginated correctly? By creating an array of FeedbackSentAndReceivedByUser queries and union-ing them together!

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

Performance

While the union approach allows for clean, composable queries, how does it perform compared to the pluck approach? Using the Benchmark IPS gem, I compared both approaches with each scenario — 1) a person looking at their own profile, 2) a manager looking at a direct report’s profile, and 3) a peer looking at a coworker’s profile. In each case, union ran faster, ranging between 2 and 4 times faster!

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

The Reflektive app is still running Rails 4.2 (we’re working on upgrading to 5). In Rails 5, the or method is available, and it’s possible the queries could be reconfigured into one SELECT statement that uses many OR operations for various conditions.

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.

--

--

--

A place for Reflektive Engineering to help share our learnings with the community.

Recommended from Medium

How to Perform a Git Force Pull

How to convert CSV to JSON in Python — It’s a cakewalk

What is an API and why should you care?

Ambassadors’ separation method by levels on Crodo

Setting up an API rule to execute queries and mutations regardless of @auth rules on Dgraph with…

Beginner’s Guide to GraphQL with Angular and Apollo

Tech Lead Handbook — Consensus vs Consent

Systems Theory of Change

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
Jared Rader

Jared Rader

Developer at Shopify. Hacking on #Ruby, #Rails, #JavaScript, and more. Former band member (for one night) with @project86band

More from Medium

DOCKER LEARNING IV

How to Revert Local Changes with Git Restore

Feel like creating Virtualization and Hypervisors

Crow API Updates