title: how to establish a relationship between node and descendants at specific level with SQLAlchemy
body: <p>I have a table with hierarchical data where each row contains an <code>id</code>, <code>parent_id</code>, <code>level</code> which is the depth, and also a path enumeration <code>hierarchy_hint</code> which is a plain string with JSON-like formatting. For example:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>parent_id</th>
  <th>level</th>
  <th>hierarchy_hint</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>null</td>
  <td>'level1'</td>
  <td>'{&quot;level1&quot;: &quot;root&quot;}'</td>
  </tr>
  <tr>
  <td>2</td>
  <td>1</td>
  <td>'level2'</td>
  <td>'{&quot;level1&quot;: &quot;root&quot;, &quot;level2: &quot;shoes&quot;}'</td>
  </tr>
  <tr>
  <td>3</td>
  <td>2</td>
  <td>'level3'</td>
  <td>'{&quot;level1&quot;: &quot;root&quot;, &quot;level2: &quot;shoes&quot;, &quot;level3&quot;: &quot;athletic&quot;}'</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>(For the sake of example I used integers, but the ids are UUIDs)</p>
  <p>I'd like to use <code>hierarchy_hint</code> to get all descendants of a given <code>Node</code> at a particular <code>level</code>. The model already has a self referential relationship established to get direct descendants (<code>children</code>). The model looks like:</p>
  <pre><code>class Node(db.Model):
      id = db.Column(UUID(as_uuid=True), primary_key=True)
      parent_id = db.Column(UUID(as_uuid=True), db.ForeignKey('node.id'))
      children = db.relationship('Node')
      level = db.Column(db.String(length=4000), primary_key=False)
      hierarchy_hint = db.Column(db.String(length=200), primary_key=False)
  </code></pre>
  <p>I've tried adding to the model:</p>
  <pre><code>    level_threes = relationship(
          &quot;Node&quot;, primaryjoin=and_(
              foreign(hierarchy_hint).like(func.regexp_replace(hierarchy_hint, '}', ',%%')),
              level == 'level3'), viewonly=True)
  </code></pre>
  <p>But I get the error:</p>
  <pre><code>sqlalchemy.exc.ArgumentError: Relationship Node.level_threes could not determine any unambiguous local/remote column pairs based on join condition and remote_side arguments.  Consider using the remote() annotation to accurately mark those elements of the join condition that are on the remote side of the relationship.
  </code></pre>
  <p>I suspect that because I'm modifying the <code>hierarchy_hint</code> using <code>regexp_replace</code> I should be using a <code>secondary</code> table somehow but I'm not experienced enough with SQLAlchemy to know what exactly to do.</p>
  <p>I'm open also to alternative suggestions for how to do this using recursive queries or otherwise but I would like to be able to access the property in the same way as I access <code>children</code> to avoid refactoring elsewhere.</p>
  <p>I'm using Flask, SQLAlchemy, and PostgreSQL.</p>
  <p>In plain SQL what I'm trying to do might look like this:</p>
  <pre><code>with ancestor as (select hierarchy_hint from node where id='2')
  select node.id from node, ancestor
      where node.hierarchy_hint and node.level='level4';
  </code></pre>
  <p>Here we would expect to get all the <code>level4</code> items within the <code>shoes</code> tree but no <code>level4</code> items from any other trees nor any <code>level5</code> etc. items from <code>shoes</code>.</p>