我试图使用闭包表生成类别路径。但我有一个问题,将我的查询转换为DQL学说不支持子查询,据我所知。有什么办法或其他解决办法吗?
目录表:-
id name slug
1 Category A category-a
2 Category B category-b
3 Category C category-c
<<p> category_closure表/strong>: - ancestor_id descendant_id path_length
4 4 0
4 44 1
4 53 2
44 44 0
44 53 1
53 53 0
预期的结果: -
id name path
3 Category C category-a/category-b/category-c
SQL执行: -
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
我的学说联想如下:-
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
- 我的查询优化了吗?
- 如何使用学说编写这个查询?
非常感谢任何帮助。由于
您可以使用DoctrineExtensions -> tree来构建闭包并利用存储库方法。https://github.com/Atlantic18/DoctrineExtensions/blob/master/doc/tree.md repository-methods。我也鼓励你看一看源代码。
另一个解决方案是使用原则本地查询。另一种解决方案是在实体管理器对象上操作纯连接。