Clash Royale CLAN TAG#URR8PPP

How to add Materialized Views in rails 5

I want to add a Materialized Views for avg rating.

My Rate model is:

class Rating < ApplicationRecord
belongs_to :post
validates_presence_of :rate
validates_inclusion_of :rate,in: 1..5
end


My Post Model is:

class Post < ActiveRecord::Base
has_many :ratings
accepts_nested_attributes_for :ratings
end


I want to find Avg for the post Using Materialized view as

<%= "Avg.rate:#post.ratings.average(:rate).to_f.round(2)"%><br>


I created a migration for MV as

class CreateAvgMatView < ActiveRecord::Migration[5.1]
def change
execute <<-SQL
CREATE MATERIALIZED VIEW rateavg_matview AS
SELECT AVG("ratings"."rate") FROM "ratings"
SQL
end
end


I find a issue like

D013:post_app naren$ rake db:migrate
== 20180813080437 CreateAvgMatView: migrating =================================
-- execute(" CREATE MATERIALIZED VIEW rateavg_matview ASn SELECT AVG("ratings"."rate") FROM "ratings"nn")
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

SQLite3::SQLException: near "MATERIALIZED": syntax error: CREATE MATERIALIZED VIEW rateavg_matview AS
SELECT AVG("ratings"."rate") FROM "ratings"


1 Answer
1

SQLite does not support materialized views. Please read more here How can a materialized view be created in sqlite?

You'd have to switch to different RDMS (e.g. PostgreSQL) or create a "normal" table and use trigger to update it.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.