SQL: Query Result Model Part 2

Are you using SQL while coding in Ruby on Rails? I could even say I think in SQL! This is why I wanted to share a few tips and tricks that will help you work with query result models.

In my previous article, SQL Query Result Model I described how to make a model from a complicated query result. This is very useful if you work with many nonstandard calculations and present results in HTML tables. It happens that ActiveRecord is not enough. For more complicated queries, I use SQL. In this article - the extended version, I will show you how to merge the results from two separate queries into one model.

Previously, we ended up with a query like this:

Query Object:

and stats class:

Now, we will try to merge order statistics with some user statistics on the same report page.

Here’s a simplified query:

Query Object:

As you can see, the only field where we can match order statistics and user statistics is the day column, so we need a way to match rows by day. Unfortunately, in both results we have arrays of hashes and we want to find elements by a key. We may do it using find method with a block to get a matching array element, but there is a better way.

Put this code into the initializer:

It will add hashify_by method to an array class which will create a hash with keys equal to the value of specified key and values equal to the object from the array.

Example:

hashify_by should be used with unique keys. It will assign the last object with a specified key as a value for it.
I wonder why this method is not available in Ruby or Ruby on Rails, as I find it to be really useful.

Now, you can obtain both order and user stats by day - fast and easy.

Let's extend the stats model to use both queries and achieve a merged result. First, you should let your class know which queries the data will come from - you do this by defining a constant with query names.

You should also add new attributes about user statistics:

and methods to retrieve data from every query:

This code defines two class methods: users_stats and orders_stats. Each of them initializes a query object with options and uses the hashify_by method for results. Now you know how to get data, so all you need to do is return the stats objects in the all method.

The complete stats class should look like this:

Short and clear, isn't it?

You may easily extend this model with additional query results just by adding statistics columns and query object names. Do you have any questions about this solution? Or maybe you want to suggest a different approach? Let me know in the comments, I’ll be happy to answer.

EDIT:

As Tomasz Szymczyszyn mentioned in his comment, there's no need to add hashify_by method to the Ruby Array class. We already have Enumerable#index_by method with a similar functionality.

In consequence our code responsible for defining order_stats and users_stats should look like this:

Do you want to know more about SQL in the context of Ruby on Rails? Check out this article about persistent queries in Ruby on Rails with PostgreSQL!