1

In my sonata comments table it is possible to see all comments with its authors. I'd like to order them after :

user_id (Author) | comment | 
Erik             | 1       |
Lisa             | 2       |
Jose             | 3       |
Erik             | 4       |
Lisa             | 5       |
Erik             | 6       |

output should be:

user_id (Author) | comment | 
Erik             | 1       |
Erik             | 4       |
Erik             | 6       |
Lisa             | 2       |
Lisa             | 5       |
Jose             | 3       |

I was able to solve it in SQL:

SELECT
  user_comment.*, counter.count
FROM
  user_comment
LEFT JOIN (
  SELECT
    user_comment.user_id, count(user_comment.user_id) as count
  FROM
    user_comment  
  GROUP BY
    user_comment.user_id
) counter ON counter.user_id = user_comment.user_id 
ORDER BY counter.count  DESC, username ASC

I can't find a way get this solution with the query builder, because it's not supporting subquery that way and it firsts executes the selects, then from, then join and so on.

For instance that won't work because the order of the qb is fixed:

$qb->select('counter')
        ->addSelect('count(counter.user) as count')
        ->from('App\Entity\UserComment', 'counter')
        ->groupBy('counter.user')
        ->leftJoin('App\Entity\UserComment', 'cm', Join::WITH,$rootAlias.'.user = cm.user')
        ->orderBy('count', 'DESC');
wolfianer
  • 41
  • 3
  • the query builder also supports subqueries, the syntax is annoying though and essentially requires you to build one query, use `->getDQL()` on it to get the subquery to insert into a `->leftJoin()` call ... have a look at https://stackoverflow.com/questions/6637506/doing-a-where-in-subquery-in-doctrine-2 for example (it's applied on the WHERE ... IN (subquery)) in contrast to here, where it's LEFT JOIN (subquery)) – Jakumi May 30 '20 at 14:54
  • @Jakumi Thx, but `$qb->leftJoin(sprintf('(%s)', $sub->getDQL()), 'counter', Join::WITH, $sub->getRootAliases()[0].'user = counter.user');` is not working. tried it in multiple variations. The posted command gives the right DQL in my opinion, but I always get: `[Semantical Error] line 0, col 44 near 'JOIN (SELECT': Error: Subquery is not supported here` is there a workaround, or have I understood you – wolfianer Jun 01 '20 at 16:35
  • please post the *complete code sample* you have as part of the question. even if it's not working right now. – Jakumi Jun 01 '20 at 20:26
  • @Jakumi I am in a sonata admin class in the function `protected function configureQuery(ProxyQueryInterface $query): ProxyQueryInterface` I get the standard query with: `$query = parent::configureQuery($query); $qb = $query->getQueryBuilder();` The sub query I created: `$sub = $qb->getEntityManager()->createQueryBuilder(); $sub->select('c') ->addSelect( 'count(c.user)') ->from('App\Entity\UserComment', 'c') ->groupBy('c.user') ;` adds a column for the count for every user. problem: i have to join now this table, with the origin table to get for.... – wolfianer Jun 02 '20 at 07:41
  • ...every comment the respective count: `$qb->leftJoin(sprintf('(%s)', $sub->getDQL()), 'counter', Join::WITH, $qb->getRootAliases()[0].'.user = counter.user');` this gives me following dql witch seems right for me: `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 o.user = counter.user` I haven't continued, due to the symfony exception: `[Semantical Error] line 0, col 44 near 'JOIN (SELECT': Error: Subquery is not supported here` – wolfianer Jun 02 '20 at 07:48
  • I am debuggin with: `var_dump($qb->getQuery()->getSQL()); //this triggers the exception (also if i am just returning the query)` and: `var_dump($qb->getQuery()->getDQL());` – wolfianer Jun 02 '20 at 07:56

0 Answers0