Doctrine2 returns array of 1 element instead of null

I'm trying to get top 3 users who has featured property = true. Here's my EntityRepository:

我正在努力让前三名用户拥有property = true。这是我的EntityRepository:

public function getFeaturedCleaners()
{
    $baseQuery = $this->_baseCleanerQuery();

    return $baseQuery
        ->where($baseQuery->expr()->eq('c.featured', $baseQuery->expr()->literal(true)))
        ->andWhere($baseQuery->expr()->isNotNull('c.user'))
        ->orderBy('ratingTotal', 'DESC')
        ->setMaxResults(3)
        ->getQuery()->getArrayResult();
}

private function _baseCleanerQuery()
{
    return $this->createQueryBuilder('c')
        ->select([
            'c as user',
            'COALESCE(AVG(rv.speed), 0) as ratingSpeed',
            'COALESCE(AVG(rv.quality), 0) as ratingQuality',
            'COALESCE(AVG(rv.responsibility), 0) as ratingResponsibility',
            'COALESCE((AVG(rv.speed) + AVG(rv.quality) + AVG(rv.responsibility)) / 3, 0) as ratingTotal',
        ])
        ->leftJoin('c.reviews', 'rv');
}

But when there's no featured users, I get one row with null fields, instead of empty array:

但是当没有特色用户时,我得到一行包含空字段,而不是空数组:

array(1) { [0]=> array(5) { ["user"]=> NULL ["ratingSpeed"]=> string(6) "0.0000" ["ratingQuality"]=> string(6) "0.0000" ["ratingResponsibility"]=> string(6) "0.0000" ["ratingTotal"]=> string(10) "0.00000000" } }

Why is it happening? How can I fix it?

为什么会这样?我该如何解决?

1 个解决方案

#1

This is because you are using aggregation in your query (with AVG), so it always will get at least a row with the result.

这是因为您在查询中使用聚合(使用AVG),因此它总是至少会得到一行结果。

SELECT AVG(0) FROM user WHERE 0;

This query will always return at least 1 row with the result, not a null result.

此查询将始终返回至少1行结果,而不是null结果。

If the query must continue as it is, you can filter the result before return it checking that the user is not null, otherwise return an empty array.

如果查询必须按原样继续,则可以在返回结果之前过滤结果,检查用户是否为空,否则返回空数组。

return (null == $result[0]['user'] ? [] : $result);

#1