Add select COUNT events in common between the current user and the others in DQL query

Question

I have this DQL query in Symfony 4 project, it does select all users who are registered in all the same events as current user

$query = $this->_em->createQuery(
    "select u as user, er.dateRegistration from App:EventRegistration er 
     left join App:User u with er.user = u.id
     where er.user != :user_id and 
     er.event in (select identity(er2.event) from App:EventRegistration er2 
     where er2.user = :user_id)")
->setParameter('user_id', $user->getId());

return $query->getResult();

Now, in the same query, I'd like to select COUNT how many times each one of the other users has registered in the same event of the current user.

I mean the number of events in common between the current user and the others.

if I add count(er.id) to the select

select u as user, er.dateRegistration, count(er.id)

I get this error :

SQLSTATE[42803]: Grouping error: 7 ERREUR: la colonne « u0_.username » doit apparaître dans la clause GROUP BY ou doit être utilisé dans une fonction d'agrégat LINE 1: SELECT u0_.username AS username_0, u0_.username_canonical AS..

Answer 1

You need to add group by;

$query = $this->_em->createQuery(
  "select u as user, count(er.id) as register_count from App:EventRegistration er 
  left join App:User u with er.user = u.id
  where er.user != :user_id and 
  er.event in (select identity(er2.event) from App:EventRegistration er2 
  where er2.user = :user_id) group by u.id")
->setParameter('user_id', $user->getId());

return $query->getResult();

It will give you register count of each user but in this case you can not expose the "er.dateRegistration" because now you are grouping counts for every user.