如何在轨道中添加物化视图 5.

Question

我想添加一个物化视图以进行平均评级。

我的费率模型为：

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

我的帖子模型是：

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

我想找到帖子的平均使用物化视图作为

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

我为 MV 创建了一个迁移，作为

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

我发现一个问题，例如

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"

Answer 1

SQLite不支持物化视图。请在此处阅读更多内容 如何在 sqlite 中创建物化视图？

您必须切换到不同的RDMS(例如PostgreSQL(或创建一个"普通"表并使用触发器来更新它。