Creating database views with rails

A view is a virtual table in the database that contains data from one or more tables. They don't store any data but rather a set of stored queries. Since views are basically tables, they can be queried against.

Say we have the following 2 tables;

DriverProfiles

has_many :driver_summaries, foreign_key: :short_user_id, primary_key: :short_user_id

create_table 'driver_profiles', force: :cascade do |t|
  t.integer :id
  t.integer 'short_user_id'
  t.string 'email'
  t.string 'username'
end

DriverSummaries

belongs_to :driver_profile, primary_key: :short_user_id, foreign_key: :short_user_id

create_table 'driver_summaries', force: :cascade do |t|
  t.string 'email'
  t.integer 'short_user_id'
  t.float 'average_score'
  t.float 'average_score_accel'
  t.float 'average_score_brake'
  t.float 'average_score_turn'
  t.float 'average_score_speeding'
  t.float 'average_score_phone_motion'
  t.datetime 'score_date'
end

Now the problem we have is, we want to fetch all DriverProfiles with DrivingSummaries for the last 2 weeks using the score_date having an average_score_accel of at least 75 and then return the results as a combination of some columns from both tables.

Now we could approach this by adding a bunch of scopes and raw SQL under the DriverSummary model or we can store these queries in a view so it can easily be reused in the future while keeping our classes clean.

We want to create a view that fetches the last 2 weeks of DrivingSummaries and combines the data from DriverProfiles in the results.

What we first need to do is generate a migration file. Since ActiveRecord DSL does not give us any methods to create views, we will need to use SQL to build this out.

rails g migration recent_driving_summaries_view

class RecentDrivingSummariesView < ActiveRecord::Migration[5.2]
  def change
    reversible do |dir|
      dir.up do
        execute <<-SQL
          CREATE OR REPLACE VIEW public.recent_driving_summaries AS
          SELECT 
            p.id, 
            p.short_user_id, 
            nullif(p.username, '') as username,
            (SELECT AVG(s.average_score_accel) 
             FROM driver_summaries s 
             WHERE s.short_user_id = p.short_user_id 
             AND s.score_date >= NOW() - interval '2 week') AS average_score_accel
          FROM driver_profiles p;
        SQL
      end
      
      dir.down do
        execute <<-SQL
          DROP VIEW IF EXISTS public.recent_driving_summaries;
        SQL
      end
    end
  end
end

The next step was to create a modal in rails so that I can access this view in the console. This works exactly the same as any other table in the database, so since my view was called recent_driving_summaries my modal will use the same rails naming convention.

class RecentDrivingSummary < ApplicationRecord
end

Now that I have my view and model all setup I can now fetch the data with an additional where statement on the new view so that I can filter my results by all users with DriverProfiles that have an average average_score_accel score based on the last 2 weeks of their DriverSummaries data.

NOTE: When querying large data sets its wise to put a limit. This is because Postgres will need to run all subqueries for each row in the view.

RecentDrivingSummary.where('average_score_accel > 75').limit(10)

RecentDrivingSummary Load (10.4ms) SELECT 'recent_driving_summaries'.* FROM 'recent_driving_summaries' WHERE (average_score_accel > 75) LIMIT $1 [['LIMIT', 10]]
=> [#, #, #, #, #, #, #, #, #, #]