I'm trying to generate a category path using a closure table. But I'm having a problem converting my query into DQL as doctrine doesn't support sub-queries, as far as I know. Is there a way to do it or any other workaround?
category table:-
id name slug
1 Category A category-a
2 Category B category-b
3 Category C category-c
category_closure table:-
ancestor_id descendant_id path_length
4 4 0
4 44 1
4 53 2
44 44 0
44 53 1
53 53 0
Desired result:-
id name path
3 Category C category-a/category-b/category-c
SQL executed:-
SELECT c.id, c.name, tmp.path
FROM category c
INNER JOIN (
SELECT a.descendant_id, group_concat( c1.slug
ORDER BY a.path_length DESC
SEPARATOR '/' ) AS path
FROM category c1
JOIN category_closure a ON c1.id = a.ancestor_id
WHERE a.descendant_id = 3
) tmp ON c.id = tmp.descendant_id
My doctrine associations are as follows:-
AppBundle\Entity\Category:
type: entity
table: category
repositoryClass: AppBundle\Repository\CategoryRepository
oneToMany:
closure:
targetEntity: CategoryClosure
mappedBy: category
AppBundle\Entity\CategoryClosure:
type: entity
table: category_closure
manyToOne:
category:
targetEntity: Category
inversedBy: closure
joinColumn:
name: descendant_id
referencedColumnName: id
Any help is greatly appreciated. Thanks
You can use DoctrineExtensions -> tree to build closure and take advantage of repository methods. https://github.com/Atlantic18/DoctrineExtensions/blob/master/doc/tree.md#repository-methods. I encourage you also to take a look at the source.
Other sollution is to use doctrine native query. Another sollution is to operate on pure connection on entity manager object.