Simplifying complex queries with the ActiveRecord Union gemJared RaderBlockedUnblockFollowFollowingFeb 8Recently, 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 pagePreviously, 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 problemI 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 OperatorI 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 queriesThe 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:PerformanceWhile 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/100msCalculating — — — — — — — — — — — — — — — — — — – 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.
022005sComparison: using union: 84.
0 i/s using pluck: 20.
8 i/s — 4.
05x slowerPeer looking at coworker’s profileWarming up — — — — — — — — — — — — — — — — — — — using union 7.
000 i/100ms using pluck 2.
000 i/100msCalculating — — — — — — — — — — — — — — — — — — – 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.
007717sComparison: using union: 87.
9 i/s using pluck: 29.
0 i/s — 3.
03x slowermanager looking at direct reportManager looking at direct report’s profileWarming up — — — — — — — — — — — — — — — — — — — using union 6.
000 i/100ms using pluck 4.
000 i/100msCalculating — — — — — — — — — — — — — — — — — — – 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.
082164sComparison: using union: 75.
6 i/s using pluck: 44.
2 i/s — 1.
71x slowerMoving forwardThe 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.
.