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

07 Mar 2018
0 Comments
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




Leave a comment: