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

Question

Doctrine ORM

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, t1_.id AS id_1, t1_.name AS name_2, 
t1_.country AS country_3, t1_.company AS company_4, t1_.created_at AS created_at_5, t1_.updated_at AS updated_at_6, m2_.id 
AS id_7, m2_.is_leader AS is_leader_8, m2_.sex AS sex_9, m2_.country 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 t1_.id = m2_.team_id INNER JOIN run r0_ ON m2_.id = r0_.member_id WHERE 
t1_.company IS NOT NULL GROUP BY m2_.id 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;
        $query->addSelect(
            [
                $isMemberDefaultSelect ? $memberTableRef : $teamTableRef,
                sprintf('%s as distance', $query->expr()->avg('r.distance'))
            ]
        )
              ->from(
                  $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('%s.sex', $memberTableRef), ':sex'))
                      ->setParameter(
                          'sex',
                          $type === Member::USER_SEX_FEMALE ? Member::USER_SEX_FEMALE : Member::USER_SEX_MALE
                      )
                ;
                break;
            case self::LEADERBOARD_TYPE_CHIEF:
                $query->andWhere($query->expr()->eq(sprintf('%s.isLeader', $memberTableRef), true));
                break;
            case self::LEADERBOARD_TYPE_COMPANY:
                $query->andWhere($query->expr()->isNotNull(sprintf('%s.company', $teamTableRef)))
                      ->join(sprintf('%s.members', $teamTableRef), $memberTableRef)
                      ->addSelect($memberTableRef)
                ;
                break;
            case self::LEADERBOARD_TYPE_TEAM:
                $query->join(sprintf('%s.members', $teamTableRef), $memberTableRef);
                break;
            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')))
                ;
                break;
            default:
                throw new \RuntimeException(sprintf('LeaderBoard type is not supported %s', $type));
                break;
        }
        $query->join(sprintf('%s.runs', $memberTableRef), 'r');
        $query->addGroupBy(sprintf('%s.id', $memberTableRef));
        $query->addOrderBy('r.distance', 'DESC');
        $query->setMaxResults(100);

        return $this->apiResponse($query->getQuery()->getResult());

Answer 1

Set an alias and sort by it.