Magento2: Filter Group Builder OR doesn't work only AND works

[joshspivey]

Filter Group Builder OR doesn't work only AND works.
Its wrong according to the Docs here
http://devdocs.magento.com/guides/v2.1/howdoi/webapi/search-criteria.html

        $productFilters[] = $this->filterBuilder->setField('sku')
          ->setValue('%'.$productSearch.'%')
          ->setConditionType('like')
          ->create();

        $productFilters[] = $this->filterBuilder->setField('name')
          ->setValue('%'.$productSearch.'%')
          ->setConditionType('like')
          ->create();

        $filter_group_products = $this->filterGroup
          ->setFilters($productFilters)
          ->create();

        $searchCriteriaProducts = $this->searchCriteriaBuilder
                      ->setFilterGroups([$filter_group_products])
                      ->create();

        $listProducts = $this->orderItemRepository->getList($searchCriteriaProducts);

makes this query with a AND

SELECT `main_table`.* FROM `sales_order_item` AS `main_table` WHERE (`sku` LIKE '%80705%') AND (`name` LIKE '%80705%')

According to your docs is should make this with a OR

SELECT `main_table`.* FROM `sales_order_item` AS `main_table` WHERE (`sku` LIKE '%80705%') OR (`name` LIKE '%80705%')

Preconditions

1. Magento 2.1.3
2. PHP 7.1w
3. Percona 5.6

Steps to reproduce

1. Call the first example above.
2. Look at query in mysql work bench
3. No data gets pulled because its using a AND

Expected result

1. 
   
   1. Data should get pulled because it's using a OR
   
   

Actual result

1. [Screenshot, logs]
   
   
   
   

@igrybkov I think this has something to do with your update on
https://github.com/magento/magento2/commit/19d8137e180503bc8092d40b8ce710491eff8ee9

[joshspivey]

Here is another reference of how its supposed to work

http://magento.stackexchange.com/questions/99575/filtering-with-magento2/156660#156660

@xmav Do you think this has anything to do with your update? https://github.com/magento/magento2/commit/bf891993a2a7cdd9c953761eda850051a1872a4a

[MortenKL]

Just to confirm, this bug is also present with Magento 2.1.3 running PHP 5.6. and PHP 5.7 - Just upgraded to 2.1.3 here and filtering with OR in the REST API is now _not_ working

[joshspivey]

I am pretty sure its in one of these updates by @igrybkov https://github.com/magento/magento2/commits/19d8137e180503bc8092d40b8ce710491eff8ee9/lib/internal/Magento/Framework/Search/Adapter/Mysql/Filter/Builder.php

[joshspivey]

If you need a way to test this or get a primer up and running quick you can install this.
https://github.com/joshspivey/magento2-sales-grid
Try to search for product by name or sku.

[MortenKL]

It works for me now after a clean install of Magneto 2.1.3, it seems that somthing had gone wrong with my updating from Magento 2.0.7.
So when I use the following query, through the rest API:
http://magshop2.mydomain.net/rest/V1/orders/?searchCriteria[page_size]=10&
searchCriteria[filter_groups][0][filters][0][field]=status&
searchCriteria[filter_groups][0][filters][0][value]=complete&
searchCriteria[filter_groups][0][filters][1][field]=status&
searchCriteria[filter_groups][0][filters][1][value]=processing

I get the expected json result back:

... "search_criteria": { "filter_groups": [ { "filters": [ { "field": "status", "value": "complete", "condition_type": "eq" }, { "field": "status", "value": "processing", "condition_type": "eq" } ] } ], "page_size": 10 }, total_count:2
However I'm not entirely sure that this is releated to the filtering problems described in this issue since it is done through the REST API.

[NotAndD]

as @MortenKL said, it works correctly using Magento REST api, I tried like him and it works for me (Magento 2.1.3).
I don't know if the Rest api uses the Filter Group Builder but I guess no since with rest it works and with filter group builder it doesn't.

[joshspivey]

@MortenKL @ziliquas I did a clean install using 2.1.3 from the begining. You can test this with installing my plugin.

https://github.com/joshspivey/magento2-sales-grid
Just look at the read me its a quick composer install.

Here is the controller with the issue.

https://github.com/joshspivey/magento2-sales-grid/blob/master/Controller/Adminhtml/Orders/OrderList.php

I know there are 6 areas of mage 2 and REST being one of them having its own flow. But I don't see much logic under the rest area for filters so it might be using API.

call controller like.

http://staging.yoursite.com/index.php/admin_123/SalesGrid/Orders/OrderList/key/2bbd81a89be9da0105c92f049ed36411587cfde17e3a93de32dadbb17fc3b1b6/?productSearch=product&page=1&results_per_page=20&searchColumns=false&isAjax=true

