A practical Example of Database View in Ruby on Rails

To learn how to create a database view in Ruby on Rails, start by reading the following article:

How to create Database Views in Ruby on Rails?

1. Concatenation methods

Combine ActiveRecord result by the plus(+) operator (or any similar methods):

photo_posts = PhotoPost.all
text_posts = TextPost.all
result = photo_posts + text_posts

There are some drawbacks with this solution:

• Performance issue (Ram based process)
• Really hard to sort and pagination

2. Polymorphic

In this solution, we will create a model (table) with a polymorphic association to PhotoPost and TextPost:

class Post < ApplicationRecord
  belongs_to :postable, polymorphic: true
end

and associate your models (PhotoPost and TextPost) to Post model:

class PhotoPost < ApplicationRecord
  has_one :posts, as: :postable
end
class TextPost < ApplicationRecord
  has_one :posts, as: :postable
end

Note that you need to create Post object manually after creating every PhotoPost or TextPost.

This technique is called multi-table inheritance. There are some gems to simulate MTI for ActiveRecord models (e.g. active_record-acts_as).

Drawbacks with this solution:

• N+1 query problem, Because you can’t join Post model with PhotoPost & TextPost
• Additional model (Post)
• Hard to sort by attributes which are not a member of Post model

3. Database View

Describing Database View is beyond the scope of this article. We just will know how we should use View in Ruby on Rails.

Create a migration to create Database View:

class CreatePostView < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE VIEW posts AS
      SELECT
        id AS indentifier,
        title,
        updated_at,
        created_at
      FROM
        photo_posts
      UNION ALL
      SELECT
        id AS identifier,
        title,
        updated_at,
        created_at
      FROM
        text_posts
    SQL
  end
  def down
    execute <<-SQL
      DROP VIEW IF EXISTS posts
    end
  end
end

Rails behaves in the same way with tables & views and you only need to create Post model like the other ones:

class Post < ApplicationRecord
end

Now, All SELECT kind of queries are available by Post model, e.g. Post.first, Post.all, Post.where() .

Wrapping up

In this I’ve listed the advantages and disadvantages of each solution:

Concatenation methods

✓ Easy to read and write
✗ Hard to sort
✗ Hard to paginate
✗ Performance issue (Ram based process)

Polymorphic

✓ Ability to generalize attributes, associations, and methods
✓ Easy pagination
✓ Transparency between the models which have is-a relationship together
✗ Performance issue (N+1 query problem)
✗ Additional model (table)
✗ Hard to sort by attributes which are not a member of Post model

Database View

✓ High performance
✗ Hard to maintain