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:

Table of Contents

maxLevel	1

Creating Filters

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

Code Block

language	groovy

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

The following filtering operators are available:

Operator	Notes	Example
EQUAL		^{Filter.equal("name",}

^"Bob")
NOT_EQUAL		^{Filter.notEqual("age",}

⁵⁾
GREATER_THAN		^{Filter.greaterThan("age",}

⁵⁾
GREATER_OR_EQUAL		^{Filter.greaterOrEqual("name",}

^"M")
LESS_THAN		^{Filter.lessThan("name",}

^"N")
LESS_OR_EQUAL		^{Filter.lessOrEqual("age",}

⁶⁵⁾
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",}

¹⁸⁾⁾

^//all

^children

^are

¹⁸

^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

⁹

^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",}

⁶⁵⁾⁾
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:

Code Block

language	groovy

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:

Code Block

language	groovy

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

Code Block

language	groovy

Filter.equal("name", "Promotions"), 
Filter.equal("sku", api.product("sku")), 
Filter.equal("attribute2","Fix price"), 
Filter.lessOrEqual("attribute5", targetTimestamp), 
Filter.greaterOrEqual("attribute6", targetTimestamp)
def rec = api.find("PX", )

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

Readable code

Code Block

language	groovy

def filters = [
  Filter.equal("name", "Promotions"),
  Filter.equal("sku", api.product("sku")),
  Filter.equal("attribute2", "Fix price"),
  Filter.lessOrEqual("attribute5", targetTimestamp),  // from
  Filter.greaterOrEqual("attribute6", targetTimestamp),  // to
]

...

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

Parametrized filter

Code Block

language	groovy

def productStructureFilters(lookupTableName, itemCategory, productGroup, manufacturer) {
  return [
    Filter.equal("lookupTable.uniqueName", lookupTableName),
    Filter.equal("key1", itemCategory),
    Filter.equal("key2", productGroup),
    Filter.equal("key3", manufacturer)
  ]
}

...

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

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().

...

As the whole filtering API is largely based on an opensource project (but not 100% identical anymore), you may also have a look at their documentation. Chances are that the options described there will also work in the Groovy code:

https://code.google.com/archive/p/hibernate-generic-dao/

Versions Compared

Old Version 9

New Version Current

Key

Creating Filters

Using Filters

Tidying up Filters

Unreadable code

Readable code

Parametrized filter

Page Comparison

Versions Compared

Old Version 9

New Version Current

Key

Creating Filters

Using Filters

Tidying up Filters

Unreadable code

Readable code

Parametrized filter