Filters are widely used throughout the Groovy API. You will find them in all kinds of search calls where they are used to filter data.

You might also want to read the section Searching API to find out how filters are actually used.

In this section:

1 Creating Filters
2 Using Filters
3 Tidying up Filters
4 Known Issues
5 Further Reading

Creating Filters

Filter objects can be instantiated and manipulated with the Filter class static methods:

def myFilter = Filter.equal("name", "Bob"); // creates a filter with the OP_EQUAL operator

The following filtering operators are available:

Operator	Notes	Example

Operator	Notes	Example
EQUAL		^{Filter.equal("name", "Bob")}
NOT_EQUAL		^{Filter.notEqual("age", 5)}
GREATER_THAN		^{Filter.greaterThan("age", 5)}
GREATER_OR_EQUAL		^{Filter.greaterOrEqual("name", "M")}
LESS_THAN		^{Filter.lessThan("name", "N")}
LESS_OR_EQUAL		^{Filter.lessOrEqual("age", 65)}
IN	Equals to one of the items in the list of values. The value can be a collection or an array.	^{Filter.in("eyeColor", EyeColor.BLUE, EyeColor.HAZEL)}
NOT_IN		^{Filter.notIn("hairColor", HairColor.BROWN, HairColor.BLACK)}
LIKE	Takes an SQL like expression.	^{Filter.like("name", "Wil%")}
ILIKE	LIKE + ignore case (Some databases ignore case by default.)	^{Filter.ilike("name", "wil%")}
NULL	SQL IS NULL	^{Filter.isNull("primaryDoctor")}
NOT_NULL		^{Filter.isNotNull("phone")}
EMPTY	NULL or empty string or empty collection/association	^{Filter.isEmpty("children")}
NOT_EMPTY		^{Filter.isNotEmpty("primaryDoctor.firstName")}
SOME	Applies to collection/association properties. Takes another Filter as a value, and matches when at least one of the values in the collection matches the filter.	^{Filter.some("children", Filter.equal("name", "Joey"))} ^{//has a child named 'Joey'}
ALL	The same as SOME, except that all values must match the filter.	^{Filter.all("children", Filter.greaterOrEqual("age", 18))} ^{//all children are 18 or older}
NONE	The same as SOME, except that none of the values may match the filter.	^{Filter.none("pets", Filter.and(Filter.equal("species", "cat"), Filter.lessThan("age", .75))} ^{//has no cats under 9 months old}
AND	Takes no property. Takes an array or collection of Filters as a value. Matches when all the filters in the value match.	^{Filter.and(Filter.greaterOrEqual("age", 40), Filter.lessThan("age", 65))}
OR	The same as AND, except that it matches when any of the filters match.	^{Filter.or(Filter.like("firstName", "Wil%"), Filter.like("lastName", "Wil%"))}
NOT	Takes no property. Takes a single Filter as a value. Matches when the filter in the value does not match.	^{Filter.not(Filter.ilike("name", "W%"))} ^{//name does not start with 'w'}

When building complex filters, it is recommended to format the code in a structure like this:

def myFilter =
    Filter.or(
        Filter.equal("name", "Jack"),
        Filter.and(
            Filter.equal("name", "Jill"),
            Filter.like("location", "%Chicago%"),
            Filter.greaterThan("age", 5)
        )
    )

Using Filters

To get the idea how you can use a filter, see the following example:

def prices = api.productExtension("ListPrices", Filter.equal("sku", api.product("sku")), Filter.lessOrEqual("attribute1", 200))

In this example, the query returns all rows from Product Extension called "ListPrices" which are assigned to the current product and whose price is less or equal to 200.

For more examples see Data Reading Functions where the filters are used extensively.

Tidying up Filters

Filters are very often used for multiple fields and writing many filters in one row makes the code not well readable and produces long lines like this:

Unreadable code

A better approach is to group them in a list and save into a variable:

Readable code

Then use the Spread operator * to expand this list to function parameters:

The code style above is more readable and reusable. It can be easily copied and quickly adjusted.

If you need to parametrize the filters, you can create a function that returns such a list instead:

Parametrized filter

And here is the sample call:

Known Issues

Filtering on NULL Values with Filter.notEqual() Returns No Rows

When filtering on NULL values together with the Filter.notEqual() filter, you must be aware of the fact that DB treats NULL values as unknown, which could result in an unexpected behavior.

See the following example. Let's say we want to retrieve all values except when Attribute1 equals 'A'.

SKU	Label	Attribute1

SKU	Label	Attribute1
001	Product 1	NULL
002	Product 2	NULL
003	Product 3	A

The common sense tells you to use Filter.notEqual("attribute1", "A") but this returns no rows. That is because you need to treat the NULL values using Filter.isNull() or Filter.isNotNull().

The following code works:

Developer Knowledge Base

Filters for Data Reading