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]]

=> [#<RecentDrivingSummary:0x00007f87c01a2580 id: 1, short_user_id: 6171910, username: "alison", average_score_accel: 89.4163396535677>,
 #<RecentDrivingSummary:0x00007f87c01a2440 id: 3, short_user_id: 6210980, username: "Sal", average_score_accel: 80.7338236469113>,
 #<RecentDrivingSummary:0x00007f87c01a2300 id: 4, short_user_id: 6222008, username: "Gregory", average_score_accel: 80.2317805210835>,
 #<RecentDrivingSummary:0x00007f87c01a21c0 id: 5, short_user_id: 6170433, username: "Tom", average_score_accel: 88.2581367463545>,
 #<RecentDrivingSummary:0x00007f87c01a2030 id: 6, short_user_id: 6240247, username: "Sandy", average_score_accel: 95.2707508231227>,
 #<RecentDrivingSummary:0x00007f87c01a1ef0 id: 9, short_user_id: 6171320, username: "Matthew", average_score_accel: 89.5460293726325>,
 #<RecentDrivingSummary:0x00007f87c01a1db0 id: 11, short_user_id: 6210432, username: "Carlos", average_score_accel: 93.1588153398447>,
 #<RecentDrivingSummary:0x00007f87c01a1c70 id: 12, short_user_id: 6237114, username: "John", average_score_accel: 88.7137779598431>,
 #<RecentDrivingSummary:0x00007f87c01a1b30 id: 13, short_user_id: 6241435, username: "Daniel", average_score_accel: 92.2150350113147>,
 #<RecentDrivingSummary:0x00007f87c01a19f0 id: 15, short_user_id: 6244943, username: "Gurbir", average_score_accel: 87.9780951166859>]