Creating database views with rails

21 Nov 2018, by Robert Matesic

database view in 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.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]] => [#, #, #, #, #, #, #, #, #, #] ```

Cookies help us deliver our services. By using our services, you agree to our use of cookies.