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.

This is in MySQL 5.7 , use aggregate functions. Several questions regarding this, here is an example question, https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql — Viraj Amarasinghe, Jul 17 '17 at 04:51
Thanks @Sameera. I have studied the question and answers of that link for about three hours without applying the logic to my own problem. Will you kindly show me how to apply the logic in this situation? thanks — Josiah, Jul 17 '17 at 04:57
What is the value that you really need to get from this query ? — Viraj Amarasinghe, Jul 17 '17 at 05:09
Yeah. The dayOfWeek may be similar for several results. I want to group Sunday's jobs together, monday's jobs together etc. — Josiah, Jul 17 '17 at 05:15
you can use the selections like this SELECT d.dayOfWeek,d.lessonTime seperately without selecting id as a selection. — Viraj Amarasinghe, Jul 17 '17 at 05:31
That still gives back the same error as before: 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_.lesson_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by — Josiah, Jul 17 '17 at 05:36

score 2 · Accepted Answer · answered Jul 18 '17 at 13:07

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.

How to use group by in symfony repository

1 Answers1