Error in orm for many-to-many, polymorphic relationships

[Former user]

Our application has a class hierarchy with three classes, BaseItem, Item, and PackItem. BaseItem is the common base class, Item is a concrete item, and PackItem represents a collection of items sold as a group.

The application also has a class called Collection. A Collection may contain many BaseItems, and a BaseItem may belong to many Collections.

Modeling this in SQLAlchemy gives the following error: Traceback (most recent call last): File "C:\tmp\testcase.py", line 100, in ? i = Item() File "c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg\sqlalchemy\orm\ mapper.py", line 646, in init mapper = mapper.compile() File "c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg\sqlalchemy\orm\ mapper.py", line 321, in compile self._compile_all() File "c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg\sqlalchemy\orm\ mapper.py", line 341, in _compile_all mapper._initialize_properties() File "c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg\sqlalchemy\orm\ mapper.py", line 596, in _initialize_properties prop.init(key, self) File "c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg\sqlalchemy\orm\ interfaces.py", line 60, in init self.do_init() File "c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg\sqlalchemy\orm\ properties.py", line 186, in do_init self._create_polymorphic_joins() File "c:\python24\lib\site-packages\sqlalchemy-0.3.6-py2.4.egg\sqlalchemy\orm\ properties.py", line 382, in _create_polymorphic_joins raise exceptions.AssertionError(str(self) + ": Could not find corresponding column for " + str(c) + " in selectable " + str(self.mapper.select_table)) sqlalchemy.exceptions.AssertionError: Collection.items (BaseItem): Could not fin d corresponding column for base_item_collection.collection_id in selectable SELE CT item.dummy, base_item.child_name, item.id FROM base_item JOIN item ON base_item.id = item.id UNION ALL SELECT CAST(NULL AS INTEGER) AS dummy, anon_a24a.child_name, anon_a24a.id FROM (SELECT base_item.id AS id, base_item.child_name AS child_name FROM base_item WHERE base_item.child_name = ?) AS anon_a24a

It appears the ORM assumes that for a many-to-many relationship, the foreign keys on the join table are actually fields on the two tables being joined.

The following is a potential fix for your evaluation. It is a slightly modified version of sqlalchemy.orm.properties.PropertyLoader._determine_remote_side. It does not add the join table's foreign keys to the remote_side collection.

def _determine_remote_side(self): if len(self.remote_side): return self.remote_side = util.Set()

if self.direction is sync.MANYTOONE:
    for c in self._opposite_side:
        self.remote_side.add(c)
elif self.direction is sync.ONETOMANY:
    for c in self.foreign_keys:
        self.remote_side.add(c)
elif self.direction is sync.MANYTOMANY:
    # The target table for a MANYTOMANY join does not contain
    # the foreign key fields of the join table itself.
    pass

[Former user]

Test case to reproduce the bug

[Former user]

I forgot to set the reported_by field. My email address is [e-mail].

[Mike Bayer]

the fix involves just excluding secondary columns from the check for polymorphic equivalents, as is implemented in changeset:2495. the FK columns in the secondary table are certainly part of the "remote side"...but thanks for the testcase/analysis anyway, most ppl dont brave it at all within the ORM code :).

a few things about your mapping, in particular youre going to have problems creating an explicit mapping to your secondary table (BaseItemCollection) while also naming it as a secondary table in the Collection relationship. SA is going to generate conflicting inserts/deletes as a result as it is not going to try to reconcile BaseItemCollection instances against relationships of BaseItem to Collection instances. a pattern thats in use to reconcile having an association object while also having easy access off the parent class is the associationproxy plugin described in the docs.

also the surrogate primary key in the base_item_collection table may be problematic, though it might just work. I typically define the primary key of a many-to-many table as the composite of its foreign keys.

[Mike Bayer]

oh and also, you want to use "backref" for the bi-directional relationship between BaseItem and Collection - its specifically needed for many-to-many relationships as a signal that the two sides of the relation should "communicate" with each other who is responsible for inserting/deleting particular assocation rows in the many-to-many table.