Fancy MySQL footwork


#1

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.