Vanilla Web Architect

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column?

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause
 is not in GROUP BY 
clause and contains nonaggregated column 'sgx.r0_.distance' which is not functionally dependent on 
columns in GROUP BY 
clause; this is incompatible with sql_mode=only_full_group_by"

class: "Doctrine\DBAL\Exception\DriverException"
detail: "An exception occurred while executing 'SELECT AVG(r0_.distance) AS sclr_0, AS id_1, AS name_2, AS country_3, AS company_4, t1_.created_at AS created_at_5, t1_.updated_at AS updated_at_6, 
AS id_7, m2_.is_leader AS is_leader_8, AS sex_9, AS country_10, m2_.first_login_at AS first_login_at_11, 
m2_.created_at AS created_at_12, m2_.updated_at AS updated_at_13, m2_.team_id AS team_id_14, m2_.user_id AS user_id_15
 FROM team t1_ INNER JOIN member m2_ ON = m2_.team_id INNER JOIN run r0_ ON = r0_.member_id WHERE IS NOT NULL GROUP BY ORDER BY r0_.distance DESC LIMIT 100':↵↵
status: 500

/** @var User $user */
        $user                  = $this->getUser();
        $type                  = $request->get('type');
        $query                 = $manager->createQueryBuilder();
        $memberTableRef        = 'm';
        $teamTableRef          = 't';
        $isMemberDefaultSelect = $type === self::LEADERBOARD_TYPE_MALE ||
                                 $type === self::LEADERBOARD_TYPE_BENEFICIARY ||
                                 $type === self::LEADERBOARD_TYPE_FEMALE ||
                                 $type === self::LEADERBOARD_TYPE_CHIEF;
                $isMemberDefaultSelect ? $memberTableRef : $teamTableRef,
                sprintf('%s as distance', $query->expr()->avg('r.distance'))
                  $isMemberDefaultSelect ? Member::class : Team::class,
                  $isMemberDefaultSelect ? $memberTableRef : $teamTableRef

        if ($type === self::LEADERBOARD_TYPE_BENEFICIARY &&
            !$guestRepository->findOneBy(['id' => $user->getGuestId()])->list()
        ) {
            return $this->apiResponse();

        switch ($type) {
            case $type === self::LEADERBOARD_TYPE_MALE || $type === self::LEADERBOARD_TYPE_FEMALE:
                $query->andWhere($query->expr()->eq(sprintf('', $memberTableRef), ':sex'))
                          $type === Member::USER_SEX_FEMALE ? Member::USER_SEX_FEMALE : Member::USER_SEX_MALE
            case self::LEADERBOARD_TYPE_CHIEF:
                $query->andWhere($query->expr()->eq(sprintf('%s.isLeader', $memberTableRef), true));
            case self::LEADERBOARD_TYPE_COMPANY:
                $query->andWhere($query->expr()->isNotNull(sprintf('', $teamTableRef)))
                      ->join(sprintf('%s.members', $teamTableRef), $memberTableRef)
            case self::LEADERBOARD_TYPE_TEAM:
                $query->join(sprintf('%s.members', $teamTableRef), $memberTableRef);
            case self::LEADERBOARD_TYPE_BENEFICIARY:
                $query->join(sprintf('%s.user', $memberTableRef), 'u')
                      ->join(sprintf('%s.guest', 'u'), 'g')
                      ->andWhere($query->expr()->isNotNull(sprintf('%s.list', 'g')))
                throw new \RuntimeException(sprintf('LeaderBoard type is not supported %s', $type));
        $query->join(sprintf('%s.runs', $memberTableRef), 'r');
        $query->addGroupBy(sprintf('', $memberTableRef));
        $query->addOrderBy('r.distance', 'DESC');

        return $this->apiResponse($query->getQuery()->getResult());
  • Вопрос задан
  • 59 просмотров
Пригласить эксперта
Ответы на вопрос 1
Задайте алиас и отсортируйте по нему.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
22 окт. 2020, в 13:19
1000 руб./за проект
22 окт. 2020, в 13:10
20000 руб./за проект
22 окт. 2020, в 13:08
80000 руб./за проект