group-bysymfony4dqlknppaginatorbundle

Symfony 4 GROUP BY how to format in DQL


Symfony 4 & KnpPaginatorBundle. I have a stock table with the same model many times. I would like to do something like this in my Repository:

$query = $this->createQueryBuilder(...);
$query = $query->andWhere(.....);
$query = $query->addGroupBy(brand, model);
$query = $query->addOrderBy(brand, model);
return $query;
......
KnpPaginatorBundle

The skeleton of what I wish I had

After treatment in KnpPaginatorBundle The problem that I want to return multiple Fields.. (And i think the id pose a problem)


A bit like that : (It works as I want)

        $conn = $this->getEntityManager()->getConnection();
        $sql = 'SELECT objet,marque,type FROM stock GROUP BY objet,`type`,marque ORDER BY marque,type';
        $stmt = $conn->prepare($sql);
        $stmt->execute();
        return $stmt->fetchAllAssociative():

But I would like to use the coding from above ..

Sorry for my aproximative English ;-)


Solution

  • Thank you for your interest! Here is my answer for my problem :

    $query = $this->createQueryBuilder('a');
    $query->select('a.objet,a.marque,a.type');
    $query->addGroupBy('a.objet');
    $query->addGroupBy('a.marque');
    $query->addGroupBy('a.type');
    $query->addOrderBy('a.marque');
    $query->addOrderBy('a.type');
    dd($query->getQuery()->execute());