CODEWITHSUNDEEP
symfonydoctrine-ormdoctrine-query
L01C
L01C

Reputation: 753

Doctrine query builder parenthesis bad place

I have a query that receive some array parameters without any ideas how rows there is. It must return contents with all filters (AND clause), and one or more categories (OR clause).

I can't get results I'd like because parentheses are not a the good place. I should got this SQL rendering: 

WHERE (
  f3_.idfilter = '87'
  AND f5_.idfilter = '90'
  AND f7_.idfilter = '154'
  AND f9_.idfilter = '165'
)
AND (
  c0_.content_category_idcontent_category = 1
  OR c0_.content_category_idcontent_category = 3
)

and got this instead: 

WHERE (
  (
    f3_.idfilter = '87' 
    AND f5_.idfilter = '90' 
    AND f7_.idfilter = '154' 
    AND f9_.idfilter = '165' 
    AND c0_.content_category_idcontent_category = 1
  ) 
  OR c0_.content_category_idcontent_category = 3
)

My code:

public function getContentByFiltersAjax($categs, $filters, $offset, $limit) {
    $filtersTab = explode(',', $filters);
    $query = $this->createQueryBuilder('c');

    for ($i = 1; $i <= count($filtersTab); $i++) {
        $query = $query
            ->leftJoin('c.filterfilter', 'f' . $i)
            ->andWhere('f' . $i . '.idfilter = :filter_idfilter' . $i)
            ->setParameter('filter_idfilter' . $i, $filtersTab[$i - 1]);
    }

    $categsTab = explode(',', $categs);

    if(sizeof($categsTab) > 1) {
        $expr = $query->expr();
        $categsTab = explode(',', $categs);

        foreach ($categsTab as $key => $value) {
            if($key === 0){
                $query->andWhere($expr->eq('c.contentCategorycontentCategory', $value));
            } else {
                $query->orWhere($expr->eq('c.contentCategorycontentCategory', $value));
            }
        }
    } else {
        $query
            ->andWhere('c.contentCategorycontentCategory = :category')
            ->setParameter('category', $categs);
    }

    $query
        ->andWhere('c.status = :status')
        ->setParameter('status', 'publie');

    $query->orderBy('editor.plan', 'DESC');
    $query->addOrderBy('c.creationDate', 'DESC');

    $query
        ->setFirstResult($offset)
        ->setMaxResults($limit);

    $result = $query->distinct()->getQuery()->getResult();

    return $result;
}

Upvotes: 1

Views: 1466

Answers (1)

Julesezaar
Julesezaar

Reputation: 3396

You put the orWhere's inside 1 andWhere like this:

$query->andWhere(
    $query->expr->orX(
        $query->expr->eq('c.contentCategorycontentCategory', $value1),
        $query->expr->eq('c.contentCategorycontentCategory', $value2)
    )
);

Or in your foreach loop:

$orX = $query->expr->orX();

foreach ($categsTab as $value) {
    $orX->add($query->expr->eq('c.contentCategorycontentCategory', $value));
}

$query->andWhere($orX);

Upvotes: 4

Related Questions