Unorderable types when creating a child object in SQLAlchemy

Question

I'm using SQLAlchemy as my ORM in a web app. When I try to create a new object and add it as a child of some other object, I get the following exception:

Traceback (most recent call last):
File "/usr/local/lib/python3.4/dist-packages/tornado/web.py", line 1346, in _execute
    result = method(*self.path_args, **self.path_kwargs)
File "/usr/share/app/server/handlers.py", line 248, in wrapper
    return fn(self, *args, **kwargs)
File "/usr/share/app/server/crud/node.py", line 157, in post
    session.flush()
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2004, in flush
    self._flush(objects)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2122, in _flush
    transaction.rollback(_capture_exception=True)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/util/compat.py", line 182, in reraise
    raise value
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/session.py", line 2086, in _flush
    flush_context.execute()
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 149, in save_obj
    base_mapper, states, uowtransaction
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 270, in _organize_states_for_save
    states):
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 1035, in _connections_for_states
    for state in _sort_states(states):
File "/usr/local/lib/python3.4/dist-packages/sqlalchemy/orm/persistence.py", line 1057, in _sort_states
    sorted(persistent, key=lambda q: q.key[1])
TypeError: unorderable types: str() < UUID()

The primary keys are composed of two UUIDs (like multitenancy). I'm using sqlalchemy.ext.orderinglist to order the children, with an integer seq column to store the indices. The code for creating the new object is roughly:

node = Node(group_id=group_id)
hierarchy = (session.query(Hierarchy).get((hierarchy_id, group_id)))
node.hierarchy = hierarchy
hierarchy.nodes.append(node)
hierarchy.nodes.reorder()
session.flush()

For completeness, here are the relevant mappings and relationships:

class Group(Base):
    __tablename__ = 'group'
    id = Column(GUID, default=uuid.uuid4, primary_key=True)

class Hierarchy(Base):
    __tablename__ = 'hierarchy'
    id = Column(GUID, default=uuid.uuid4, primary_key=True)
    group_id = Column(
        GUID, ForeignKey('group.id'), nullable=False, primary_key=True)

class Node(Base):
    __tablename__ = 'node'
    id = Column(GUID, default=uuid.uuid4, primary_key=True)
    group_id = Column(GUID, nullable=False, primary_key=True)
    hierarchy_id = Column(GUID, nullable=False)
    parent_id = Column(GUID)
    seq = Column(Integer)

    __table_args__ = (
        ForeignKeyConstraint(
            ['parent_id', 'group_id'],
            ['node.id', 'node.group_id']
        ),
        ForeignKeyConstraint(
            ['hierarchy_id', 'group_id'],
            ['hierarchy.id', 'hierarchy.group_id']
        ),
        ForeignKeyConstraint(
            ['group_id'],
            ['group.id']
        ),
    )

    group = relationship(Group)

Group.hierarchies = relationship(
    Hierarchy, backref="group", passive_deletes=True,
    order_by='Hierarchy.title')

Hierarchy.nodes_all = relationship(
    Node, backref='hierarchy', passive_deletes=True,
    primaryjoin=and_(foreign(Node.hierarchy_id) == Hierarchy.id,
                    Node.group_id == Hierarchy.group_id))

Hierarchy.nodes = relationship(
    Node, passive_deletes=True,
    order_by=Node.seq, collection_class=ordering_list('seq'),
    primaryjoin=and_(and_(foreign(Node.hierarchy_id) == Hierarchy.id,
                        Node.group_id == Hierarchy.group_id),
                    Node.parent_id == None))

Node.parent = relationship(
    Node, backref=backref(
        'children', passive_deletes=True,
        order_by=Node.seq, collection_class=ordering_list('seq')),
    primaryjoin=and_(foreign(Node.parent_id) == remote(Node.id),
                    Node.group_id == remote(Node.group_id)))

The structure is:

• a group owns many hierarchies
• a hierarchy owns many nodes (where node.parent_id is NULL; this is a filtering primary join)
• a node owns many nodes (where node.parent_id = remote(node.parent_id))
• all nodes have a link to both their group and hierarchy, even if they are not directly owned by the hierarchy (yes, a little denormalised)

Answer 1

It turns out I was adding the object to the list of children twice. Removing the node.hierarchy = hierarchy line fixes the problem. However, that's a sub-optimal solution, because I want it to be easy to set the hierarchy of non-root nodes. The real fix is to prevent the Node.hierarchy relationship from inserting items into the Hierarchy.nodes list. The relationships should be changed to use a one-way backref:

Node.hierarchy = relationship(
    Hierarchy,
    primaryjoin=and_(foreign(Node.hierarchy_id) == remote(Hierarchy.id),
                    Node.group_id == remote(Hierarchy.group_id)))

Hierarchy.nodes = relationship(
    Node, back_populates='hierarchy', passive_deletes=True,
    order_by=Node.seq, collection_class=ordering_list('seq'),
    primaryjoin=and_(and_(foreign(Node.hierarchy_id) == Hierarchy.id,
                        Node.group_id == Hierarchy.group_id),
                    Node.parent_id == None))

This is described in the docs under Linking Relationships with Backref > One Way Backrefs. As the docs point out, this will also prevent items from being inserted into the nodes list even when Node.parent_id == None, which is somewhat unfortunate.

I also decided to get rid of the Hierarchy.nodes_all relationship, because it was just complicating matters. I'll use a special query if I need that.