Magento 2: How to add multilevel AND and OR conditions to searchCriteria?

Question

What i want to achieve is this -

attr1 = 'val1' AND attr2 = 'val2' AND ((attr_s1 = 'val_s1' AND attr_d1 = 'val_d1' AND attr_t1 = 'val_t1') OR (attr_s2 = 'val_s2' AND attr_d2 = 'val_d2' AND attr_t2 = 'val_t2') OR (attr_s3 = 'val_s3' AND attr_d3 = 'val_d3' AND attr_t3 = 'val_t3'))

in short

P and Q and ( (A and B and C) or (L and M and N) or (X and Y and Z) )

I have gone through similar kind of thread here - SearchCriteria Logical OR and AND search

But what I want is much more complex. Can it be done with searchCriteria or in any other way?

Answer 1

Try following way:

use Magento\Framework\Api\FilterBuilder;
use Magento\Framework\Api\Search\FilterGroupBuilder;
use Magento\Framework\Api\SearchCriteriaBuilder;
use Magento\Cms\Model\BlockRepository;

DI

/**
 * @var FilterBuilder
 */
private $filterBuilder;

/**
 * @var FilterGroupBuilder
 */
private $filterGroupBuilder;

/**
 * @var SearchCriteriaBuilder
 */
private $searchCriteriaBuilder;

/**
 * @var BlockRepository
 */
private $blockRepository;

public function __construct(
    FilterBuilder $filterBuilder,
    FilterGroupBuilder $filterGroupBuilder,
    SearchCriteriaBuilder $searchCriteriaBuilder,
    BlockRepository $blockRepository
) {
    $this->filterBuilder = $filterBuilder;
    $this->filterGroupBuilder = $filterGroupBuilder;
    $this->searchCriteriaBuilder = $searchCriteriaBuilder;
    $this->blockRepository = $blockRepository;

}

Now:

$filter = $this->filterBuilder->setField('identifier')
    ->setConditionType('eq')
    ->setValue('men-block')
    ->create();

$filter2 = $this->filterBuilder->setField('identifier')
    ->setConditionType('eq')
    ->setValue('gear-block')
    ->create();

$filterGroup = $this->filterGroupBuilder->setFilters([$filter, $filter2])
    ->create();

$filter3 = $this->filterBuilder->setField('is_active')
    ->setConditionType('eq')
    ->setValue(1)
    ->create();

$filterGroup2 = $this->filterGroupBuilder->setFilters([$filter3])
    ->create();

$searchCriteria = $this->searchCriteriaBuilder->setFilterGroups([$filterGroup, $filterGroup2])
    ->create();
$blockItems = $this->blockRepository->getList($searchCriteria)->getItems();

Query:

SELECT `main_table`.* FROM `cms_block` AS `main_table` WHERE ((`identifier` = 'men-block') OR (`identifier` = 'gear-block')) AND ((`is_active` = 1))