Salesforce Commerce Cloud/Demandware - OCAPI query orders by date range

Question

I am trying to query the Demandware (SFCC) api to extract orders using a date range. The POST to orders_search works but it seems terribly inefficient. First I pull ALL data and then I filter the results.

I would like to simply query for the date ranges, but I cannot figure out how to do that. Help.

{ 
    query : {
        filtered_query: {
            query: {
                term_query: { fields: ["creation_date"], operator: "is_not_null" } 
            },
       filter: {
                range_filter: {
                    field: "creation_date",
                    from: "2017-08-12T00:00:00.000Z",
                    to: "2017-08-13T00:00:00.000Z",
                    from_inclusive: true
                }
            }
        }
    }
}

EDIT: While I solved the initial question, this ends up being more complicated because the service only allows 200 responses at a time. So first you have to make a request to find out how many results there are, then call the service multiple times to get data. Below is the code as used with C#. The date ranges are passed in as variables.

                var m_payload_count = "{ query : { filtered_query: { query: { term_query: { fields: [\"creation_date\"], operator: \"is_not_null\" } }, filter: { range_filter: { field: \"creation_date\", from: \"" + strBeginDateTime + "\", to: \"" + strEndDateTime + "\", from_inclusive: true } } } } }";

                // can only get 200 responses at a a time so make a basic call first to get the total

                m_response_count = apiClient.UploadString(m_url, m_payload_count);

                dynamic m_jsoncount = JsonConvert.DeserializeObject(m_response_count);

                // determine number of times of full api call, rounding up.  substitute begin/end dates and beginning count placeholder 

                int m_records = m_jsoncount["total"];
                int m_numbercalls = (m_records + (200 - 1)) / 200;

                dynamic m_json;

                for (int i = 0; i < m_numbercalls; i++)
                {
                    var m_payload = "{ query : { filtered_query: { query: { term_query: { fields: [\"creation_date\"], operator: \"is_not_null\" } }, filter: { range_filter: { field: \"creation_date\", from: \"" + strBeginDateTime + "\", to: \"" + strEndDateTime + "\", from_inclusive: true } } } }, select: \"(**)\", start: " + i * 200 + ", count: 200 }";

                    m_response = apiClient.UploadString(m_url, m_payload);

                    m_json = JsonConvert.DeserializeObject(m_response);

The remainder of the code is omitted, but it is essentially iterating through the m_json object.

May I ask why you aren't asking for the maintainers of the SFCC code to create a batch export of these orders for you? — sholsinger, Jul 01 '18 at 23:46
Because it is part of an automated process and batch export sounds terribly manual. — smoore4, Jul 03 '18 at 01:41
It can be created as a recurring job and therefore wouldn't be manual at all. — sholsinger, Oct 14 '19 at 21:34

score 2 · Answer 1 · answered Oct 23 '17 at 14:15

2

{ 
   "query" :
   { 
      "filtered_query": {
            "query": { match_all_query: {} },
            "filter": {
                "range_filter": {
                    "field": "creation_date",
                    "from": "2016-01-01T00:00:00.000Z"
                }
            }
        }
   },
   "select" : "(**)"
}

answered Oct 23 '17 at 14:15

iJarvis

21
2

2

please put also a brief explanation of your answer, for pointing out why you're proposing this as a solution – sissy Oct 23 '17 at 14:37

Salesforce Commerce Cloud/Demandware - OCAPI query orders by date range

1 Answers1