Supported Lookup Functions

Arithmetic Lookups

gt – Greater than

Example: Filtering sales order detail(s) for only those with an ordered quantity.

…/order_dtl/?ord_qty__gt=0

gte – Greater than or equal to

Example: Filtering sales order detail(s) for only those with an ordered quantity.

…/order_dtl/?ord_qty__gte=1

lt – Less than

Example: Filtering sales order detail(s) for only those with ordered quantity below 10.

…/order_dtl/?ord_qty__lt=10

lte – Less than or equal to

Example: Filtering sales order detail(s) for those with ordered quantity at or below 10.

…/order_dtl/?ord_qty__lte=10

Text Match Lookups

contains/icontains – Text contains substring

Example: Filtering sales order(s) for orders with “FOO” in the order_nbr field.

…/order_hdr/?order_nbr__contains=FOO

Example: Same as previous example, but ignore case.

…/order_hdr/?order_nbr__icontains=FOO

exact/iexact – Text exactly matches

Example: Match sales order(s) exactly on the order number.

…/order_hdr/?order_nbr__exact=ORDER001

Note: “Exact” is not typically needed. The above filter condition does not require the exact lookup since this is automatically implied by the exact operator (“=”).

The query string can be simplified to:

…/order_hdr/?order_nbr=ORDER001

“iexact”, on the other hand, is a useful tool when you need to do an exact match, but ignore letter casing:

…/order_hdr/?order_nbr__iexact=OrDeR001

startwith/istartswith – Text starts with

Example: Filtering sales order(s) for only those whose order_nbr starts with “ORD”:

…/order_hdr/?order_nbr__startswith=ORD

endswith/iendswith – Text ends with

Example: Filtering sales order(s) for only those whose order_nbr ends with “001”:

…/order_hdr/?order_nbr__endswith=001

Temporal (Date/Time) Lookups

The following temporal functions may only be used on date, time, and/or date-time data. Consider the “order_hdr” entity’s “order_shipped_ts” date-time field with a value “2018-09-17T20:30:59”:

year – Match on a date’s year (date or date-time).

…/order_hdr/?order_shipped_ts__year=2018

month – Match on a date’s month (date or date-time).

…/order_hdr/?order_shipped_ts__month=09

week_day – Match on a date’s day of the week (date or date-time).

Takes an integer value representing the day of week from 1 (Sunday) to 7 (Saturday).

…/order_hdr/?order_shipped_ts__week_day=2

day – Match on a date’s day (date or date-time).

…/order_hdr/?order_shipped_ts__day=17

hour – Match on a date’s hour (time or date-time).

Assumes a 24-hour clock.

…/order_hdr/?order_shipped_ts__hour=20

minute – Match on the time’s minutes (time or date-time).

…/order_hdr/?order_shipped_ts__minute=30

You can also apply other lookup and arithmetic functions to temporal fields:

Date Range

For example, if we have a date-time field where we want to search for resources that have a value within a range, it is possible to chain two temporal filters together to search within a set date range:

…/order_hdr/?order_shipped_ts__gte=2018-09-01T00:00:00&order_shipped_ts__lt=2018-10-01T00:00:00

Or, it is possible to use the “range” lookup function:

…/order_hdr/?order_shipped_ts__range=2018-09-01T00:00:00,2018-10-01T00:00:00

However, since in this example we don’t have any specific time data, this could have also been accomplished more easily using the “month” lookup:

…/order_hdr/?order_shipped_ts__month=09

There may be multiple different ways to arrive at the same result when filtering. It is always desirable to be as specific as possible to minimize the result set and improve efficiency.

Additional Lookups

isnull – Boolean; Is the field’s value null?

This lookup is used to test if a field is null. This is a useful lookup as it can be used on any type of field to test for null.

Example: Filtering sales order(s) for only those where the shipped timestamp is null:

…/order_hdr/?order_shipped_ts__isnull=true

This is important because it allows you to make this test for any field type. If, for example, you tried to filter on the field’s value directly (…/order_hdr/?order_shipped_ts=null), you would receive an error that “null” is not a valid date. Since the field is of type date-time, it is expecting a temporal value and is interpreting “null” as the input.

in – Filter by values in a list

This lookup function allows for filtering by a group of values. These values may be a mix of different types, but the type(s) should be consistent with the type of the field being filtered. The input is a comma-delimited list with no spaces between entries in the list.

Example: Filter order_hdr by specific status id values:

…/order_hdr/?status_id__in=10,30,90

Or, it can be applied for filtering on a specific set of sales order numbers:

…/order_hdr/?order_nbr__in=ORDER001,ORDER002,ORDER003

It is also possible to use an “in” lookup with a single value to effectively function the same as an exact operator (“=”). The two following examples are equivalent in that they will return the same result set:

…/order_hdr/?order_nbr=ORDER001

…/order_hdr/?order_nbr__in=ORDER001

The difference is that an “in” lookup in inherently slower because of the way the filter is built and applied when filtering the data. If you have a single value to match on, it is recommended to use “=” instead of “in”.

range – Filter for resources with value within an inclusive range.

Numeric range

…/order_hdr/?status_id__range=10,90

Date range

…/order_hdr/?order_shipped_ts__range=2018-09-01T00:00:00,2018-10-01T00:00:00

Relational Resource Filtering

It is possible to filter on any related field for the given entity. All related field names end with “_id” and are integers by default.

For example, the simplest and fastest performing related resource filter is to search directly on the resource’s id. An “id” is the unique value assigned to every resource. Using the “order_hdr” field, “facility_id”, we could filter specifically for order belong to the facility with id “1”:

…/order_hdr/?facility_id=1

Adding the “company_id” field is a very common thing to do, in order to filter resources by facility and company (assuming the company’s id is also “1”):

…/order_hdr/?facility_id=1&company_id=1

But what if we wanted to filter by the value of a field belonging to the related resource. For example, what if we knew the facility and company codes, but didn’t yet know their respective “id” values. It is possible to filter on the related resource’s fields using double-underscore (“__”) notation.

Assuming facility with id=1 has a code “FAC1” and company with id=1 has a code “COM1”:

…/order_hdr/?facility_id__code=FAC1&company_id_code=COM1

This is not as efficient as using just the “id” of the related resources since lgfapi will need to do an additional lookup for each related resource to filter on their respective “code” fields. It is recommended to cache client-side the “id” values of commonly used, static entities (like facility and company) in order to improve performance in high-throughput systems.

It is also possible to filter multiple levels deep with related resources. For example, in order to filter on the order’s facility’s parent company, we could further chain the facility field, “parent_company_id”, as it is a related resource of “facility_id” and of entity type “company”:

…/order_hdr/?facility_id__parent_company_id=1

Again, you can also search on a related field:

…/order_hdr/?facility_id__parent_company_id_code=COM1

This is a handy and powerful tool for looking up resource sets based on related data. However, it is important to remember that as the relational filter depth increases, the performance may decrease as well since there is more work to be done to lookup related resource(s). Client-side caching and other performance methodologies are discussed in their own section.

Chaining Multiple Filters

It is possible to chain multiple filters on the same field. Each condition is just another key-value pair where the field is consistent. For example, if we wanted to filter the order_hdr entity to return those whose order_nbr starts with “ABC” and additionally contains the word “TEST”, we would write it as:

…/order_hdr/?order_nbr__startswith=ABC&order_nbr__contains=TEST

It is possible to chain together any number of different field and lookup combinations to arrive at your desired result set. However, it is important to note that the more filters applied, the more the performance may degrade. Therefore, it is always preferred to be as specific as possible when using filtering.