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- gt addEntityResult  VNNCoreBundle Player    p     rsm- gt addFieldResult  p    player id    id      sql          SELECT player id       FROM players p      WHERE CONCAT p first name       p last name           query    this- gt getEntityManager  - gt createNativeQuery  sql   rsm    query- gt setParameter 1   name    players    query- gt getResult      That last line returns a list of players but I just want one result  How do I do that

User · Answer

I use fetchObject    here a small example using Symfony 4 4       lt  php      use Doctrine DBAL Driver Connection       class MyController       public function index  username          queryBuilder    connection- gt createQueryBuilder           queryBuilder         - gt select  id    name           - gt from  app user           - gt where  name               - gt setParameter 0   username          - gt setMaxResults 1          stmUser    queryBuilder- gt execute           dump  stmUser- gt fetchObject              get class methods  stmUser  - gt  to see all methods             Response        id    2    name   myuser

User · Answer

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- gt getArrayResult     myPlayer    players 0

User · Answer

You can use  query- gt 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- gt 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

User · Answer

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- gt createQueryBuilder  - gt select  sub           - gt from   Application Entity Subscriber    sub           - gt where  sub subscribe  isSubscribe           - gt setParameter  isSubscribe   1            - gt setMaxResults 1          - gt getQuery           - gt getOneOrNullResult

User · Answer

- getSingleScalarResult   will return a single value  instead of an array

User · Answer

To fetch single row   result    this- gt getEntityManager  - gt getConnection  - gt fetchAssoc  sql    To fetch all records   result    this- gt getEntityManager  - gt getConnection  - gt fetchAll  sql    Here you can use sql native query  all will work without any issue

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

Examples related to doctrine-orm