Doctrine2 returns array of 1 element instead of null

Question

I'm trying to get top 3 users who has featured property = true. Here's my 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?

Miguel G. Flores · Accepted Answer · 2016-05-19 15:13:19Z

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.

SELECT AVG(0) FROM user WHERE 0;

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

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

answered May 19, 2016 at 15:13

Miguel G. Flores

8127 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Doctrine2 returns array of 1 element instead of null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related