How should I approach "has many through" relationships with Single Table Inheritance (STI) in Rails 4.0

Question

I have a parent class called Place.

# Parent class for Country, City and District
class Place < ActiveRecord::Base
end

class Country < Place
  has_many :cities, foreign_key: "parent_id"
  has_many :districts, through: :cities
end

class City < Place
  belongs_to :country, foreign_key: "parent_id"
  has_many :districts, foreign_key: "parent_id"
end

class District < Place
  belongs_to :city, foreign_key: 'parent_id'
  has_one :country, through: :city
end

The schema:

create_table "places", force: true do |t|
  t.string   "name"
  t.string   "type"
  t.integer  "parent_id"
  t.datetime "created_at"
  t.datetime "updated_at"
end

add_index "places", ["parent_id"], name: "index_places_on_parent_id"
add_index "places", ["type"], name: "index_places_on_type"

The following works as expected:

@country.cities # => Returns all of the cities that belong to this country
@city.districts # => Returns all of the districts that belong to this city

But this does not work as I thought it would:

@country.districts # => Does not return all of the districts belonging to cities in this country

Can anybody explain how I should approach has many through with STIs?

Update

Here's the output SQL query from @country.districts

SELECT "places".* FROM "places" INNER JOIN "places" "cities_districts_join" ON "places"."parent_id" = "cities_districts_join"."id" WHERE "places"."type" IN ('City') AND "places"."type" IN ('District') AND "cities_districts_join"."parent_id" = ?  [["parent_id", 1]]

I think the problem is that it's using the same join table for both relations, but I'm not sure if there's a "Rails way" to change the name of the joins table (elegantly)

Answer 1

This is a challenging case for ActiveRecord. It needs to infer that columns in the self-join needed to find districts are STI instances. Apparently it's not smart enough to get this right. Since the only table is places, it's not much of a surprise that it generates this query:

SELECT "places".* FROM "places" 
INNER JOIN "places" "cities_districts_join" 
ON "places"."parent_id" = "cities_districts_join"."id" 
WHERE "places"."type" IN ('City')   <<<<< ERROR HERE
AND "places"."type" IN ('District') 
AND "cities_districts_join"."parent_id" = ?  

As you can see the type check must fail since one string can't be both City and District. All would work if the first clause in the WHERE were instead

WHERE "cities_districts_join"."type" IN ('City')  

I tried several options on the relations (thought :class_name might do it), but no joy.

You can work around this limitation with SQL. Delete the has_many ... through in the Country class and replace with

def districts
  District.find_by_sql(['SELECT * from places AS city 
                           INNER JOIN places AS district 
                         ON district.parent_id = city.id 
                         WHERE city.parent_id = ?', id])
end

Or maybe someone else will see a more elegant way. If not, you might consider posting this as an issue in Rails development. It's an interesting case.