Sometimes you need to combine two table’s query together. For example, you have two kinds of posts stored in two separate tables. Assume you want to list all posts ordered by creation-date with pagination.

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

model with & 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() and….

But what is the problem with this solution? View will not be added to schema.rb file so if you use rake db:schema:load to create your database schema, View won’t be generated!

To resolve this issue you need to switch your schema to SQL format. Add following line to your application.rb or proper environment file:

config.active_record.schema_format = :sql

and then you need to run rake db:structure:dump to generate structure.sql file based on the existing database. structure.sql contains SQL commands to recreate your database. To load structure.sql file to your database via rake command, you just need to run rake db:structure:load (instead of rake db:schema:load ). Your structure.sql file will be something like this:

CREATE TABLE “schema_migrations” (“version” varchar NOT NULL PRIMARY KEY); CREATE TABLE “ar_internal_metadata” (“key” varchar NOT NULL PRIMARY KEY, “value” varchar, “created_at” datetime NOT NULL, “updated_at” datetime NOT NULL); CREATE TABLE “photo_posts” (“id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, “title” varchar, “created_at” datetime NOT NULL, “updated_at” datetime NOT NULL); CREATE TABLE “text_posts” (“id” INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, “title” varchar, “created_at” datetime NOT NULL, “updated_at” datetime NOT NULL); CREATE VIEW posts AS

SELECT

id AS identifier,

title,

updated_at,

created_at

FROM

photo_posts

UNION

SELECT

id AS identifier,

title,

updated_at,

created_at

FROM

text_posts; INSERT INTO schema_migrations (version) VALUES (‘20180508153851’), (‘20180508153917’), (‘20180508154451’);

Yeah better but still boring! As always there is a gem to save your life. By scenic you don’t need to switch your schema format to SQL. It has an acceptable community, follow its GitHub page to know how to use it.