You might be familiar already with a previous post on Use Liquid to Return JSON or XML but what if you want efficient paging included in your scenario. I have had a couple of queries of how to do this with large data sets so that the fetchxml limit of 5000 results can be exceeded or results returned in an efficient manner as possible. Fetchxml has a solution with the paging cookie and the portal natively uses this in all its entity view type queries, but you can use it as well in your custom liquid fetchxml!
Using the same method in the previous post Use Liquid to Return JSON or XML we will setup a web template that makes the fetchxml query and instead of returning HTML we will set the MIME type to application/json.
This is the stubbed in liquid code we are starting with in our web template:
{% fetchxml feed %}
<fetch version="1.0" mapping="logical">
<entity name="contact">
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="contactid" />
<order attribute="lastname" descending="false" />
</entity>
</fetch>
{% endfetchxml %}{
"results": [
{% for item in feed.results.entities %}
{
"firstname": "{{ item.firstname }}",
"lastname": "{{ item.lastname }}",
"contactid": "{{ item.contactid }}"
}{% unless forloop.last %},{% endunless %}
{% endfor %}
]
}
Here we are just making a simple query using the liquid fetchxml tag and returning a list of all contacts (up to 5000 with the fetchxml limit).
The problem using this just like this is that it is not getting back a limited number of results and there is no paging of records involved. To make paging efficient on large date sets Microsoft has included what is called a paging cookie in fetchxml so that you can get faster application performance. Read more about the fetchxml paging cookie on the Microsoft Docs site – Page Large Result Sets with FetchXML.
The paging_cookie property should be used with the more_results boolean property both available the results object of a fetchxml query. The code below now has updated to include both of those properties in the highlighted lines in JSON returned by the endpoint.
{% fetchxml feed %}
<fetch version="1.0" mapping="logical">
<entity name="contact">
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="contactid" />
<order attribute="lastname" descending="false" />
</entity>
</fetch>
{% endfetchxml %}{
"morerecords": {{ feed.results.more_records }},
"paging-cookie": "{{ feed.results.paging_cookie }}",
"results": [
{% for item in feed.results.entities %}
{
"firstname": "{{ item.firstname }}",
"lastname": "{{ item.lastname }}",
"contactid": "{{ item.contactid }}"
}{% unless forloop.last %},{% endunless %}
{% endfor %}
]
}
Now you can make logic decisions if to get more records based on the value of more_records and use the value of paging_cookie to provide to the fetchxml.
At this point we are going to want to include a page size or returned record count so that we aren’t getting all the records at once (to a max of 5000). To do this you want to add the count attribute to the opening fetch with a integer value as to the number of records in the page of results.
<fetch version="1.0" mapping="logical" count="10">
Now we have setup the returned JSON with all the necessary details for the UI to make choices to get more data. Now we need further enhance the liquid logic to allow the UI to pass the endpoint parameters for the page and paging cookie so you can include those in the liquid fetchxml query. To do this we need to collect both of those as query string parameters and then add them to the fetchxml query if they exist.
For the paging cookie we want some logic so that the cookie is only included when it is passed as a parameter to the endpoint. Adding the following code to the top of your web template will check the request parameters for the key 'paging-cookie' and if it has a value then setup the XML statement attribute with the value of the query string parameter.
{% assign pagingCookie = request.params['paging-cookie'] %}
{% if pagingCookie %}
{% assign pagingCookie = ' paging-cookie="{{ pagingCookie }}"' | liquid %}
{% endif %}
Note we also have a filter at the end of the paging cookie variable assignment, the liquid filter so that liquid is executed in the assignment of the variable.
With the paging cookie and the page parameter we want to add those to the opening fetch xml tag.
<fetch version="1.0" mapping="logical"{{ pagingCookie }} page="{{ request.params['page'] | default:1 }}" count="10">
We have also applied the default filter on the request.params['page'] so that when it isn’t included as a query string parameter that it assumes you want the first page. The first page won’t also require a paging cookie.
Everything looks pretty good at this point with the exception of one gotcha. The paging cookie value is going to be XML. XML is not going to go well into a query string parameter because it includes illegal URL characters. We could solve this with the UI layer itself and translate or encode the XML as URL safe but that would require logic at the UI. We can actually encode the XML on the liquid end so that the data we pass to whatever the UI is doesn’t need to worry about any translating, just passing that same data back.
We are going to add another property to the return JSON that is the encoded version of the paging cookie. With this we need to use a liquid filter called url_escape to encode all the XML to URL friendly characters.
"paging-cookie-encoded": "{{ feed.results.paging_cookie | escape | url_escape }}",
For example the original XML paging cookie of:
<cookie page="1"><lastname last="Vermander" first="Administrator" /><contactid last="{D77E163F-4B77-E811-A960-000D3A1CA7D6}" first="{7469FD95-C0BD-4236-90BF-1D1100291DF5}" /></cookie>
Becomes:
%26lt%3Bcookie+page%3D%26quot%3B1%26quot%3B%26gt%3B%26lt%3Blastname+last%3D%26quot%3BVermander%26quot%3B+first%3D%26quot%3BAdministrator%26quot%3B+%2F%26gt%3B%26lt%3Bcontactid+last%3D%26quot%3B%7BD77E163F-4B77-E811-A960-000D3A1CA7D6%7D%26quot%3B+first%3D%26quot%3B%7B7469FD95-C0BD-4236-90BF-1D1100291DF5%7D%26quot%3B+%2F%26gt%3B%26lt%3B%2Fcookie%26gt%3B
With this you now have a JSON endpoint that supports paging with the fetchxml paging cookie and can now efficiently return any number of records with various page sizes in your portal implementations. I do always recommend you keep your page sizes reasonable for performance considerations.
Below is the completed web template example with paging cookie included in the input of the endpoint and output of JSON.
{% assign pagingCookie = request.params['paging-cookie'] %}
{% if pagingCookie %}
{% assign pagingCookie = ' paging-cookie="{{ pagingCookie }}"' | liquid %}
{% endif %}
{% fetchxml feed %}
<fetch version="1.0" mapping="logical"{{ pagingCookie }} page="{{ request.params['page'] | default:1 }}" count="10">
<entity name="contact">
<attribute name="firstname" />
<attribute name="lastname" />
<attribute name="contactid" />
<order attribute="lastname" descending="false" />
</entity>
</fetch>
{% endfetchxml %}{
"morerecords": {{ feed.results.more_records }},
"paging-cookie": "{{ feed.results.paging_cookie }}",
"paging-cookie-encoded": "{{ feed.results.paging_cookie | escape | url_escape }}",
"page": {{ request.params['page'] | default: 0 }},
"results": [
{% for item in feed.results.entities %}
{
"firstname": "{{ item.firstname }}",
"lastname": "{{ item.lastname }}",
"contactid": "{{ item.contactid }}"
}{% unless forloop.last %},{% endunless %}
{% endfor %}
]
}
You can test your new service now without parameters and with parameters. Your query for the first page would follow this format, the page parameter being optional.
https://[portalname].microsoftcrmportals.com/[json-endpoint]/?page=1
Queries for next or previous pages should include the encoded version of the paging cookie.
https://[portalname].microsoftcrmportals.com/[json-endpoint]/?page=2&paging-cookie=%26lt%3Bcookie+page%3D%26quot%3B1%26quot%3B%26gt%3B%26lt%3Blastname+last%3D%26quot%3BVermander%26quot%3B+first%3D%26quot%3BAdministrator%26quot%3B+%2F%26gt%3B%26lt%3Bcontactid+last%3D%26quot%3B%7BD77E163F-4B77-E811-A960-000D3A1CA7D6%7D%26quot%3B+first%3D%26quot%3B%7B7469FD95-C0BD-4236-90BF-1D1100291DF5%7D%26quot%3B+%2F%26gt%3B%26lt%3B%2Fcookie%26gt%3B
Colin Vermander 