Creating database views with rails21 Nov 2018
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;
has_many :driver_summaries, foreign_key: :short_user_id, primary_key: :short_user_id
create_table "driver_profiles", force: :cascade do |t|
belongs_to :driver_profile, primary_key: :short_user_id, foreign_key: :short_user_id
create_table "driver_summaries", force: :cascade do |t|
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
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>]