How to combine data from two different tables into new rows to be displayed as one table in Rails application?

Let’s start with an example that you have two different tables called InternalEmail and ExternalEmail.

internal_emails
Id (integer)	sender_id (integer)	content (text)	Subject (character varying)	recipient_email (character varying)	Status (character varying)	created_at (timestamp without time zone)	updated_at (timestamp without time zone)
1	52	internal_content_1	internal_subject_1	44	accepted	2016-10-21 00:49:24.991002	2016-10-21 00:49:24.991002
2	46	internal_content_2	internal_subject_2	43	rejected	2017-10-21 00:49:24.991002	2017-10-21 00:49:24.991002

external_emails
Id (integer)	sender_id (integer)	content (text)	Subject (character varying)	receiver_id (integer)	opened (boolean)	created_at (timestamp without time zone)	updated_at (timestamp without time zone)
1	44	external_content_1	external_subject_1	42	true	2014-10-21 00:49:24.991002	2014-10-21 00:49:24.991002
2	42	external_content_2	external_subject_2	41	false	2015-10-21 00:49:24.991002	2015-10-21 00:49:24.991002
3	32	external_content_3	external_subject_3	40	true	2016-10-21 00:49:24.991002	2016-10-21 00:49:24.991002

If you want to combine these two tables to get result set as given below

internal_external_emails
Id (integer)	type (character varying)	sender_id (integer)	content (text)	subject (character varying)	receiver_id (integer)	recipient_email (character varying)	opened (boolean)	status (character varying)	created_at (timestamp without time zone)	updated_at (timestamp without time zone)
1	external	44	external_content_1	external_subject_1	42		true		2014-10-21 00:49:24.991002	2014-10-21 00:49:24.991002
2	external	42	external_content_2	external_subject_2	41		false		2015-10-21 00:49:24.991002	2015-10-21 00:49:24.991002
3	external	32	external_content_3	external_subject_3	40		true		2016-10-21 00:49:24.991002	2016-10-21 00:49:24.991002
1	internal	52	internal_content_1	internal_subject_1		abc@gmail.com		accepted	2016-10-21 00:49:24.991002	2016-10-21 00:49:24.991002
2	internal	46	internal_content_2	internal_subject_2		internal@gmail.com		rejected	2017-10-21 00:49:24.991002	2017-10-21 00:49:24.991002

you can use SQL UNION. Unlike JOIN query, UNION combines data from two tables into new rows that means each row of result set will be from Table A or from Table B. Then store this UNION query as SQL view in database to be able to use it as database table and query on it as and when required to display data on UI. To create view of UNION query, add migration in rails.

$rails generate migration CreateInternalExternalEmailView

Generated migration file should look like this

20180220065644_create_internal_external_email_view.rb
class CreateLightboxTeamLightboxView < ActiveRecord::Migration
def up
execute <<-SQL
CREATE VIEW internal_external_emails AS
SELECT id, 'external' AS type, sender_id, content, subject, receiver_id, NULL AS  recipient_email, opened, NULL AS status, created_at, updated_at
	FROM external_emails
	UNION ALL
SELECT id, 'internal' AS type, sender_id, content, subject, NULL AS receiver_id,       recipient_email, NULL AS opened, status, created_at, updated_at
FROM internal_emails
SQL
end

def down
execute "DROP VIEW internal_external_emails"
end
end

In above migration file, I have used NULL AS for some columns because there is rule of UNION query that it must contain same number of columns with same datatype.

Above migration will create SQL view internal_external_emails in database.

You can generate model InternalExternalEmail to mention necessary associations in it.

internal_external_email.rb model will look like this

class InternalExternalEmail < ActiveRecord::Base
self.primary_key = 'id'
belongs_to :sender, class_name: :User
belongs_to :receiver, class_name: :User
end

Source: http://www.cryptextechnologies.com/blogs/combine-different-tables-into-new-rows-displayed-one-table