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(&#39;average_score_accel &gt; 75&#39;).limit(10) RecentDrivingSummary Load (10.4ms) SELECT 'recent_driving_summaries'.* FROM 'recent_driving_summaries' WHERE (average_score_accel &gt; 75) LIMIT $1 [['LIMIT', 10]] =&gt; [#<recentdrivingsummary:0x00007f87c01a2580 1="" id:="">, #<recentdrivingsummary:0x00007f87c01a2440 3="" id:="">, #<recentdrivingsummary:0x00007f87c01a2300 4="" id:="">, #<recentdrivingsummary:0x00007f87c01a21c0 5="" id:="">, #<recentdrivingsummary:0x00007f87c01a2030 6="" id:="">, #<recentdrivingsummary:0x00007f87c01a1ef0 9="" id:="">, #<recentdrivingsummary:0x00007f87c01a1db0 11="" id:="">, #<recentdrivingsummary:0x00007f87c01a1c70 12="" id:="">, #<recentdrivingsummary:0x00007f87c01a1b30 13="" id:="">, #<recentdrivingsummary:0x00007f87c01a19f0 15="" id:="">] /recentdrivingsummary:0x00007f87c01a19f0/recentdrivingsummary:0x00007f87c01a1b30/recentdrivingsummary:0x00007f87c01a1c70/recentdrivingsummary:0x00007f87c01a1db0/recentdrivingsummary:0x00007f87c01a1ef0/recentdrivingsummary:0x00007f87c01a2030/recentdrivingsummary:0x00007f87c01a21c0/recentdrivingsummary:0x00007f87c01a2300/recentdrivingsummary:0x00007f87c01a2440/recentdrivingsummary:0x00007f87c01a2580