How to use group by in symfony repository

Question

I have this code in DayRepository.php :

public function findAllFromThisUser($user)
    {
        $query = $this->getEntityManager()
            ->createQuery(
                'SELECT d FROM AppBundle:Day d
                WHERE d.user = :user
                ORDER BY d.dayOfWeek ASC'
            )->setParameter('user', $user);
        try{
            return $query->getResult();
        } catch (\Doctrine\ORM\NoResultException $e){
            return null;
        }

    }

In the controller DayController.php, I have this code:

/**
 * @Route("/days/list", name="days_list_all")
 */
public function listAllAction()
{
    $user = $this->container->get('security.token_storage')->getToken()->getUser();

    $days = $this->getDoctrine()
        ->getRepository('AppBundle:Day')
        ->findAllFromThisUser($user);

    //$user = $job->getUser();

    return $this->render('day/listAll.html.twig', ['days' => $days]);
}

The output of {{ dump(days) }} in day/listAll.html.twig is:

array:3 [▼
  0 => Day {#699 ▼
    -id: 11
    -dayOfWeek: "0"
    -lessonTime: DateTime {#716 ▶}
    -user: User {#486 ▶}
    -job: Job {#640 ▶}
    -client: Client {#659 ▶}
  }
  1 => Day {#657 ▼
    -id: 13
    -dayOfWeek: "0"
    -lessonTime: DateTime {#658 ▶}
    -user: User {#486 ▶}
    -job: Job {#640 ▶ …2}
    -client: Client {#659 ▶ …2}
  }
  2 => Day {#655 ▼
    -id: 12
    -dayOfWeek: "4"
    -lessonTime: DateTime {#656 ▶}
    -user: User {#486 ▶}
    -job: Job {#640 ▶ …2}
    -client: Client {#659 ▶ …2}
  }
]

What I really need is to group the results so that all the results that have the dayOfWeek as 0 will be grouped together? I need to group the results according to the dayOfWeek property. I have tried to use GROUP BY d.dayOfWeek in the query but I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'taskMaestro.d0_.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Thanks for your time.

Answer 1

I will try to provide solution and explanation, that may help you.

Let's say you have table structure like this:

And you want to get all records grouped by dayOfWeek with list of lectors, who will conduct lectures on this day (separated by comma, respectively).

You may come up with something like this:

SELECT `dayOfWeek`, GROUP_CONCAT(`lector`) AS `dayLectors` FROM `day` GROUP BY `dayOfWeek`

And the result will be:

Also, if you want to get list of ids of fetched records, you may write this:

SELECT `dayOfWeek`, GROUP_CONCAT(`lector`) AS `dayLectors`, GROUP_CONCAT(`id`) AS `dayIds` FROM `day` GROUP BY `dayOfWeek`

So result will be:

And, respectively, if I understood your problem right, this answer may help you.