title: Doctrine Orm 2 Recursive query with Native SQL
body: <p>I'm using the symfony framework and that uses Doctrine Orm 2 and DQL to talk to the Database. However, DQL does not support recursive queries. It does give you an option to use Native SQL along with result set mappings to create advanced queries that DQL does not support.</p>
  
  <p>Now to the task at hand. I'm trying to write a function that when given a group Id I want to return an array of the of the group's parent Id and the parent's parent Id until we reach the root group with a parent Id equal to NULL. For example passing 3 to the function would return {3,2,1}, passing in 2 would return {2,1}, and passing in 1 would return {1}.</p>
  
  <p>I have a groups table as such</p>
  
  <pre><code>| Group Id | Parent Id |
  |    1     |    NULL   |
  |    2     |      1    |
  |    3     |      2    |
  </code></pre>
  
  <p>My current solution to this problem is just returning the parent of a group Id and querying the database again and again until I reach the root Group. However, if I could accomplish this all in one query, that would be a lot less overhead for the server/DB to take care of. I'm not very familiar with recursive queries either, but I will update this post with my progress as I learn more about them.</p>
  
  <p>update 1: This is the code I have so far, I also had to update to maria db 10.2 as that was when recursive cte support was implemented. I am currently dealing with a syntax error in the where clause.</p>
  
  <pre><code>public function getGroupOwnershipChain($group_id){
      $rsm = new ResultSetMapping();
      $rsm-&gt;addEntityResult('Bundle:Group', 'u');
      $rsm-&gt;addFieldResult('u', 'id', 'id');
      $rsm-&gt;addFieldResult('u', 'parent', 'parent');
  
  
      $query = $this-&gt;em-&gt;createNativeQuery('
     with recursive cte as (
              SELECT u.id, u.parent
              WHERE u.id = ?
              FROM   group
  
              UNION  ALL
  
              SELECT e.id, e.parent
              FROM   cte c
              JOIN   group e ON e.id = c.parent
       )
      SELECT *
      FROM   cte;', $rsm);
  
      $query-&gt;setParameter(1, $group_id);
      return $query-&gt;getResult();
  }
  </code></pre>