CODEWITHSUNDEEP
sqlsymfonydoctrinedistinct
Gauthier
Gauthier

Reputation: 1266

Doctrine QueryBuilder multiple distinct

I would like to restrict results with DISTINCT on two columns using doctrine ORM

My function is like this :

public function findFdvLinkedToPdv($pointVenteCodes){
    $queryBuilder = 
    $this->createQueryBuilder('r')
                ->leftJoin('r.forceVente', 'forceVente')
                ->leftJoin('r.pointVente', 'pointVente')
                ->leftJoin('r.signature', 'signature')
                ->leftJoin('signature.affaire', 'affaire')
                ->andWhere('pointVente.code IN (:pointvente_codes)')
                ->orderBy('forceVente.matricule', 'ASC')
                ->addOrderBy('pointVente.code', 'ASC')
                ->addOrderBy('affaire.code', 'ASC')
                ->addOrderBy('r.type', 'ASC')
                ->setParameters(array('pointvente_codes' => $pointVenteCodes,))
                ->select(array(
                        'forceVente.matricule AS forcevente_matricule',
                        'pointVente.code AS pointvente_code',
                        'affaire.code AS affaire_code',
                        'r.id AS id',
                        'r.profil AS profil',
                        'r.type AS type',
                        'forceVente.nom AS nom',
                        'forceVente.prenom AS prenom',
                        'forceVente.email AS email',
                        'r.deletedAt AS deletedAt'));
    return $queryBuilder->getQuery()->getArrayResult();
}

For each forcevente.matricule and each pointVente.code , I have from 2 to 6 rows. I would like to get one row for each couple forcevente.matricule/pointVente.code I have to do a distinct on both columns, but when I try :

            ->select(array(
                    'DISTINCT forceVente.matricule AS forcevente_matricule',
                    'DISTINCT pointVente.code AS pointvente_code',
                    'affaire.code AS affaire_code', etc ...

I have a doctrine error ...

[EDIT] I do this in PHP after executing the request to filter the results...

//BEGIN PATCH
$i=0;
$linkedForceVentes2 = array();
foreach ($linkedForceVentes as $item) {
    if (!isset($linkedForceVentes2[$item['pointvente_code']][$item['forcevente_matricule']])){
        $linkedForceVentes2[$item['pointvente_code']][$item['forcevente_matricule']] = $item;
    }else{
        unset($linkedForceVentes[$i]);
    }
    $i++;
}
//END PATCH

Upvotes: 1

Views: 4294

Answers (1)

Gauthier
Gauthier

Reputation: 1266

I finaly resolved it with a groupBy :

->groupBy('forcevente_matricule', 'pointvente_code', 'type' , 'affaire_code');

Just after the select statement.

Upvotes: 2

Related Questions