Query analysis

gistfile1.txt

-- The main select just gets _all_ records which have a relation to taxonomies via taxable_taxonomies
select d.id, d.name, tt.taxable_type, tt.taxonomy_id from domains as d left outer join taxable_taxonomies as tt on tt.taxable_id = d.id and tt.taxable_type = 'Domain' left outer join taxonomies as t on t.id = tt.taxonomy_id and t.type = 'Location';

 id |        name        | taxable_type | taxonomy_id 
----+--------------------+--------------+-------------
  2 | four.example.com   | Domain       |           1
  1 | three.example.com  | Domain       |           1
 11 | one.example.com    | Domain       |          13
 11 | one.example.com    | Domain       |          12
 12 | two.example.com    | Domain       |          14
 11 | one.example.com    | Domain       |           2
 11 | one.example.com    | Domain       |           1
(7 rows)

-- The sub select wants to get now all records that have a specific taxonomy_id
select d.id from domains as d  inner join taxable_taxonomies tt on tt.taxable_id = d.id inner join taxonomies as t on tt.taxonomy_id = t.id where t.id = '3'; 
 id 
----
 12
 11
  2
  1
(4 rows)

-- But on a closer look it fails horribly
foreman=> select d.id, tt.taxable_type from domains as d  inner join taxable_taxonomies tt on tt.taxable_id = d.id inner join taxonomies as t on tt.taxonomy_id = t.id where t.id = '3'; 
 id | taxable_type 
----+--------------
 12 | Template
 11 | Template
  2 | Template
  1 | Template
(4 rows)