.with_polymorphic() should join tables in the correct order and add inherited mappers which don't have a polymorphic_identity attribute (multiple inheritance)

[Former user]

Hi,

Imagine the following test-case:

 A
 | \
 B  C
 |
 D

so A is the "root" mapper, B and C inherit from A and D inherits from B (B is a kind of "virtual" class/table), in SQLAlchemy I have something like (not sure for mapper_B):

mapper_A = mapper(A, table_a, polymorphic_on=table_a.c.type_id)
mapper_C = mapper(C, table_c, inherits=A, polymorphic_identity='c')
mapper_B = mapper(B, table_b, inherits=A)
mapper_D = mapper(D, table_d, inherits=B, polymorphic_identity='d')

• A.query.with_polymorphic(D) works as expected
• A.query.with_polymorphic(B) doesn't work because the tables are joined in the wrong order (ProgrammingError: (ProgrammingError) missing FROM-clause entry for table b). I think SQLAlchemy should detect that.
• A.query.with_polymorphic(D) doesn't work because of a missing table (ProgrammingError: (ProgrammingError) missing FROM-clause entry for table b).

It would be fantastic if we could do something like: A.query.with_polymorphic(E, F, G, ...) regardless of the order of tables/inheritance ... Now I'm using .iterate_to_root() to bypass that, but it's a little "hackish" because I have to do it for each mapped object .. and merge the whole thing at the end :(

Thanks, Julien

[Mike Bayer]

I agree with_polymorphic() should generate valid joins in the order of inheritance resolution, and this is not hard to implement (and this is also a bug, once I see it in action). I would really appreciate if you could turn your A, B, C, D problem into a working test script (use sqlite and minimal columns) and attach it here, I can implement a few lines in mapper that will set it up and your script will move into a unit test.

[Former user]

Hi zzzeek,

Thanks for the quick reply ..! I'm busy to write a test-case but, as I don't know SQLAlchemy internals very well (nor Nose), I wondered what's the best way to compare two join statements ? I'm using something like:

assert str(A.query.with_polymorphic(B).statement.froms0) == str(table_A.outerjoin(table_B))

.. which works, but there should be a better way to do it .. ?

Julien

[Former user]

FYI, I use the following code to resolve those issues:

polymorphic_map = orm.class_mapper(A).polymorphic_map
return list(sautil.OrderedSet(itertools.chain(*[reversed(list(polymorphic_map[ct.id](reversed(list(polymorphic_map[ct.id).iterate_to_root())) for ct in src.polymorphic_children])))

Some explanations:

• src.polymorphic_children contains the classes that I want to use with A.query.with_polymorphic()
• iterate_to_root() return the dependency list on the wrong order (ex: D -> B -> A), so I reversed() it
• I have to keep the remove the duplicates (so tables aren't joined more than one time), and keep ordering .. so I used util.OrderedSet()

[Mike Bayer]

b99480aac23fdd4e0ef3633703ca5b32ea49805a

[Mike Bayer]

Removing milestone: 0.8.0b1 (automated comment)