Scenic

In this tutorial, we will learn how to create a basic database view using the scenic gem.

What is Scenic?
What is Database View?
How to install scenic
Usage
Scenic view Model

What is Scenic?

Scenic is a Rails package that extends ActiveRecord::Migration with methods for creating and managing database views.

What is Database View?

A database view is a subset of a database that is created by running a query on one or more database tables. Database views are preserved as named queries in the database and can be used to save frequently used and/or complex queries.

How to install scenic

Reminder:
Make sure that your containers are up and running.

In your gemfile, add gem scenic. Currently, scenic does not support mysql so we also need to add the adapter for mysql, add gem scenic-mysql_adapter.

gem 'scenic'
gem 'scenic-mysql_adapter'

Then run bundle install.

 root@0122:/usr/src/app# bundle install
Fetching gem metadata from https://rubygems.org/...
Resolving dependencies...
...
Installing scenic 1.7.0
Fetching scenic-mysql_adapter 1.0.1
Installing scenic-mysql_adapter 1.0.1
Bundle complete! 22 Gemfile dependencies, 94 gems now installed.
Use `bundle info [gemname]` to see where a bundled gem is installed.

Then create the config file for scenic adapter.

# config/initializers/scenic.rb

require 'scenic/mysql_adapter'

Scenic.configure do |config|
  config.database = Scenic::Adapters::MySQL.new
end

Usage

To create a database view, generate a migration file using command rails generate scenic:view view_name.

 root@0122:/usr/src/app# rails generate scenic:view PostCountPreview
      create  db/views
      create  db/views/post_count_preview_v01.sql
      create  db/migrate/xxxxxxxxxxxxxx_create_post_count_preview.rb

Put the SQL statement that defines your view in the db/views/post_count_preview_v01.sql file. In our example, we will make a view that will count the comments of a post.

-- db/views/post_count_previews_v01.sql

SELECT posts.id as post_id, (SELECT count(*) FROM comments where comments.post_id = posts.id) as `count` FROM posts;

Then migrate.

 root@0122:/usr/src/app# rails db:migrate
== xxxxxxxxxxxxxx CreatePostCountPreviews: migrating =============
-- create_view(:post_count_previews)
   -> 0.0138s
== xxxxxxxxxxxxxx CreatePostCountPreviews: migrated (0.0139s) ====

Scenic view Model

A view is no different from a table when talking about ActiveRecord. We can also create a model for our database view.

Read Carefully:
We can use rails generate scenic:model model_name to generate a model with view migration file, but in this case, we wont be needing it.

Lets create our model manually. While we are at it, let’s also add our views association to post.

# app/models/post_count_preview.rb

class PostCountPreview < ApplicationRecord
  belongs_to :post

  def readonly?
    true
  end
end

# app/models/post.rb

class Post < ApplicationRecord
  # ...
  has_many :categories, through: :post_category_ships
  has_many :post_count_previews
  belongs_to :user
  # ...
end

Now try it out on console.

irb(main):001:0> Post.first.post_count_previews
  Post Load (0.5ms)  SELECT `posts`.* FROM `posts` WHERE `posts`.`deleted_at` IS NULL ORDER BY `posts`.`id` ASC LIMIT 1
  PostCountPreview Load (0.9ms)  SELECT `post_count_previews`.* FROM `post_count_previews` WHERE `post_count_previews`.`post_id` = 1
=> [#<PostCountPreview:0x00007f3330a91fb8 post_id: 1, count: 1>]
irb(main):002:0> PostCountPreview.all
  PostCountPreview Load (0.8ms)  SELECT `post_count_previews`.* FROM `post_count_previews`
=> [#<PostCountPreview:0x000055e5a3183628 post_id: 12, count: 7>, ... ]
irb(main):006:0> PostCountPreview.first.post
  PostCountPreview Load (0.6ms)  SELECT `post_count_previews`.* FROM `post_count_previews` LIMIT 1
  Post Load (0.5ms)  SELECT `posts`.* FROM `posts` WHERE `posts`.`deleted_at` IS NULL AND `posts`.`id` = 12 LIMIT 1
=> #<Post:0x000055e5a261e850 id: 12, ... >

That is all for Scenic. To know more about scenic, read Scenic View article.