title: SqlAlchemy recursive query
body: <p>I have a Post Model that has 3 fields</p>
  <ul>
  <li>id</li>
  <li>parent_id</li>
  <li>note</li>
  </ul>
  <p>is it possible to retrieve result like this with sqlalchemy query?</p>
  <pre><code>{ id: 1, parent_id: 0, note: 'Note1', children: [
      {id: 5, parent_id: 1, note: 'Reply1', children: [
          {id: 8, parent_id: 5, note: 'Reply reply', children: []}
         ]
      },
      {id: 6, parent_id: 1, note: 'Reply2', children: []},
      {id: 7, parent_id: 1, note: 'Reply3', children: []} 
    ]
  },
    id: 2, parent_id: 0, note: 'Note2', children: [{}]
    id: 3, parent_id: 0, note: 'Note3', children: [{}]
  }
  </code></pre>
  <p>i tried this sqlalchemy</p>
  <pre><code>id= 1 
  hierarchy = db.session.query(Post).filter(Post.id == id).cte(name=&quot;hierarchy&quot;, recursive=True)
  
  parent = aliased(hierarchy, name=&quot;p&quot;)
  children = aliased(Post, name=&quot;c&quot;)
  
  hierarchy = hierarchy.union_all(db.session.query(children).filter(children.parent_id == parent.c.id))
  
  result = db.session.query(Post).select_entity_from(hierarchy).all()
  </code></pre>
  <p>which gives me rows like this (probably because I used union_all?)</p>
  <pre><code>results = [
  {id: 1, parent_id: 0, note: 'Note1',
  {id: 5, parent_id: 1, note: 'Reply1',
  {id: 8, parent_id: 5, note: 'Reply reply'}
  ]
  </code></pre>
  <p>Is only option I have is to iterate over results recursively and make it manually? (to have nested <code>children</code> column with its children as list)</p>