How to combine data from two different tables into new rows to be displayed as one table in Rails
- Mischelle L
- Mar 8, 2018
- 1 min read
Let’s start with an example that you have two different tables called InternalEmail and ExternalEmail.
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
Comentários