Database structure as follows:
t1 -> t2
-> t3 -> t4
select * from t1;
±—±------±------+
| id | t2_id | t3_id |
±—±------±------+
| 1 | 1 | null |
| 2 | null | 1 |
| 3 | null | 2 |
±—±------±------+
select * from t2;
±—±------+
| id | title |
±—±------+
| 1 | “hat” |
±—±------+
select * from t3;
±—±------+
| id | t4_id |
±—±------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
±—±------+
select * from t4;
±—±------+
| id | title |
±—±------+
| 1 | “cat” |
| 2 | “rat” |
±—±------+
with corresponding Rails models:
t1
belongs_to :t2
belongs_to :t3
t2
has_many :t1
t3
has_many :t1
belongs_to :t4
t4
has_many :t3
The structure works as I need it; I can retrieve t2.title and t4.title
without trouble, in separate queries. But I also want to retrieve
t2.title and t4.title in the same query, or as a second choice combine
the two resulting queries, where t4’s results are also distinct. My
dream query spits out the following table:
±—±------±------+
| id | table | title |
±—±------±------+
| 1 | “t2” | “hat” |
| 2 | “t4” | “cat” |
| 3 | “t4” | “rat” |
±—±------±------+
Where id is t1.id,
table is either t2 or t4, and
title is either t2.title or t4.title
Am I delusional? Or teach me this magic.