Forum: Ruby on Rails Fancy MySQL footwork

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Fd2fc09030069111e4715dfa4e684300?d=identicon&s=25 Justin Skolnick (justinskolnick)
on 2006-04-05 19:38
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.
This topic is locked and can not be replied to.