title: SQLAlchemy: Recursive hybrid property in a tree node
body: <p>I've the following self-referential (tree) node, and wish to filter/sort by the calculated properties <code>uuid_path</code> and <code>name_path</code>:</p>
  
  <pre><code>class Node (db.Model):
      id = db.Column (db.Integer, db.Sequence ('node_id_seq'), primary_key=True)
  
      ###########################################################################
  
      root_id = db.Column (db.Integer, db.ForeignKey (id, ondelete='CASCADE'),
          index=True)
  
      nodes = db.relationship ('Node',
          cascade='all, delete-orphan', lazy='dynamic',
          primaryjoin='Node.id==Node.root_id',
          backref=db.backref ('root', remote_side=id))
  
      ###########################################################################
  
      _uuid = db.Column (db.String (36), nullable=False, index=True, unique=True,
          name = 'uuid')
      _name = db.Column (db.Unicode (256), nullable=False, index=True,
          name = 'name')
  
      ###########################################################################
  
      @hybrid_property
      def uuid (self):
          return self._uuid
  
      @hybrid_property
      def name (self):
          return self._name
      @name.setter
      def name (self, value):
          self._name = value
  
      ###########################################################################
  
      def __init__ (self, name, root, mime=None, uuid=None):
  
          self.root = root
          self._uuid = uuid if uuid else str (uuid_random ())
          self._name = unicode (name) if name is not None else None
  
      def __repr__ (self):
  
          return u'&lt;Node@%x: %s&gt;' % (self.id if self.id else 0, self._name)
  
      ###########################################################################
  
      @hybrid_property
      def uuid_path (self):
          node, path = self, []
          while node:
  
              path.insert (0, node.uuid)
              node = node.root
  
          return os.path.sep.join (path)
  
      @hybrid_property
      def name_path (self):
          node, path = self, []
          while node:
  
              path.insert (0, node.name)
              node = node.root
  
          return os.path.sep.join (path)
  
      ###########################################################################
  </code></pre>
  
  <p>If I get a <code>Node</code> instance <code>subnode</code> and execute e.g. <code>subnode.name_path</code> then I get correctly e.g. <code>root/subnode</code>. But if I try to use <code>Node.name_path</code> (for filtering/sorting) then SQLAlchemy complains:</p>
  
  <pre><code>Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Node.root has an attribute 'name'.
  </code></pre>
  
  <p>I'm pretty sure I've to introduce something like:</p>
  
  <pre><code>class Node (db.Model):
  
      @hybrid_property
      def name_path (self):
          node, path = self, []
          while node:
  
              path.insert (0, node.name)
              node = node.root
  
          return os.path.sep.join (path)
  
      @name_path.expression
      def name_path (cls):
          ## Recursive SQL expression??
  </code></pre>
  
  <p>But I struggle to get a correct definition for <code>@name_path.expression</code> (or <code>@uuid_path.expression</code>); it should somehow instruct SQL to deliver the path from the root node down to the node in question.</p>
  
  <p>What I don't understand is why this is required, since I've told SQLAlchemy to iteratively calculate the path values. Thanks for your help.</p>