Symfony2.3 Doctrine Query Builder complex query

Question

I am looking for the equivalent of this SQL SELECT statement in Doctrine Query Builder:

SELECT p.*
FROM position p, fonction f
WHERE ( (p.id = f.position_id) AND (p.type ='MONO_MEMBRE') AND (f.date_fin IS NOT NULL) )
OR ( p.type='MULTI_MEMBRE' )

I tried this way :

function(PositionRepository $er) {
    return $er->createQueryBuilder('p')
        ->leftJoin('p.fonctions', 'f', 'WITH', '(f.dateFin IS NOT NULL) AND (p.type= :type_mono)')
        ->orWhere('p.type = :type_multi')
        ->setParameters(array(
            'type_multi' => 'MULTI_MEMBRE',
            'type_mono'  => 'MONO_MEMBRE'
            ));
}

It doesn't return the expected results. Can anyone help me please? Thank you for your time in advance.

Thanks for the quick answer. the expected results are as follow : either the type of the position is "MULTI_MEMBRE" or it's "MONO_MEMBRE" and there isn't any 'fonction' associated with the position that has NULL as 'dateFin'. I think that the SQL statement can explain more clearly. — zgkais, Mar 11 '15 at 09:09

score 0 · Answer 1 · answered Mar 11 '15 at 14:19

0

This should be equivalent.

return $er->createQueryBuilder('p')
    ->leftJoin('p.fonctions', 'f')
    ->where('p.type = :mono')
    ->andWhere('f.date_fin IS NOT NULL')
    ->orWhere('p.type = :muli')
    ->setParameter(['mono' => 'MONO_MEMBRE', 'multi' => 'MULTI_MEMBRE']);

answered Mar 11 '15 at 14:19

Markus

440
2
10

Thanks for the answer. This is equivalent to the SQL statement that I posted. But this excludes the 'positions' that do not have any 'fonction' associated with. – zgkais Mar 11 '15 at 15:45
are you sure? A left join should return all positions regardless if the position have an fonction or not. – Markus Mar 12 '15 at 09:03
Right. But with the condition "f.dateFin IS NOT NULL" it doesn't return any position that has "MONO_MEMBRE" as type and that doesn't have any fonction associated with it. – zgkais Mar 12 '15 at 11:51
I already found the solution. Check it out in my answer. That's the way to deal with the parenthesis... – zgkais Mar 12 '15 at 12:02

score 0 · Accepted Answer · answered Mar 11 '15 at 15:52

I followed the doctrine documentation on the QueryBuilder and I found the solution. Here it is :

function(PositionRepository $er) {
    $qb= $er->createQueryBuilder('p')
            ->leftJoin('p.fonctions', 'f');

    $andModule = $qb->expr()->andX();
    $andModule->add($qb->expr()->isNotNull('f.dateFin'));
    $andModule->add($qb->expr()->eq('p.type', ':mono'));    

    return $qb->where('f IS NULL')
              ->orWhere('p.type = :multi')
              ->orWhere($andModule)
              ->setParameters(array(
                'mono' => 'MONO_MEMBRE',
                'multi' => 'MULTI_MEMBRE'
                ));
}

Symfony2.3 Doctrine Query Builder complex query

2 Answers2