Filtering, Sorting & Paging

Both the HasOffers Network API and Affiliate API use the same filtering, sorting, and paging fields.

Filtering

Parameters of the Filter type allow the caller of an API method to restrict the results returned by the method to those which meet certain criteria. Fields that can be filtered are limited to those of the model whose type the function returns.

The default operator for filters is an equality filter that limits results to those models with a field matching a single value or one of a list of values. For example, if you wish to retrieve Offers whose status is "active" and whose currency is "USD" the following filters could be used:

&filters[status]=active&filters[currency]=USD

Since the default operator supports an array of values to achieve the effect of "is one of", we can update our example to return Offers whose status is "paused" in addition to those which are "active" by using the parameter array notation "[]" to denote a list of statuses:

&filters[status][]=active&filters[status][]=paused&filters[currency]=USD

The default behavior for filters is to "AND" the conditions together, so that results must meet the criteria of each filter in order to match. It is also possible to construct a filter that "OR"s the conditions together. For example, if we wish to retrieve Offers whose status is "active" OR whose currency is "USD" we could construct a filter by prefixing each field with an "[OR]" block:

&filters[OR][status]=active&filters[OR][currency]=USD

Note on OR with same field: The OR feature works only with searching on different fields. If attempting to use OR with the same field, only the final value is used to search. "&filters[OR][id]=2&filters[OR][id]=4" would only return the record of ID 2 (if any), not both ID 2 and ID 4. Use multiple API calls to achieve the desired result.

Other Filter Operators

In addition to the default equality operator, following are additional comparison operators that can be used to limit results with a filter. NOTE: They are limited to comparing against a single value, rather than a list like the default operator.

  • NOT_EQUAL_TO - Finds objects which do not have the provided value. Example: Retrieve Offers whose status is not equal to "active"
    &filters[status][NOT_EQUAL_TO]=active
  • LESS_THAN - Finds objects which have a value less than the provided value. Example: Retrieve Offers whose id is less than 25
    &filters[id][LESS_THAN]=25
  • LESS_THAN_OR_EQUAL_TO - Finds objects which have a value less than or equal to the provided value. Example: Retrieve Offers whose id is less than or equal to 25
    &filters[id][LESS_THAN_OR_EQUAL_TO]=25
  • GREATER_THAN - Finds objects which have a value greater than the provided value. Example: Retrieve Offers whose id is greater 25
    &filters[id][GREATER_THAN]=25
  • GREATER_THAN_OR_EQUAL_TO - Finds objects which have a value greater than or equal to the provided value. Example: Retrieve Offers whose id is greater than or equal to 25
    &filters[id][GREATER_THAN_OR_EQUAL_TO]=25
  • LIKE - Returns objects which have a value that matches the provided string, using case-insensitive search. The '%' character is a wildcard: use at the beginning of the string to find values ending in that string, at the end of the string to find values beginning with that string, or in the middle to find values bookended by two parts of the string. Example: Retrieve Offers whose names begin with "Atomic Tilt", such as "Atomic Tilt (iPhone/US)" or "Atomic Tilt (Android/SK)"
    &filters[name][LIKE]=Atomic Tilt%

    Example: Retrieve Offers whose names have "gift card" in them, such as "$1,500 Mondo Burger Gift Card" or "Mother's Day Flowers $500 Gift Card (Incent)"

    &filters[name][LIKE]=%gift card%

    Important: If you don't use a wildcard, the call only returns values that match the provided string, though still as a case-insensitive search.

  • NOT_LIKE - Returns objects which have a value that does not match the provided string. The '%' character is a wildcard, with the same function as in the LIKE operator. Example: Retrieve Offers whose name does not contain "Free"
    &filters[name][NOT_LIKE]=%Free%
  • NULL - Finds objects which have a value that is NULL. This is not the same as an empty string; you should use the EQUAL_TO conditional to find objects whose value is an empty string. Example: Retrieve Offers whose description is NULL
    &filters[description][NULL]=1
  • NOT_NULL - Finds objects which have a value that is NOT NULL. This is not the same as an empty string; you should use the NOT_EQUAL_TO conditional to find objects whose value is not an empty string. Example: Retrieve Offers whose description is NOT NULL.
    &filters[description][NOT_NULL]=1
  • TRUE - Finds objects which have a value that is the boolean true. Example: Retrieve Offers which are private
    &filters[is_private][TRUE]=1
  • FALSE - Finds objects which have a value that is the boolean false. Example: Retrieve Offers which are not private
    &filters[is_private][FALSE]=1

Report Filtering

Parameters of the Report Filter type are similar to the Filter type except that they are used exclusively when calling functions that generate reports. The format for a Report Filter is slightly different than that of normal Filter parameters in a few ways. The conditional must always be specified using a "conditional" index. For conditionals that take value parameters, and the value(s) are specified as a sibling of the conditional, indexed by the string "values". Additionally, some of the available conditionals differ between the two filter types. Report Filters do not support the "OR" construct of regular filters; instead, all provided filters are "AND"ed together.

The "EQUAL_TO" conditional behaves like an IN clause, allowing you to filter for data where the field matches one of the provided values. For example, if you are applying filters to a Conversion Report and you wish to restrict the results to conversions having either an "approved" or a "pending" status you would format the report filters as follows:

&filters[status][conditional]=EQUAL_TO&filters[status][values][0]=active&filters[status][values][1]=pending

