Get single row result with Doctrine NativeQuery
PhpDoctrine OrmPhp Problem Overview
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?
Php Solutions
Solution 1 - Php
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)
Solution 2 - Php
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();
Solution 3 - Php
->getSingleScalarResult() will return a single value, instead of an array.
Solution 4 - Php
> 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];
Solution 5 - Php
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"
}
Solution 6 - Php
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.