Bug: searchCriteria conditionType "IN" does not show any results

[tw123]

The following soap request is currently translated into the sql statement below.

Unfortunately the backticks are wrong.
'canceled,new' > shows no results
'canceled','new' > would show results

Can you fix this bug in the next release?

<soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:def="http://host/soap/default?services=salesOrderRepositoryV1">
   <soap:Header/>
   <soap:Body>
      <def:salesOrderRepositoryV1GetListRequest>
         <searchCriteria>
            <filterGroups>
               <item>
                  <filters>
                     <item>
                        <field>state</field>
                        <value>canceled,new</value>
                        <conditionType>in</conditionType>
                     </item>
                  </filters>
               </item>
            </filterGroups>
         </searchCriteria>
      </def:salesOrderRepositoryV1GetListRequest>
   </soap:Body>
</soap:Envelope>

SELECT `main_table`.* FROM `sales_order` AS `main_table` WHERE (`state` IN('canceled,new'))

[wouthoekstra]

I'm experiencing the same issue for the rest API.

[arkadiych]

Thanks for reporting. Internal ticket MAGETWO-47451

[cspruiell]

This issue has been resolved. Thanks!

[JakeOB25]

Hi @arkadiych , this still appears to be an issue with SOAP commands. Can you confirm if the initial example provided in this post "should" be working?

[skynixukraine]

Hi @arkadiych @cspruiell ,

This seems, it is still an issue with REST API on Magento 2.1.2
Im using the following endpoint
http://bikebiz.local:4575/index.php/rest/V1/products?searchCriteria[filter_groups][0][filters][0][field]=category_id&searchCriteria[filter_groups][0][filters][0][value]=5,112&searchCriteria[filter_groups][0][filters][0][condition_type]=in

And this looks like Magento ignores condition_type in, and uses something like this category_id=5
as a result returns 0 products.

However, if I change order of values, e.g. [value]=112,5 it will use category_id=112 as a result it will return a bunch of products from category 112.

This shows that condition_type in completely does not work. Could you advice on file name where I can fix this please?

[stapleup]

I can confirm that this issue is still present in Magento CE 2.2.4. If you try to filter by multiple categories using the logical OR procedure explained in magento doc, it doesn't return any results, filtering by single category works just fine. If you try to use like condition type it still fails.

