Doctrine - count users in table by id

Question

I have 6 records in a database. 3 from user 1, 2 from user 2 and 1 from user 3.

I want to count them by user_id so the final result would be 3. And it always returns count of all records from table. It returns 6.

My func:

return $this->getDeviceRepository()
        ->createQueryBuilder('tlu')
        ->select('COUNT(tlu.user_id)')
        ->getQuery()
        ->getResult();

Does this answer your question? [Get maximum id inside Doctrine entity](https://stackoverflow.com/questions/36937754/get-maximum-id-inside-doctrine-entity) — yivi, Nov 20 '19 at 12:45

score 2 · Answer 1 · answered Nov 20 '19 at 12:50

2

You have 2 solutions (with groupBy or countDistinct)

return $this->getDeviceRepository()
        ->createQueryBuilder('tlu')
        ->select('COUNT(tlu.user_id)')
        ->groupBy('tlu.user_id')
        ->getQuery()
        ->getResult();

or

return $this->getDeviceRepository()
        ->createQueryBuilder('tlu')
        ->select('COUNT(tlu.user_id)')
        ->countDistinct('tlu.user_id')
        ->getQuery()
        ->getResult();

answered Nov 20 '19 at 12:50

Rached BCH

31
2

I needed to add count($result) on result because it returns array with 3 objects. @RachedBCH – Elementor Nov 20 '19 at 13:03

score 0 · Answer 2 · answered Nov 20 '19 at 16:37

Yes, we can do as following

getResult()->count();

or

return $this->getDeviceRepository()
        ->createQueryBuilder('tlu')
        ->select('COUNT(tlu.user_id)')
        ->countDistinct('tlu.user_id')
        ->getQuery()
        ->getSingleScalarResult();

Doctrine - count users in table by id

2 Answers2