Get single row result with Doctrine NativeQuery

Question

I'm trying to get a single row returned from a native query with Doctrine. Here's my code:

$rsm = new ResultSetMapping; $rsm->addEntityResult('VNNCoreBundle:Player', 'p'); $rsm->addFieldResult('p', 'player_id', 'id');  $sql = "      SELECT player_id       FROM players p      WHERE CONCAT(p.first_name, ' ', p.last_name) = ? ";  $query = $this->getEntityManager()->createNativeQuery($sql, $rsm); $query->setParameter(1, $name); $players = $query->getResult(); 

That last line returns a list of players but I just want one result. How do I do that?

Answer 1

You can use $query->getSingleResult(), which will throw an exception if more than one result are found, or if no result is found. (see the related phpdoc here https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/AbstractQuery.php#L791)

There's also the less famous $query->getOneOrNullResult() which will throw an exception if more than one result are found, and return null if no result is found. (see the related phpdoc here https://github.com/doctrine/doctrine2/blob/master/lib/Doctrine/ORM/AbstractQuery.php#L752)

Answer 2

Both getSingleResult() and getOneOrNullResult() will throw an exception if there is more than one result. To fix this problem you could add setMaxResults(1) to your query builder.

 $firstSubscriber = $entity->createQueryBuilder()->select('sub')         ->from("\Application\Entity\Subscriber", 'sub')         ->where('sub.subscribe=:isSubscribe')         ->setParameter('isSubscribe', 1)           ->setMaxResults(1)         ->getQuery()         ->getOneOrNullResult();