如何在Symfony存储库中使用分组方式 [英] How to use group by in symfony repository

问题描述

我在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;
        }

    }

在控制器DayController.php中，我有以下代码:

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]);
}

day/listAll.html.twig中{{ dump(days) }}的输出为:

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}
  }
]

我真正需要的是将结果分组，以便将具有dayOfWeek作为0的所有结果分组到一起?我需要根据dayOfWeek属性对结果进行分组.我尝试在查询中使用GROUP BY d.dayOfWeek，但出现此错误:

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

感谢您的时间.

推荐答案

我将尝试提供解决方案和解释，可能会对您有所帮助.

假设您具有这样的表结构:

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

Let's say you have table structure like this:

您想获得按dayOfWeek分组的所有记录以及讲师列表，讲师将在这一天进行演讲(分别以逗号分隔).

您可能会想到以下内容:

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:

此外，如果要获取获取的记录的ID列表，则可以这样写:

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.

这篇关于如何在Symfony存储库中使用分组方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！