Previously we blogged about eager loading calculations with database views in Rails to avoid running lots of database queries. The approach we described really only works if your SQL is fairly static and you don't mind creating database views for each of your calculations. In this post we'll explore an alternative approach that avoids these issues by not using database views. First let's quickly recap the problem...

The Problem

Let's assume we have a very simple data model consisting of organizations that contain many products each of which can be in an active or inactive state:

We'd like to render a view with the first 25 organizations that shows how many active and inactive products they have. A naive way to compute this would be:

The problem with this approach is it will run 51 database queries: one to retrieve the organization and then for each organization one to retrieve the active product count and one to retrieve the inactive product count.

We can do better than that.

Eager Loading Using Database Views

Let's quickly recap how we can solve this problem using the database view approach. First we need to create our database view that computes the necessary calculations:

(Note: This SQL is intentionally naive to illustrate the example. In reality it can be reworked so it can be quickly computed from database indexes.)

Next we create a ProductSummary Active Record model to read from this table:

We'll need to create an association between Organization and ProductSummary:

Finally we can rewrite our algorithm to use this association and standard Rails eager loading to compute the results with just two database queries:

 We got the performance boost we wanted but unfortunately we'll have to change our database view whenever we introduce a new product status that requires aggregate stats. We can do better than that.

Eager Loading Using Inline Views

Instead of creating a database view for our calculation SQL we can use an inline view that's set as part of a default scope on the ProductSummary model:

Notice that we had to implement the ProductSummary.columns method to return column metadata for our inline view SQL since the view doesn't exist in the database. Also notice that the SQL is dynamically generated based on the list of valid statuses. If this list of valid statuses ever changes the ProductSummary class won't require any changes to compute the new aggregates.

Conclusion

This post covered an approach to computing calculations in the database via inline views and ActiveRecord models that don't correspond to actual database objects. We've had success using this technique in certain scenarios. We'd love to hear if you solved similar problems.