What is the equivalent Doctrine SQL query for getting a recursive query on item's categories

Question

In this post, DRapp provided an SQL query for solving the problem proposed by Ali. In practice, Ali asked how to write a query capable of returning all the rows from the table Category related to a row in the Post table, in order to do what we can see in all the catalog-based websites (eBay, Amazon, etc.).

This stated, how the SQL query proposed in the DRapp's solution can be written with Doctrine2?

Please, avoid using QueryBuilder.

PS: I use MySQL.

Answer 1

I believe that it is not possible to write such a query like the one mentioned in the question by using DQL or the QueryBuilder directly. However, one possibility is to use Doctrine2 Native Query, which let the use to execute a raw SQL.

For sake of completeness, here follows the query by DRapp.

select
      P.ID,
      P.Post_Title,
      P.Category_ID,
      C.Category_Name as FirstCat,
      C.Parent,
      COALESCE( C2.Category_Name, ' ' ) as ParentCategory
   from
      Posts P
         JOIN Categories C
            on P.Category_ID = C.Category_ID
            LEFT JOIN Categories C2
               on C.Parent = C2.Category_ID
   where
      AnyFiltering