In addition to the "EQUAL_TO" operator, following are additional comparison operators that can be used to limit results with a Report Filter.

  • BETWEEN - Finds objects which have a field that is in a certain range, inclusive. Example: Retrieve conversions whose date is greater than or equal to 2013-12-29 and less than or equal to 2014-01-03
    &filters[Stat.date][conditional]=BETWEEN&filters[Stat.date][values][0]=2013-12-29&filters[Stat.date][values][1]=2014-01-03
  • NOT_EQUAL_TO - Finds objects which do not have the provided value. Example: Retrieve conversions whose status is not equal to "approved"
    &filters[Stat.conversion_status][conditional]=NOT_EQUAL_TO&filters[Stat.conversion_status][values]=approved
  • LESS_THAN - Finds objects which have a value less than the provided value. Example: Retrieve conversions whose id is less than 25
    &filters[Stat.id][conditional]=LESS_THAN&filters[Stat.id][values]=25
  • LESS_THAN_OR_EQUAL_TO - Finds objects which have a value less than or equal to the provided value. Example: Retrieve conversions whose id is less than or equal to 25
    &filters[Stat.id][conditional]=LESS_THAN_OR_EQUAL_TO&filters[Stat.id][values]=25
  • GREATER_THAN - Finds objects which have a value greater than the provided value. Example: Retrieve conversions whose id is greater 25
    &filters[Stat.id][conditional]=GREATER_THAN&filters[Stat.id][values]=25
  • GREATER_THAN_OR_EQUAL_TO - Finds objects which have a value greater than or equal to the provided value. Example: Retrieve conversions whose id is greater than or equal to 25
    &filters[Stat.id][conditional]=GREATER_THAN_OR_EQUAL_TO&filters[Stat.id][values]=25
  • LIKE - Returns objects which have a value that matches the provided string, using case-insensitive search. The '%' character is a wildcard: use at the beginning of the string to find values ending in that string, at the end of the string to find values beginning with that string, or in the middle to find values bookended by two parts of the string. Example: Retrieve Conversions for Offers whose names begin with "Atomic Tilt", such as "Atomic Tilt (iPhone/US)" or "Atomic Tilt (Android/SK)"
    &filters[Offer.name][conditional]=LIKE&[Offer.name][values]=Atomic Tilt%

    Example: Retrieve Conversions for Offers whose names have "gift card" in them, such as "$1,500 Mondo Burger Gift Card" or "Mother's Day Flowers $500 Gift Card (Incent)"

    &filters[Offer.name][conditional]=LIKE&[Offer.name][values]=%gift card%

    Important: If you don't use a wildcard, the call only returns values that match the provided string, though still as a case-insensitive search.

  • NOT_LIKE - Returns objects which have a value that does not match the provided string. The '%' character is a wildcard, with the same function as in the LIKE operator. Example: Retrieve Conversions for Offers whose names do not contain "Free"
    &filters[Offer.name][conditional]=NOT_LIKE&[Offer.name][values]=%Free%
  • NULL - Finds objects which have a value that is NULL. This is not the same as an empty string; you should use the EQUAL_TO conditional to find objects whose value is an empty string. Notice no "values" are passed for this conditional. Example: Retrieve conversions whose ip is NULL
    &filters[Stat.ip][conditional]=NULL
  • NOT_NULL - Finds objects which have a value that is NOT NULL. This is not the same as an empty string; you should use the NOT_EQUAL_TO conditional to find objects whose value is an not an empty string. Notice no "values" are passed for this conditional. Example: Retrieve conversions whose ip is NOT NULL.
    &filters[Stat.ip][conditional]=NOT_NULL

Note: Since the Report Filter does not support "TRUE" or "FALSE" conditionals, the "EQUAL_TO" or "NOT_EQUAL_TO" conditionals should be used instead when filtering on boolean-typed report fields. A value of "1" corresponds to boolean true, while a value of "0" corresponds to boolean false.

Sorting

Parameters of the Sort type used on get/find type functions to order the results returned by the method. The fields that can be sorted are limited to those of the model whose type the function returns. The format of a sort parameter is an object having keys identifying the model field name and values specifying the direction of either "asc" or "desc" to order the results in ascending order or descending order, respectively.

For example, an API method that has a "Sort" parameter named "sort" that returns Offer model objects could be called in the following way to sort the results by Offer.status ascending and by Offer.id descending within each status.:

&sort[status]=asc&sort[id]=desc

Paging

API calls with limit and page parameters allow you to control the amount of data returned in that call. To handle paging, include both parameters: limit for the size of the result set and page for which result set to return.

limit must be an integer, and must be supplied to return paged results. page must be an integer, and defaults to 1 if omitted. For example, to return the first 50 results you would append the following to your call:

&limit=50

To return the second set of 50 results, append:

&limit=50&page=2

Note: If you supply only the page parameter, you may receive unexpected results. Do not supply page without also supplying limit.

When using paging, the data portion of the response object changes to contain the paging information and subset of records. The structure changes to:

"data": {
    "page": "1",
    "current": 0,
    "count": 314,
    "pageCount": 7,
    "data": {
        . . .
    }
}

The fields contained:

  • page: the page of the result set returned
  • current: the index of the first row on this page; this is based on the total result set given the parameters supplied, not the first record’s ID
  • count: the total number of records available given the parameters supplied
  • pageCount: the total number of pages given the parameters supplied
  • data: the result set of that page, structured as the data object normally is without paging active

Note: In some methods, some of these fields may not appear.

Have a Question? Please contact [email protected] for technical support.