count of subquery in doctrine - querybuilder

user_3900951 0
Viewed 2319

I have simple query:

$this->qb->select('l.value')
        ->addSelect('count(l) AS cnt')
        ->addSelect('hour(l.time) AS date_hour')
        ->from(Logs::class, 'l')
        ->where('l.header = :header')
        ->groupBy('l.value')
        ->addGroupBy('date_hour')
        ->setParameter('header', 'someheader')

This code select 3 columns have one condition and two groupBy.

I want get records count of this query. Of course I dont want to download all records and check size of downloaded data.

Question: How to rebuild this query and get result from db as singleScalarValue()?

symfony doctrine php
2 Answers

I think you should use (id column for count) here:

    ->addSelect('count(l) AS cnt')

Something like this, but if you show your Entity i can suggest right solution:

         $this->qb->select('l')
        ->addSelect('count(l.id) AS cnt')
        ->addSelect('hour(l.time) AS date_hour')
        ->from(Logs::class, 'l')
        ->where('l.header = :header')
        ->groupBy('l.value')
        ->addGroupBy('date_hour')
        ->setParameter('header', 'someheader')

$count = $qb->getQuery()->getSingleScalarResult();
user_81133910

Since GROUP BY + COUNT + getSingleScalarResult does not seem to work together in DQL queries, I guess your best option is to count afterwards

$sub
    ->select('l.id')
    ->addSelect('l.value as hidden val')
    ->addSelect('hour(l.time) AS hidden date_hour')
    ->from(Logs::class, 'l')
    ->where('l.header = :header')
    ->groupBy('val')
    ->addGroupBy('date_hour')
    ->setParameter('header', 'someheader')
;

$subIds = array_column($sub->getQuery()->getResult(), 'id');
$count = count($subIds);
user_54185140
Related