References among objects using multi-table inheritance

[Former user]

(original reporter: evan) Seems that when using multiple table inheritance, if one inheriting table has a foreign key referencing another inherited table and a relationship mapped on that column, and you try to assign to that column, it'll trip up when figuring out the primary key. (That, or I'm screwing something up.)

Test case attached

[Former user]

(original author: evan) Test case

[Mike Bayer]

well lets think about this. SA, when given two tables that it knows are joined together, which is the case when you tell it "table B inherits table A", or you tell it "table A references a collection in table B", by default wants to figure out how those two tables are related. the best it can do is to just locate all foreign keys between the two tables, AND them together, and thats the join.

in the case of inheritance, i.e. mapper(someclass, TableB, inherits=someotherclass_on_TableA), that mapper now has a "local" table of TableB and a "mapped" table of TableA.join(TableB).

A relationship such as "relation(somethirdclass_on_TableC)" on this mapper will be calculated using just TableB and TableC to figure out the join; the fact that youre sticking the relationship on the TableB mapper and not its parent TableA mapper indicates that TableA is not involved in the desired join.

However, it cant make any assumptions about TableC. In this case, TableC is actually TableA.join(TableC). so upon locating all the foreign keys, its coming up with:

managers.person_id = engineers.manager_id AND people.person_id = managers.person_id

Anyway, you dont have to worry too much about the details of the above condition. When you see something like this, i.e. a slightly less simple relationship between the tables, and the mappers are not coming up with the right result, the next course of action is to just set up explicit join conditions. heres both of them (in fact you only need the one on the 'relation'):

    person_mapper = mapper(Person, people)
    mapper(Engineer, engineers, inherits=person_mapper, inherit_condition=engineers.c.person_id==people.c.person_id)
    mapper(Manager, managers, inherits=person_mapper,properties=dict(
        minions=relation(Engineer, primaryjoin=managers.c.person_id==engineers.c.manager_id),
    ))

and then youll get the right results.

[Former user]

(original author: evan) Oops... completely missed inherit_condition while reading the docs, and it was right below where I linked, too; sorry about that. Thanks for the explanation.