Doctrine Orm 2 Recursive query with Native SQL

Question

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.

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}.

I have a groups table as such

| Group Id | Parent Id |
|    1     |    NULL   |
|    2     |      1    |
|    3     |      2    |

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.

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.

public function getGroupOwnershipChain($group_id){
    $rsm = new ResultSetMapping();
    $rsm->addEntityResult('Bundle:Group', 'u');
    $rsm->addFieldResult('u', 'id', 'id');
    $rsm->addFieldResult('u', 'parent', 'parent');


    $query = $this->em->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->setParameter(1, $group_id);
    return $query->getResult();
}

http://stackoverflow.com/a/24899603/460557 – Jorge Campos Apr 29 '17 at 20:35 — Jorge Campos, Apr 29 '17 at 20:35
I will take a look at that thanks – user7806384 Apr 29 '17 at 20:43 — user7806384, Apr 29 '17 at 20:43
That article basically solved it thanks boss – user7806384 Apr 29 '17 at 22:41 — user7806384, Apr 29 '17 at 22:41
I will I have to wait 2 days though – user7806384 Apr 30 '17 at 20:32 — user7806384, Apr 30 '17 at 20:32

score 2 · Accepted Answer · answered Apr 29 '17 at 22:40

public function getGroupOwnershipChain($group_id){
$rsm = new ResultSetMapping();
$rsm->addEntityResult('Bundle:Group', 'u');
$rsm->addFieldResult('u', 'id', 'id');
$rsm->addFieldResult('u', 'parent', 'parent');


$query = $this->em->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->setParameter(1, $group_id);
return $query->getResult();
}

I had to flip around the from and the where clause in the recursive anchor. It works a ok now.

Doctrine Orm 2 Recursive query with Native SQL

1 Answers1