^
productSearch=product name here

Here is my DI so you can see the area.

        \Magento\Framework\App\Action\Context $context,
        \Magento\Sales\Api\OrderRepositoryInterface $orderRepository,
        \Magento\Sales\Api\OrderItemRepositoryInterface $orderItemRepository,
        \Magento\Framework\Api\SearchCriteriaBuilder $searchCriteriaBuilder,
        \Magento\Framework\Api\FilterBuilder $filterBuilder,
        \Magento\Framework\Api\Search\FilterGroupBuilder $filterGroupBuilder,
        \Magento\Framework\Api\Search\FilterGroup $filterGroup,
        \Magento\Framework\Controller\Result\JsonFactory $resultJsonFactory,
        \Magento\Framework\Api\SortOrderBuilder $sortOrderBuilder

[igrybkov]

Hi,

@joshspivey, As I see, you're using \Magento\Sales\Api\OrderItemRepositoryInterface::getList.

This issue happens because several repositories of the Sales module (including the one which you're using) are working incorrectly with the collection. It is already addressed in the develop branch.

We've created internal ticket MAGETWO-64087 to address this issue specifically for 2.1.x versions.

Below you can see some notes from our investigation.

Interface \Magento\Sales\Api\OrderItemRepositoryInterface is implemented by \Magento\Sales\Model\Order\ItemRepository. Its getList method looks like this:

\Magento\Sales\Model\Order\ItemRepository::getList in Magento 2.1.3:

/**
 * Find entities by criteria
 *
 * @param SearchCriteria $searchCriteria
 * @return OrderItemInterface[]
 */
public function getList(SearchCriteria $searchCriteria)
{
    /** @var OrderItemSearchResultInterface $searchResult */
    $searchResult = $this->searchResultFactory->create();
    $searchResult->setSearchCriteria($searchCriteria);
    foreach ($searchCriteria->getFilterGroups() as $filterGroup) {
        foreach ($filterGroup->getFilters() as $filter) {
            $condition = $filter->getConditionType() ? $filter->getConditionType() : 'eq';
            $searchResult->addFieldToFilter($filter->getField(), [$condition => $filter->getValue()]);
        }
    }
    /** @var OrderItemInterface $orderItem */
    foreach ($searchResult->getItems() as $orderItem) {
        $this->addProductOption($orderItem);
    }
    return $searchResult;
}

To find how the filters should be processed, we need to look at implementation of $searchResult->addFieldToFilter(),
which is implemented by \Magento\Sales\Model\ResourceModel\Order\Item\Collection:

\Magento\Sales\Model\ResourceModel\Order\Item\Collection::addFieldToFilter in Magento 2.1.3:

/**
 * Add field filter to collection
 *
 * @see self::_getConditionSql for $condition
 *
 * @param string|array $field
 * @param null|string|array $condition
 * @return $this
 */
public function addFieldToFilter($field, $condition = null)
{
    if (is_array($field)) {
        $conditions = [];
        foreach ($field as $key => $value) {
            $conditions[] = $this->_translateCondition($value, isset($condition[$key]) ? $condition[$key] : null);
        }

        $resultCondition = '(' . implode(') ' . \Magento\Framework\DB\Select::SQL_OR . ' (', $conditions) . ')';
    } else {
        $resultCondition = $this->_translateCondition($field, $condition);
    }

    $this->_select->where($resultCondition, null, Select::TYPE_CONDITION);

    return $this;
}

PHPDoc of this method says that addFieldToFilter can accept an array as a first argument, and you can see in the code that its values will be imploded by OR operator.

However, ItemRepository::getList pass filters to this method one by one and addFieldToFilter adds them as separate filters, not as a single group of filters.

Actually, it's a common issue of the Sales module and will be reproducible in several repositories:

• \Magento\Sales\Model\Order\AddressRepository::getList
• \Magento\Sales\Model\Order\CreditmemoRepository::getList
• \Magento\Sales\Model\Order\ItemRepository::getList
• \Magento\Sales\Model\Order\ShipmentRepository::getList

This issue is already addressed for develop branch in internal ticket MAGETWO-56082 where we’ve implemented a general solution to unify processing of SearchCriteria and this fix already delivered to the develop branch:
https://github.com/magento/magento2/blob/develop/app/code/Magento/Sales/Model/Order/ItemRepository.php#L132

[joshspivey]

@igrybkov thanks for you detailed writeup. Awesome!

[magento-engcom-team]

@joshspivey, thank you for your report.
The issue is already fixed in 2.1.9