[doctrine-orm] Get single row result with Doctrine NativeQuery

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?

To fetch single row

$result = $this->getEntityManager()->getConnection()->fetchAssoc($sql)

To fetch all records

$result = $this->getEntityManager()->getConnection()->fetchAll($sql)

Here you can use sql native query, all will work without any issue.

I use fetchObject() here a small example using Symfony 4.4

    <?php 
    use Doctrine\DBAL\Driver\Connection;

    class MyController{

    public function index($username){
      $queryBuilder = $connection->createQueryBuilder();
      $queryBuilder
        ->select('id', 'name')
        ->from('app_user')
        ->where('name = ?')
        ->setParameter(0, $username)
        ->setMaxResults(1);
      $stmUser = $queryBuilder->execute();

      dump($stmUser->fetchObject());

      //get_class_methods($stmUser) -> to see all methods
    }
  }

Response:

{ 
"id": "2", "name":"myuser"
}

I just want one result

implies that you expect only one row to be returned. So either adapt your query, e.g.

SELECT player_id
FROM players p
WHERE CONCAT(p.first_name, ' ', p.last_name) = ?
LIMIT 0, 1

(and then use getSingleResult() as recommended by AdrienBrault) or fetch rows as an array and access the first item:

// ...
$players = $query->getArrayResult();
$myPlayer = $players[0];

->getSingleScalarResult() will return a single value, instead of an array.

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

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)