11

The question is too close to this topic: Magento 2: Product Repositories, Filter Groups, and `AND`

but is it possible to create criteria with multiple OR and AND?

In my case i would like to create alternative to this code:

/** @var $collection \Magento\Catalog\Model\ResourceModel\Product\Collection */
        $collection = $this->collectionFactory->create();
        $collection->addAttributeToSelect('special_from_date');
        $collection->addAttributeToSelect('special_to_date');
        $collection->addAttributeToSelect('price');
        $collection->addAttributeToSelect('special_price');
        $collection->setVisibility($this->catalogProductVisibility->getVisibleInCatalogIds());

        $collection->addStoreFilter()->addAttributeToFilter(
            'special_from_date',
            [
                'or' => [
                    0 => ['date' => true, 'to' => $todayEndOfDayDate],
                    1 => ['is' => new \Zend_Db_Expr('null')],
                ]
            ],
            'left'
        )->addAttributeToFilter(
            'special_from_date',
            [
                'or' => [
                    0 => ['date' => true, 'from' => $todayStartOfDayDate],
                    1 => ['is' => new \Zend_Db_Expr('null')],
                ]
            ],
            'left'
        )->addAttributeToFilter(
            [
                ['attribute' => 'special_from_date', 'is' => new \Zend_Db_Expr('not null')],
                ['attribute' => 'special_to_date', 'is' => new \Zend_Db_Expr('not null')],
            ]
        )->addAttributeToSort(
            'special_from_date',
            'desc'
        );

The problem is that i have here several wrapped OR conditions and all of them are combined using AND.

3 Answers 3

45

Yes it is possible

You can create an

\Magento\Framework\Api\SearchCriteriaBuilder $searchCriteriaBuilder
\Magento\Framework\Api\FilterBuilder $filterBuilder
\Magento\Framework\Api\Search\FilterGroupBuilder $filterGroupBuilder

After that define your filters

// AND
$this->searchCriteriaBuilder->addFilter('attribute1','value2');
$this->searchCriteriaBuilder->addFilter('attribute2','value2');

// OR
$attr3 = $this->filterBuilder->setField('attribute3')
            ->setValue('value3')
            ->setConditionType('eq')
            ->create();
$attr4 = $this->filterBuilder->setField('attribute4')
            ->setValue('value4')
            ->setConditionType('eq')
            ->create();

 $filterOr = $this->filterGroupBuilder
            ->addFilter($attr3)
            ->addFilter($attr4)
            ->create();
 $this->searchCriteriaBuilder->setFilterGroups([$filterOr]);

 $searchCriteria = $this->searchCriteriaBuilder->create();

 $this->productRepository->getList($searchCriteria);

GET ==>

attribute1 = 'value1' AND attribute2 = 'value2' AND (attribute3='value3' OR attribute4 = 'value4')

Tell me if you have any other questions

    $attr1 = $this->filterBuilder->setField('attribute1')
        ->setValue('value1')
        ->setConditionType('eq')
        ->create();
    $attr2 = $this->filterBuilder->setField('attribute2')
        ->setValue('value2')
        ->setConditionType('eq')
        ->create();

    $filterOr1 = $this->filterGroupBuilder
        ->addFilter($attr1)
        ->addFilter($attr2)
        ->create();


    $attr3 = $this->filterBuilder->setField('attribute3')
        ->setValue('value3')
        ->setConditionType('eq')
        ->create();
    $attr4 = $this->filterBuilder->setField('attribute4')
        ->setValue('value4')
        ->setConditionType('eq')
        ->create();

    $filterOr2 = $this->filterGroupBuilder
        ->addFilter($attr3)
        ->addFilter($attr4)
        ->create();

    $this->searchCriteriaBuilder->setFilterGroups([$filterOr, $filterOr2]);

    $searchCriteria = $this->searchCriteriaBuilder->create();

    $this->productRepository->getList($searchCriteria);

GET ==>

(attribute1 = 'value1' OR attribute2 = 'value2') AND (attribute3='value3' OR attribute4 = 'value4')
8
  • Thanks for your answer. This criteria says: attribute1 = 'value2' AND attribute2 = 'value2' AND (attribute3='value3' OR attribute4 = 'value4'). I'm talking about this case: (attribute1 = 'value2' OR attribute2 = 'value2') AND (attribute3='value3' OR attribute4 = 'value4') And more interesting staff is to use OR after this statement as well.
    – Anthony
    Commented Nov 10, 2017 at 13:08
  • I update my answer you cant find the solution Commented Nov 22, 2017 at 21:14
  • 1
    @MohamedElMrabet Thanks for above code snippet. I want (attribute = 'value1') OR (attribute = 'value2' AND attribute = 'value3') OR (attribute = 'value3' AND attribute = 'value4') but i'm not able to achieve this. Can you please help? Commented Jan 1, 2019 at 4:37
  • @chiragdodia did you find any solutions? I also want (A AND B) OR (C AND D). If anyone else can help. Please let me know. Commented Feb 19, 2020 at 10:39
  • No, I haven't find the solution. Commented Feb 19, 2020 at 11:52
3

Magento applies the OR operator within the filters in the filter group, while the AND operator for joining the filter groups. This makes conditions like (A and B) or (C and D) tricky to implement. This is where the distributive laws of logical operators come in handy, because you can transform your conditions into a different set of conditions that can work with Magento's logic:

(A and B) or (C and D) = (A or (C and D)) and (B or (C and D)) = (A or C) and (A or D) and (B or C) and (B or D)

If this is too complicated for the desired purpose, one might also look into the \Magento\Framework\Api\CombinedFilterGroup class that lets you create filter groups that can group two or more filters together using 'OR' or 'AND' strategy.

0
                      `->addAttributeToFilter([
                             ['attribute' => 'small_image','null' => true ],
                             ['attribute' => 'small_image','eq' => 'no_selection' ]
                        ]);`

Reads if attribute 'small_image' is null or attribute 'small_image' == 'no_selection';

Not the answer you're looking for? Browse other questions tagged or ask your own question.