module_data sorting, filtering and pagination

Updated on 20-October-2016 at 10:16 AM

Business Catalyst End of life announcement - find out more details.

Introduction

When using module_data it is possible to sort, filter or paginate the data server-side before anything is sent to the browser. This allows fast and secure access to the data stored in the back-end.

Note: With the flexibility module_data offers when accessing the server data its syntax can get pretty complicated. To make module_data easier to work with we have introduced the BC API Discovery app . This is a free tool that enables you to visually create complex queries you can then copy-paste right in your site.

Let's take a look below at the methods you can use.

Remember: when using any of the methods below with module_data you will need to escape the curly and square brackets using a backslash character.

Resource ordering

Each collection can be sorted (ascending and descending) using the order query parameter. Below is an example where we will get the list of orders sorted in a descending order:

{module_data version="v3" resource="orders" order="-totalPrice" collection="myOrders" template=""}

Filtering

You can now get the data filtered by various parameters using the where parameter. In the examples below we will use the Orders resource:

{module_data version="v3" where="\{'creationDate':'2014-06-10'\}" resource="orders" skip="100" limit="50" collection="myOrders" template=""}
Query operation syntax Description Example
{
<serializedName>: <value>
}
Equals operation applied to the specified property.

Get all orders created on 10th of June 2014.

{
"creationDate": "2014-06-10"
}

sample where parameter:

where="\{'creationDate':'2014-06-10'\}"
{
<serializedName>: {
"$lt": <value>
}
}

 

 

Less than operation applied to the specified property.

Return all orders created before 10th of June 2014.

{ "creationDate": { "$lt": "2014-06-10" } }

sample where parameter:

where="\{'creationDate':\{'$lt':'2014-06-10'\}\}"
{
<serializedName>: {
"$lte": <value>
}
}
Less than or equal operation applied to the specified property.

Return all orders created until 11th of June 2014.

{
"creationDate": {
"$lte": "2014-06-10"
}
}

sample where parameter:

where="\{'creationDate':\{'$lte':'2014-06-10'\}\}"
{
<serializedName>: {
"$gt": <value>
}
}
Greater than operation applied to the specified property.

Get all orders with a total price greater than 99.9.

{
"totalPrice": {
"$gt": 99.9
}
}

sample where parameter:

where="\{'totalPrice':\{'$gt':99.9\}\}"
{
<serializedName>: {
"$gte": <value>
}
}
Greater than or equal operation applied to the specified property.

Get all orders with a total price greater than or equal with 99.9.

{
"totalPrice": {
"$gte": 99.9
}
}

sample where parameter:

where="\{'totalPrice':\{'$gte':99.9\}\}"
{
<serializedName>: {
"$ne": <value>
}
}
Not equal operation applied to the specified property.

Get all orders where total price is different than 99.9.

{
"totalPrice": {
"$ne": 99.9
}
}

sample where parameter:

where=\{"totalPrice":\{"$ne":99.9\}\}"
{
<serializedName>: {
"$in": <value>
}
}
In (contain) operation applied to the specified property.

Get all orders where total price is 99.9 or 22.5 or 0.99.

{
"totalPrice": {
"$in": [
99.9,
22.5,
0.99
]
}
}

sample where parameter:

where="\{'totalPrice':\{'$in':\[99.9,22.5,0.99\]\}\}"
{
<serializedName>: {
"$nin": <value>
}
}
Not in (not contained) operation applied to the specified property.

Get all orders where total price is different than 99.9 and 22.5 and 0.99.

{ "totalPrice": { "$nin": [ 99.9, 22.5, 0.99 ] } }

sample where parameter:

 where="\{'totalPrice':\{'$nin':\[99.9,22.5,0.99\]\}\}"
{
"$or": [
<filter 1>,
<filter 2>
]
}
Or operation applied to a list of conditions. It is the equivalent of or statement from SQL.

Get all orders where total price is 99.9 or was created on 10th of June or 11th of June 2014.

{
"$or": [
{
"totalPrice": 99.9
},
{
"creationDate": {
"$in": [
"2014-06-10",
"2014-06-11"
]
}
}
]
}

sample where parameter:

where="\{'$or':\[\{'totalPrice':99.9\},\{'creationDate':\{'$in':\['2014-06-10','2014-06-11'\]\}\}\]\}"
{
"$and": [
<filter 1>,
<filter 2>
]
}
And operation applied to a list of conditions. It is equivelent of AND statement from SQL.

Get all orders where total price is between 99.99 and 199.99.

{
"$and": [
{"totalPrice": {"$gt": 99.99}},
{"totalPrice": {"$lt": 199.99}}
]
}

sample where parameter:

where="\{'$and': \[\{'totalPrice': \{'$gt': 99.99\}\},\{'totalPrice': \{'$lt': 199.99\}\}\]\}"
{
<serializedName>: {
"$contains": <string value>
}
}
Contains operation applicable only for string fields. It detects if serializedName field contains requested string value.

Get all orders where order description contains sample order string.

{
"description": {
"$contains": "sample order"
}
}

sample where parameter:

where="\{'description':\{'$contains':'sampleorder'\}\}"
{
<serializedName>: {
"$beginsWith": <string value>
}
}
Begins with operation applicable only for string fields. It detects if serializedName starts with requested string value.

Get all orders where order description starts with sample.

{
"description": {
"$beginsWith": "sample"
}
}

sample where parameter:

where="\{'description':\{'$beginsWith':'sample'\}\}"
{
<serializedMappedResourceName.serializedName>:  {
<operator>: <value>
}
}
{
<serializedMappedResourceName.serializedName>:<value>
}
Operation applied to the specified property of subresource. The current version of web resources supports only one level of nesting

Lists all the products that have a price with a rate greater than 5. Prices is a subresource for orders resource.

{
"prices.rate" : {
"$gt" : 5
}
}

sample where parameter:

where="\{'prices.rate':\{'$gt':5\}\}"

or

Lists all customers which have email1 and it's value contains '@gmail.com'. Email1 is a subfield for customers resource.

{
"email1.value" : {
"$contains" : "@gmail.com"
}
}

sample where parameter:

where="\{'email1.value':\{'$contains':'@gmail.com'\}\}"

Resource pagination

Each resource can be paginated using the skip and limit query parameters. These parameters are optional, if you do not specify them their default values are skip=0 and limit=10.

Do note the limit parameter cannot be larger than 500 module_data output is limited to a maximum of 500 items per request.

In the example below we get a batch 50 orders and skip the first 100:

 {module_data version="v3" resource="orders" skip="100" limit="50" collection="myOrders" template=""} 

Examples

Using the skip and limit parameters you can paginate the output of module_data using only Liquid code (no javascript needed). Take a look at the Pagination for module_data tutorial for more information.