How to use SQL views to Build Reports with Ruby on Rails

SQL views are a powerful capability of a relational database server. This functionality can be easily used to implement important reporting, data mining, and other functionality to a web application built with Ruby on Rails in a manner that may be faster and more concise than an implementation that uses only typical ActiveRecord patterns. The complexity of dealing with the relational data is abstracted away from the Rails application that is a consumer of the data, keeping the model, controllers, and Rails view concisely focused on their appropriate roles.

Why I use SQL Views in my Development Practice

Before I became a full time Ruby on Rails developer in 2011, I had worked as a web developer on other technology stacks for a decade. In each of those stacks the part that has changed the least is the underlaying SQL server. Starting with MySQL 3 in 1999, I have grown to love the power and flexibility that SQL gives the developer for working with relational data. I have now worked with MySQL, Microsoft SQL Server, and PostgreSQL extensively in my career.

With this background, I look for ways to use the full power of the database server when it makes sense. To me, Ruby on Rails is the best general purpose web application development platform to date. ActiveRecord is a wonderful ORM, but there come times when the SQL code that it generates is just not the best tool for the job. The SQL server has capabilities that go beyond what ActiveRecord is going to ever ask it to do.

In my projects, I have used SQL views to compute complex statistics, to discover connections among objects in the system (think of a search index with data mining) and then even have the PostgreSQL database tell the Rails application which objects are related by computing polymorphic associations.

These sorts of situations are beyond the scope of what is needed to show you so that you can try this pattern out for yourself. Just keep in mind that this technique goes well beyond the simple report example that I am sharing with you today. SQL is powerful, flexible, and fast!

What is a SQL View?

In formal terms, a base table in a relational database is called a Relation. But the magic is that these tables are only one type of relation and that all relations are fundamentally interchangeable! This property is called the Principle of Interchangeability and it gives rise to the ability to implement powerful object modeling, inheritance, and composition all from within the relational database!

For an even more formal definition, let’s take a look at the definition given in the database textbook that was co-authored by one of my undergraduate research advisors, Dr. Sham Navathe.

A view in SQL terminology is a single table that is derived from other tables. These other tables could be base tables or previously defined views. A view does not necessarily exist in physical form; it is considered a virtual table, in contrast to base tables, whose tuples are actually stored in the database. This limits the possible update operations that can be applied to views, but it does not provide any limitations on querying a view… (Elmasri, Navathe, Fundamentals of Database Systems Fourth Edition, 2004).

Drs. Elmasri and Navathe use the term Tuple, which is a formal computer science term for an ordered list of elements that have various formal properties. In this context, just think of it as a row in a database table.

The major SQL servers all have support for views, you can create one like this:

CREATE VIEW relation_name AS
  SELECT ... ;

The query can be simple or complex. It can involve a single table or it can perform tens or hundreds of joins. It can expose all fields from the constituent relations, or just a relevant subset, or it can compute new derived fields that are then presented as a ordinary data element in the resulting relation. In other words, it is a very flexible capability of the database server.

For more information about the syntax for your SQL server, see PostgreSQL: Create View, T-SQL (Microsoft) Create View, or MySQL Create View syntax.

Using a SQL View with Rails for a Report

At the most basic level, a SQL view is a stored query. It is a first class relation with its own name. To your Rails application, it is just like an ordinary table in the database that ActiveRecord can deal with without any special Ruby code.

Here is a concrete example of this pattern to create a reporting interface in a Rails application. The first example is a SQL view that I wrote for one of the reports in a project (in the migration that sets it up). The second is the Ruby example of the class that sits upon it.

Creating the SQL View

In order to create the view, we are going to utilize scenic. If you are using a database other than postgresql, check out these third-party database adapters for scenic.

You can install scenic by adding gem 'scenic' to your Gemfile followed by running $ bundle install. Follow steps in READMEs for 3rd party adapter installation.

After scenic is installed, you can create a migration for a new view with:

$ rails generate scenic:view report_state_popularities
      create  db/views/report_state_popularities_v01.sql
      create  db/migrate/[TIMESTAMP]_create_report_state_popularities.rb

Edit db/views/report_state_popularities_v01.sql and input your view code, without the declaration. For example:

SELECT
  MAX(M.id) AS id,
  UPPER(A.state) AS state,
  country,
  COUNT(M.id) AS popularity
FROM submitted_requests M INNER JOIN addresses A ON
  M.address_id = A.id
WHERE A.state != ''
GROUP BY UPPER(A.state), country
ORDER BY popularity DESC;
)

Creating a Read-only Report Model

module Report
  class StatePopularity < ActiveRecord::Base
    scope :us_region, -> { where(country: 'United States') }

    def name
      state
    end

    def to_s
      "#{state}, #{popularity}"
    end

    # This isn't required, but it will prevent an exception
    # if you attempt to save
    def readonly?
      true
    end
  end
end

That’s all there is to the basic model. Now, you can do any ActiveRecord calls with it that you want, except for those that would update the model being that it is a read-only relation.

The Report’s Controller

  class PopularityController < ApplicationController
    def state_popularity
      @states = Report::StatePopularity.us_region
      respond_to do |format|
        format.html {}
      end
    end
  end

The Report’s Rails View

  <table>
    <tr>
      <th>State</th>
      <th>Popularity</th>
    </tr>
    <% @states.each do |state| %>
      <tr>
        <td><%= state.name %></td>
        <td><%= state.popularity %></td>
      </tr>
    <% end %>
  </table>

SQL Views are Powerful!

I invite you to experiment with this pattern and to consider using it in your next project. It is a powerful tool that has served me well over the years.