DQL select all users and count the number of reservations of each

Question

I'm trying to select all my users and, for each user, count the number of reservations made and incoming. For the moment I have this

$qb = $this->createQueryBuilder('user');
return $qb->select('user')
  ->leftJoin('user.reservations', 'reservations')
  ->leftJoin('reservations.marketDate', 'market_date')
  ->addSelect('COUNT(nb_reservations FROM reservations WHERE market_date.date >= CURRENT_DATE())')
  ->orderBy('user.name')
  ->groupBy('user.id')
  ->getQuery()
  ->getResult();

But I have this error

[Semantical Error] line 0, col 59 near 'market_date >=': Error: Class 'market_date' is not defined.

Please help me

Answer 1

Try this:

$qb = $this->createQueryBuilder('user');
return $qb->select('user, count(reservations) as count')
  ->leftJoin('user.reservations', 'reservations')
  ->leftJoin('reservations.marketDate', 'market_date')
  ->where('market_date.date >= CURRENT_DATE()')
  ->orderBy('user.name')
  ->groupBy('user.id')
  ->getQuery()
  ->getResult();