Magento 2 API: Filter orders by extension attribute

Question

I have an extension with an attribute assigned to order items called warehouse.
I can see this attribute under the property "extension_attributes".
I need to filter orders based on this attribute. I can't find any documentation on how to do this.

Example API response snippet:

[items] => Array(
    [0] => Array(
        [extension_attributes] => Array(
            [warehouse] => MyWarehouse
        )
    )
)

The following filter generates an internal error:

searchCriteria[filter_groups][2][filters][0][field]=warehouse,searchCriteria[filter_groups][2][filters][0][value]=mywarehouse,searchCriteria[filter_groups][2][filters][0][condition_type]=eq

Any suggestions on how I can filter orders using the warehouse attribute via the API?

- Additional point on this question:

In this case, if we will follow Fooman blog.Then can we filter the collection by the extension* attribute?

• Collection filter by this extension Attribute Should be run Fast?
• If we use custom db table for save this extension attribute table then how can filter? Should it work magento frontend and backend also.
• where in need do modification and which class need to add or any reference blog ?

Wait for good and describe/brief answer on this points?

Answer 1

Firstly, your query string contains commas (,) when it should contain ampersands (&):

/rest/V1/orders?searchCriteria[filter_groups][0][filters][0][field]=warehouse&searchCriteria[filter_groups][0][filters][0][value]=MyWarehouse&searchCriteria[filter_groups][0][filters][0][condition_type]=eq

Secondly, as other answers have mentioned in more detail, the query will only work if the attribute on which you're filtering is present as a column in the database table(s) being filtered.

Answer 2

The searchCriteria does not filter results from API response instead from the db/collection instead, if custom attributes in extension_attribute are not available in the db as a column in the table from where the data is being fetched it will not come in response.

For eg. I make following API call -

 http://example.com/rest/V1/orders?searchCriteria[filter_groups][0][filters][0][field]=random&searchCriteria[filter_groups][0][filters][0][value]=random&searchCriteria[filter_groups][0][filters][0][condition_type]=eq

Then as there is no column called random in sales_order table (from which the API is fetching response ) , I get following error ;

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'random' in 'where clause', query was: SELECT main_table.* FROM sales_order AS main_table WHERE ((random = 'random'))

So, if you have to call your custom data in the API you have to save in respective table in a new column, then you can call it normally using column name as the fiter field and value as your desired value, as below ;

 http://example.com/rest/V1/orders?searchCriteria[filter_groups][0][filters][0][field]=column_name&searchCriteria[filter_groups][0][filters][0][value]=desired_value&searchCriteria[filter_groups][0][filters][0][condition_type]=eq

I have tested it myself, I hope same works for you too.

Answer 3

At the moment of writing this (Magento 2.2), I don't think there's a way to filter on extension attributes. Although they're present in the underlying collection's select if you define a <join> in extension_attributes.xml, it seems like you can't filter on it.

It's up to the extension developer to provide a way to filter on values of an extension attribute. You can see an example in the Amazon payments module: https://github.com/amzn/amazon-payments-magento-2-plugin/blob/master/Plugin/CustomerCollection.php#L59

In that plugin, you can see how they expect the table to be already joined (as it's defined in extension_attributes.xml), but apply the filter manually.

Answer 4

@AmitBera I am not 100% sure for this and it doesn't give your all question answer but I think first you need to first use JoinProcessor to search criteria and then use filer. you can check more how to do it on this http://devdocs.magento.com/guides/v2.2/extension-dev-guide/searching-with-repositories.html

Answer 5

I came across a requirement where i need to add new table with few fields and they need to work as an extensible attribute for sales order. Whenever new sale order is created a record is added in the table with fields like order id and integration status. I implemented the extension attribute successfully and this attribute was available in REST API and we could update them through API as well. However, i could not use the new field from my table in the API search. This will give column not found issue from sales order table. I struggled a lot to use my extension attribute field in API search.

My data in table was as below:

Similarly, data in API response was as below for an example order:

"integrations_sales_order": {
                "order_id": 2776573,
                "integration_status": "Created",
                "integration_status_updated_at": null,
                "integration_fail_message": null
            }

However, when i try to use integration status in the API request as a search criteria then it did not work. my search criteria was as below

{{url}}/rest/all/V1/orders?searchCriteria[filter_groups][2][filters][0][field]=integration_status&searchCriteria[filter_groups][2][filters][0][value]=Created&searchCriteria[filter_groups][2][filters][0][condition_type]=eq

But i could make it work by changing the search criteria as below then it works:

{{url}}/rest/all/V1/orders?searchCriteria[filter_groups][2][filters][0][field]=extension_attribute_integrations_sales_order.integration_status&searchCriteria[filter_groups][2][filters][0][value]=Created&searchCriteria[filter_groups][2][filters][0][condition_type]=eq

Please note that extension_attribute_integrations_sales_order is the attribute code you have used from extension_attributes.xml file.

Filter in my API call will show up in response as below:

{
            "filters": [
                {
                    "field": "extension_attribute_integrations_sales_order.integration_status",
                    "value": "Created",
                    "condition_type": "eq"
                }
            ]
        }

This is how I could use my extension attribute in the search criteria for API call for Sales order. Hope this will help some one. Good luck.