Example URL: (http://magento.localhost/rest/default/V1/products?searchCriteria[filter_groups][0][filters][0][field]=category_id&searchCriteria[filter_groups][0][filters][0][value]=23&searchCriteria[filter_groups][0][filters][0][condition_type]=eq&searchCriteria[filter_groups][0][filters][1][field]=category_id&searchCriteria[filter_groups][0][filters][1][value]=24&searchCriteria[filter_groups][0][filters][1][condition_type]=eq)

{
    "items": [],
    "search_criteria": {
        "filter_groups": [
            {
                "filters": [
                    {
                        "field": "category_id",
                        "value": "23",
                        "condition_type": "eq"
                    },
                    {
                        "field": "category_id",
                        "value": "24",
                        "condition_type": "eq"
                    }
                ]
            }
        ]
    },
    "total_count": 0
}

While in catalog both categories have over 100 products.

[LucianMihalache]

Why is this closed and tagged as fixed?
It is not fixed in 2.3.1.
The linked task is still not done too.

Edit4
After the end of this post, I realize that this is not a bug of the rest API or anything else, but a bug on the Magento_Eav Module.

Endpoint:

/rest/default/V1/products/attribute-sets/groups/list

Filters:

'searchCriteria[filterGroups][0][filters][0][field]' => string 'attribute_set_id' (length=16)
'searchCriteria[filterGroups][0][filters][0][value]' => string '15,10,4,14,11,12,13,9' (length=21)
'searchCriteria[filterGroups][0][filters][0][condition_type]' => string 'in' (length=2)

Response from api:

'search_criteria' =>
array (size=1)
'filter_groups' =>
array (size=1)
0 =>
array (size=1)
'filters' =>
array (size=1)
0 =>
array (size=3)
'field' => string 'attribute_set_id' (length=16)
'value' => string '15,10,4,14,11,12,13,9' (length=21)
'condition_type' => string 'in' (length=2)

Query on the magento end:

SELECT main_table.* FROM eav_attribute_group AS main_table WHERE (attribute_set_id = '15,10,4,14,11,12,13,9')

Edit

After some debugging, I found the problem:
When the GroupRepository processes the collection, the process goes through:

vendor/magento/framework/Api/SearchCriteria/CollectionProcessor/FilterProcessor.php:addFilterGroupToCollection(...)

Here, on the line 66-70:

        $customFilter = $this->getCustomFilterForField($filter->getField());
        if ($customFilter) {
            $isApplied = $customFilter->apply($filter, $collection);
        }

There IS a custom field for my search (attribute_set_id), and the code goes in:

vendor/magento/module-eav/Model/Api/SearchCriteria/CollectionProcessor/FilterProcessor/AttributeGroupAttributeSetIdFilter.php

Here there is:

public function apply(Filter $filter, AbstractDb $collection)
{
    /** @var \Magento\Eav\Model\ResourceModel\Entity\Attribute\Group\Collection $collection */
    $collection->setAttributeSetFilter($filter->getValue());

    return true;
}

Which goes on:

vendor/magento/module-eav/Model/ResourceModel/Entity/Attribute/Group/Collection.php

public function setAttributeSetFilter($setId)
{
    $this->addFieldToFilter('attribute_set_id', ['eq' => $setId]);
    $this->setOrder('sort_order');
    return $this;
}

Here we can see that the eav_attribute_group collection does not even care about what we want us to do with the filter, it simply forces us on equal.

I don't want to believe that this was intentional, but instead, I believe that this was some old code that was done before the whole process-filter-thing, and it was forgotten.

Edit2

The same goes for all filters from the eav_attribute_group:

vendor/magento/module-eav/Model/Api/SearchCriteria/CollectionProcessor/FilterProcessor/AttributeGroupAttributeSetIdFilter.php

vendor/magento/module-eav/Model/Api/SearchCriteria/CollectionProcessor/FilterProcessor/AttributeGroupCodeFilter.php

vendor/magento/module-eav/Model/Api/SearchCriteria/CollectionProcessor/FilterProcessor/AttributeSetEntityTypeCodeFilter.php

All of them, don't bother to see what condition type we want to use on the collection.

Edit3

For fixes, I think that the best solution is to let the processor do its job. That means:

vendor/magento/module-eav/etc/di.xml

Remove the lines 184-154:

<virtualType name="Magento\Eav\Model\Api\SearchCriteria\CollectionProcessor\AttributeSetFilterProcessor" type="Magento\Framework\Api\SearchCriteria\CollectionProcessor\FilterProcessor">
    <arguments>
        <argument name="customFilters" xsi:type="array">
            <item name="entity_type_code" xsi:type="object">Magento\Eav\Model\Api\SearchCriteria\CollectionProcessor\FilterProcessor\AttributeSetEntityTypeCodeFilter</item>
        </argument>
    </arguments>
</virtualType>

Remove the lines: 169-179

<virtualType name="Magento\Eav\Model\Api\SearchCriteria\CollectionProcessor\AttributeGroupFilterProcessor" type="Magento\Framework\Api\SearchCriteria\CollectionProcessor\FilterProcessor">
    <arguments>
        <argument name="fieldMapping" xsi:type="array">
            <item name="attribute_group_code" xsi:type="string">main_table.attribute_group_code</item>
        </argument>
        <argument name="customFilters" xsi:type="array">
            <item name="attribute_set_id" xsi:type="object">Magento\Eav\Model\Api\SearchCriteria\CollectionProcessor\FilterProcessor\AttributeGroupAttributeSetIdFilter</item>
            <item name="attribute_group_code" xsi:type="object">Magento\Eav\Model\Api\SearchCriteria\CollectionProcessor\FilterProcessor\AttributeGroupCodeFilter</item>
        </argument>
    </arguments>
</virtualType>

And of course, remove the not used (anymore) code...

[pandeyVishakha]

Hey!!
I have the similar request
.../products?searchCriteria[filter_groups][0][filters][0][field]=category_id&searchCriteria[filter_groups][0][filters][0][value]=3&searchCriteria[filter_groups][0][filters][0][condition_type]=eq

am unable to make same request for my android app.
Any suggestions?