CODEWITHSUNDEEP
symfonydoctrinequery-builder
keegzer
keegzer

Reputation: 524

Query builder with AND and OR

I work in Symfony 3.4 flex, I need to do one querybuilder like this :

All media with "published" at True AND media is TYPE OF :type AND ((condition 1) OR (condition 2) OR (condition 3)) with a symfony doctrine query builder. These 2 conditions are already true (published and instance of :instance):

 $queryBuilder = $this->createQueryBuilder('m')
        ->chere('m.published = :published')
        ->andWhere('m INSTANCE OF :instance');

/* 
WHERE m.published = true AND m INSTANCE OF :instance

AND (
    //    ('m.startDate <= :today AND m.endDate >= :today')
    // OR ('m.startDate IS NULL AND m.endDate >= :today')
    // OR ('m.startDate IS NULL AND m.endDate IS NULL')
    // OR ('m.startDate <= :today AND m.endDate IS NULL')
*/

When I try something like :

$queryBuilder->andWhere($queryBuilder->expr()->andX(
            $queryBuilder->expr()->lte('m.startDate', ':today'),
            $queryBuilder->expr()->gte('m.endDate', ':today'))
   );

But it doesn't work it return something like :

WHERE (
    (m.published = 1 AND m.type IN (:type)) // I already needed this in all cond
    AND m.start_date <= :today AND m.end_date >= :today // cond 1
)

    OR (m.start_date IS NULL :today AND m.end_date IS NULL) // cond 2
    OR ... // cond 3

So my condition with ((m.published = 1 AND m.type IN (:type))) need to be in all conditions not only the first

Do you have any idea ?

Thanks.

Upvotes: 1

Views: 9497

Answers (2)

Preciel
Preciel

Reputation: 2827

For your own knowledge, with DQL, it would be like this:

//Important to have it initialized alone so you can use it for expressions
$qb=$this->createQueryBuilder('m');

$qb->andWhere($qb->expr()->andX(
    "m.published=1",
    "m INSTANCE OF :instance",
    $qb->expr()->orX(
        $qb->expr()->andX("m.startDate <= :today", "m.endDate >= :today"),
        $qb->expr()->andX("m.startDate IS NULL", "m.endDate >= :today"),
        $qb->expr()->andX("m.startDate IS NULL", "m.endDate IS NULL"),
        $qb->expr()->andX("m.startDate <= :today", "m.endDate IS NULL")
    )
))
->setParameters(array(
    'instance'=>$instance,
    'today'=>$today,
))
->getQuery()
->execute();

It will give you the following DQL query:

SELECT m
FROM AppBundle\Entity\Media m
WHERE m.published=1
    AND m INSTANCE OF :instance
    AND ((m.startDate <= :today AND m.endDate >= :today)
        OR (m.startDate IS NULL AND m.endDate >= :today)
        OR (m.startDate IS NULL AND m.endDate IS NULL)
        OR (m.startDate <= :today AND m.endDate IS NULL))

Some tips:

Never use where() or orWhere, its bound to give you problems someday
You can read more about it here

Always use double quotes for your query. Doctrine will only accept single quote for strings in query

Upvotes: 5

AythaNzt
AythaNzt

Reputation: 1057

You can do this:

 $queryBuilder = $this->createQueryBuilder('m')
        ->where("((m.published = true AND m INSTANCE OF :instance) AND
                      (
                          (m.startDate <= :today AND m.endDate >= :today) OR
                          (m.startDate IS NULL AND m.endDate >= :today) OR
                          (m.startDate IS NULL AND m.endDate IS NULL) OR
                          (m.startDate <= :today AND m.endDate IS NULL)
                      )
                 )"
               );

Upvotes: 1

Related Questions