Count Rows in Doctrine QueryBuilder

Question

I m using Doctrine s QueryBuilder to build a query  and I want to get the total count of results from the query       repository    em- gt getRepository  FooBundle Foo      qb    repository- gt createQueryBuilder  n           - gt where  n bar    bar           - gt setParameter  bar    bar     query    qb- gt getQuery       this doesn t work  totalrows    query- gt getResult  - gt count      I just want to run a count on this query to get the total rows  but not return the actual results    After this count query  I m going to further modify the query with maxResults for pagination

User · Answer

Example working with grouping  union and stuff   Problem     qb    em- gt createQueryBuilder        - gt select  m id    rm id        - gt from  Model    m        - gt join  m relatedModels    rm        - gt groupBy  m id      For this to work possible solution is to use custom hydrator and this weird thing called  CUSTOM OUTPUT WALKER HINT    class CountHydrator extends AbstractHydrator       const NAME    count hydrator       const FIELD    count                     inheritDoc              protected function hydrateAllData                 return  int  this- gt  stmt- gt fetchColumn 0           class CountSqlWalker extends SqlWalker                    inheritDoc              public function walkSelectStatement AST SelectStatement  AST                return sprintf  SELECT COUNT    AS  s FROM   s  AS t   CountHydrator  FIELD  parent  walkSelectStatement  AST              doctrineConfig- gt addCustomHydrationMode CountHydrator  NAME  CountHydrator  class       qb from example above  countQuery   clone  qb- gt getQuery       Doctrine bug   Doesn t make a deep copy     as of  doctrine orm    2 4 6    countQuery- gt setParameters  this- gt getQuery  - gt getParameters        set custom  hint  stuff  countQuery- gt setHint Query  HINT CUSTOM OUTPUT WALKER  CountSqlWalker  class     count    countQuery- gt getResult CountHydrator  NAME

User · Answer

Adding the following method to your repository should allow you to call  repo- gt getCourseCount   from your Controller           return array     public function getCourseCount          qb    this- gt getEntityManager  - gt createQueryBuilder          qb         - gt select  count course id            - gt from  CRMPicco Component Course Model Course    course               query    qb- gt getQuery         return  query- gt getSingleScalarResult

User · Answer

Here is another way to format the query   return  repository- gt createQueryBuilder  u               - gt select  count u id                - gt getQuery               - gt getSingleScalarResult

User · Answer

To count items after some number of items  offset    qb- setFirstResults   cannot be applied in this case  as it works not as a condition of query  but as an offset of query result for a range of items selected  i  e  setFirstResult cannot be used togather with COUNT at all   So to count items  which are left I simply did the following        in repository class      count    qb- gt select  count p id          - gt from  Products    p         - gt getQuery         - gt getSingleScalarResult         return  count         in controller class       count    this- gt em- gt getRepository  RepositoryBundle  - gt          return  count- offset    Anybody knows more clean way to do it

User · Answer

If you need to count a more complex query  with groupBy  having etc    You can borrow from Doctrine ORM Tools Pagination Paginator    paginator   new  Doctrine ORM Tools Pagination Paginator  query    totalRows   count  paginator

User · Answer

Since Doctrine 2 6 it is possible to use count   method directly from EntityRepository  For details see the link   https   github com doctrine doctrine2 blob 77e3e5c96c1beec7b28443c5b59145eeadbc0baf lib Doctrine ORM EntityRepository php L161

User · Answer

For people who are using only Doctrine DBAL and not the Doctrine ORM  they will not be able to access the getQuery   method because it doesn t exists  They need to do something like the following    qb   new QueryBuilder  conn    count    qb- gt select  count id   - gt from  tableName - gt execute  - gt fetchColumn 0

User · Answer

You can also get the number of data by using the count function     query    this- gt dm- gt createQueryBuilder  AppBundle Items                       - gt field  isDeleted  - gt equals false                      - gt getQuery  - gt count

User · Answer

It s better to move all logic of working with database to repositores   So in controller you write     you can also inject  FooRepository  repository  using autowire     repository    this- gt getDoctrine  - gt getRepository Foo  class    count    repository- gt count      And in Repository FooRepository php  public function count          qb    repository- gt createQueryBuilder  t        return  qb         - gt select  count t id            - gt getQuery           - gt getSingleScalarResult        It s better to move  qb       to separate row in case you want to make complex expressions like  public function count          qb    repository- gt createQueryBuilder  t        return  qb         - gt select  count t id            - gt where  qb- gt expr  - gt isNotNull  t fieldName            - gt andWhere  qb- gt expr  - gt orX               qb- gt expr  - gt in  t fieldName2   0                qb- gt expr  - gt isNull  t fieldName2                      - gt getQuery           - gt getSingleScalarResult        Also think about caching your query result - http   symfony com doc current reference configuration doctrine html caching-drivers  public function count          qb    repository- gt createQueryBuilder  t        return  qb         - gt select  count t id            - gt getQuery           - gt useQueryCache true          - gt useResultCache true  3600          - gt getSingleScalarResult        In some simple cases using EXTRA LAZY entity relations is good http   doctrine-orm readthedocs org projects doctrine-orm en latest tutorials extra-lazy-associations html

User · Answer

Something like    qb    entityManager- gt createQueryBuilder     qb- gt select  count account id      qb- gt from  ZaysoCoreBundle Account   account      count    qb- gt getQuery  - gt getSingleScalarResult      Some folks feel that expressions are somehow better than just using straight DQL  One even went so far as to edit a four year old answer  I rolled his edit back  Go figure

[symfony] Count Rows in Doctrine QueryBuilder

Examples related to symfony

Examples related to doctrine-orm