List Load Query Overview

---

Anytime you want to pull a list of data from the API including Orders, Customers, Products, Categories, etc you will be using a List Load Query Function. These functions are specifically designed to give you maximum control over which data is returned and how much is returned. There are advanced filters which give you granular control over the response. If you would like Miva to keep track of new and updated changes to orders please see Order Workflows, Product Workflows and Customer Workflows.

Filter List Parameters

All xxxList_Load_Query functions accept the following common parameters:

Parameter	Description
Filter	Optional search/display filter that is applied to results. See below.
Sort	Column name to sort the results, may be preceded by “-” for a descending sort.
Count	Number of records to return, if 0, return all records (defaults to 0)
Offset	0-based offset of the first record to return (defaults to 0)

Filter

Filter is an array of filter structures. The required fields for a filter structure depend on its “name” property.

{
   "Filter":[
      {
         "name":"search",
         "value":[
            {
               "field":"field_code",
               "operator":"operator",
               "value":"value"
            }
         ]
      }
   ]
}

Operators

Operator	Description
EQ	equals value (generally case insensitive)
GT	is greater than to value
GE	is greater than or equal to value
LT	is less than value
LE	is less than or equal to value
CO	contains value
NC	does not contain value
LIKE	matches value using SQL LIKE semantics
NOTLIKE	does not match value using SQL LIKE semantics
NE	is not equal to value
TRUE	is true
FALSE	is false
NULL	is null
IN	is equal to one of a set of comma-separated values taken from value
NOT_IN	is not equal to any of the set of comma-separated values taken from value

AND

To perform an AND search, use multiple top-level search filters:

"Filter":
[
    {
        "name": "search",
        "value":
        [
            {
                "field": "ship_fname",
                "operator": "EQ",
                "value": "Jonathan"
            }
        ]
    },
    {
        "name": "search",
        "value":
        [
            {
                "field": "ship_lname",
                "operator": "EQ",
                "value": "Order"
            }
        ]
    }
]

OR

To perform an OR search, use multiple values inside a single search filter:

"Filter":
[
    {
        "name": "search",
        "value":
        [
            {
                "field": "ship_fname",
                "operator": "EQ",
                "value": "Jonathan"
            },
            {
                "field": "ship_fname",
                "operator": "EQ",
                "value": "John"
            }
        ]
    }
]

SUBWHERE

Parenthetical comparisons can be accomplished using the SUBWHERE operator and special field names search, search_AND and search_OR:

"Filter":
[
    {
        "name": "search",
        "value":
        [
            {
                "field": "ship_lname",
                "operator": "EQ",
                "value": "Jones"
            },
            {
                "field": "search_OR",
                "operator": "SUBWHERE",
                "value":
                [
                    {
                        "field": "ship_fname",
                        "operator": "EQ",
                        "value": "John"
                    },
                    {
                        "field": "ship_lname",
                        "operator": "EQ",
                        "value": "Smith"
                    }
                ]
            }
        ]
    }
]

A field of search or search_AND combines its child expressions with OR and the overall results with its parent using AND. A field of search_OR combines its child expressions with AND and the overall results with its parent using OR.

In the example above, the resulting WHERE clause is:

WHERE ship_lname EQ 'Jones' OR ( ship_fname EQ 'John' AND ship_lname EQ 'Smith' )

ondemandcolumns

Many lists return certain columns only when they are explicitly requested. This may be because the column is expensive to load (payment module) or includes a large amount of data that is not always needed (custom fields, product descriptions). These fields can be requested using the ondemandcolumns

{
    "name": "ondemandcolumns",
    "value": [ "field", "field", "field", ... ]
}

CustomField_Values

Specific on-demand columns will be called out on a function by function basis below. Product, category, customer and order functions all support custom fields, which are retrieved in the following way:

Value	Description
"CustomField_Values:<module code>:<field code>"	Includes that specific field in the output
"CustomField_Values:<module code>:*"	Includes all fields for the specified module in the output
"CustomField_Values:*"	Includes all custom fields in the output

Sample Response

All xxxList_Load_Query functions output data (on success) in the following format:

{
    "success": true,
    "data":
    {
        "total_count":  <numeric total count of records matching any input filters>,
        "start_offset": <numeric starting offset of the records>,
        "data":
        [
            { record },
            { record },
            ...
        ]
    }
}