CODEWITHSUNDEEP
symfonydoctrine-orm
DevLime
DevLime

Reputation: 1027

Is there a correct way in Doctrine to nest 'OR' where clauses?

I have a query that pulls in some jobs if they have an end date and are not completed which produces the desired result:

real code

$this->createQueryBuilder('j')
                ->where('j.completed = :false OR j.completed is NULL')
                ->andWhere('j.has_due_date = :true')
                ->andWhere('j.has_due_date = :true')
                ->setParameters( array(
                    'false' => FALSE,
                    'true' => TRUE
                ) )
                ->orderBy('j.due_date', 'asc')
                ->getQuery();

result

SELECT j FROM App\Entity\Jobs j 
    WHERE (
        j.completed = :false 
        OR j.completed is NULL
    ) 
    AND j.has_due_date = :true 
    ORDER BY j.due_date ASC

I'm wanting to follow the DQL best practice for this and feel like there is another way or writing this and only having a single WHERE clause per call on a ->where variant (andWhere(), orWhere())

In my mind it's something like this but I can't find anything out there to confirm it:

pseudo code

$query = $this->createQueryBuilder('j')
                  ->where( $requiredClass->
                                ->where('j.completed = :false')
                                ->orWhere('j.completed is NULL')
                                ->setParameter('false', FALSE) 
                  )
                  ->andWhere('j.has_due_date = :true')
                  ->setParameter('true', TRUE) 
                  ->orderBy('j.due_date', 'asc')
                  ->getQuery();

Main questions are:

Upvotes: 0

Views: 1553

Answers (2)

Łukasz Jakubek
Łukasz Jakubek

Reputation: 1013

As stated in the official documentation for QueryBuilder best practice - the right way is to use the helper method QueryBuilder::expr() and also the Doctrine\ORM\Query\Expr helper class. High level API methods

i.e.:

// create QueryBuilder
$qb = $this->createQueryBuilder('j');

// build conditions
$qb->where(
    $qb->expr()->orX(
        $qb->expr()->eq('j.completed', ':false'),
        $qb->expr()->isNull('j.completed')
    )
)
->andWhere(
    $qb->expr()->eq('j.has_due_date', ':true')
)
->andWhere(
    $qb->expr()->eq('j.has_due_date', ':true')
)
->setParameters(array(
    'false' => FALSE,
    'true' => TRUE
))
->orderBy('j.due_date', 'asc');

// get query
$query = $qb->getQuery();

// execute and get result
$result = $query->getResult();

Upvotes: 1

Juan I. Morales Pestana
Juan I. Morales Pestana

Reputation: 1147

Using the query builder should be easier and more understandable

    public function someFunction()
    {
        $qb = $this->createQueryBuilder('j');

        $orX = $qb->expr()->orX();
        $andX = $qb->expr()->andX();

        $orX->add($qb->expr()->eq('j.completed', $qb->expr()->literal(false)));
        $orX->add($qb->expr()->isNull('j.completed'));

        $andX->add($qb->expr()->eq('j.has_due_date', $qb->expr()->literal(true)));

        $qb->andWhere($orX, $andX)
            ->orderBy('j.due_date', 'asc')
            ->getQuery()
            ->getResult();
    }

Upvotes: 1

Related Questions