使用内连接进行多重选择

Question

I try to translate this simple SQL request with Doctrine but with no success:

SELECT `groups`.`departure`, COUNT(`group_users`.`group_id`) as 'teamLength' FROM `groups` INNER JOIN `group_users` WHERE `groups`.`id` = `group_users`.`group_id` AND departure BETWEEN '2017-03-01' AND '2017-03-31'

Currently, my code looks like:

public function findDatesBetween(\AppBundle\Entity\Itinerary $itinerary, \DateTime $firstDate, \DateTime $lastDate)
{
  $q = $this->createQueryBuilder('g')
    ->select('g', 'COUNT(gu.group) teamLength')
    ->innerJoin('AppBundle:GroupUser', 'gu', Join::WITH, 'g.id = gu.group')
    ->andWhere('g.itinerary = :itineraryId')
      ->setParameter('itineraryId', $itinerary->getId())
    ->andWhere('g.departure BETWEEN :firstDate AND :lastDate')
    ->setParameter('firstDate', $firstDate->format('Y-m-d'))
    ->setParameter('lastDate', $lastDate->format('Y-m-d'));

  return ($q->getQuery()->getResult());
}

If I change the select() like this : ->select('g'), it works. But I need to get COUNT(gu.group).

With the actual code, I have an exception when I try to use departure: Call to a member function getDeparture() on array

How can I fix it ? Thank you.

Answer 1

You want to get an aggregate result with your query, that means that your result will be an associative array and not an array of entities instances. Try to get your data with the getArrayResult method and access your data accordingly.

You can do a {{ dump(data) }} on your twig view to see the structure of your result.