When I try joining the subquery in the following function of a Sonata Admin Panel Class
protected function configureQuery(ProxyQueryInterface $query): ProxyQueryInterface
I always get the following error:
[Semantical Error] line 0, col 44 near 'JOIN (SELECT': Error: Subquery is not supported here
First i create my subquery:
$sub = $qb->getEntityManager()->createQueryBuilder();
$sub->select(array('c', 'count(c.user)'))
->from('App\Entity\UserComment', 'c')
->groupBy('c.user');
Then I try to join it (I also tried to join with $sub->getQuery->getSQL() but same error):
$qb->leftJoin(sprintf('(%s)', $sub->getDQL()), 'counter', Join::WITH,
$sub->getRootAliases()[0].'.user = counter.user')
->setParameter('isReported', true);
In my opinion this would give back valid dql:
SELECT o FROM App\Entity\UserComment o LEFT JOIN (SELECT c, count(c.user) FROM App\Entity\UserComment c GROUP BY c.user) counter WITH c.user = counter.user
Is there a workaround for this problem? The exact description of what I want to do is the following stackoverflow question: Symfony Sonata Admin Panel order by occurrence count (doctrin query builder)