Runtime error with many-to-many collection of polymorphic objects

Our application has a hierarchy containing items for sale: BaseItem, Item, PackItem. BaseItem is the common base class. Item is a 'normal' item, and PackItem is a collection of items sold as one.

There is also a class called Collection. A Collection may contain many items, and an item may belong to many collections.

Modeling this in SQLAlchemy causes the following runtime 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_d86.child_name, anon_d86.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_d86

It appears that SQLAlchemy assumes the target class of the many-to-many relationship contains the same fields as the join table.

The following is a potential fix for your evaluation. It is a slightly modified version of the _determine_remote_side function in sqlalchemy.orm.properties.PropertyLoader. For many-to-many relationships, it does not add the join table's foreign keys to the remote_side collection. This change appears to work in our brief initial testing.

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

Comments (1)