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:

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:

Filter.equal("name", "Bob")

Filter.notEqual("age", 5)

Filter.greaterThan("age", 5)

Filter.greaterOrEqual("name", "M")

Filter.lessThan("name", "N")

Filter.lessOrEqual("age", 65)

Filter.in("eyeColor", EyeColor.BLUE, EyeColor.HAZEL)

Filter.notIn("hairColor", HairColor.BROWN, HairColor.BLACK)

Filter.like("name", "Wil%")

Filter.ilike("name", "wil%")

Filter.isNull("primaryDoctor")

Filter.isNotNull("phone")

Filter.isEmpty("children")

Filter.isNotEmpty("primaryDoctor.firstName")

Filter.some("children", Filter.equal("name", "Joey")) 
//has a child named 'Joey'

Filter.all("children", Filter.greaterOrEqual("age", 18)) 
//all children are 18 or older

Filter.none("pets", Filter.and(Filter.equal("species", "cat"), Filter.lessThan("age", .75)) 

//has no cats under 9 months old

Filter.and(Filter.greaterOrEqual("age", 40), Filter.lessThan("age", 65))

Filter.or(Filter.like("firstName", "Wil%"), Filter.like("lastName", "Wil%"))

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:

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:

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
]

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

def rec = api.find("PX", *filters)

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:

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

And here is the sample call:

def rec = api.find("MLTV3", *productStructureFilters(lookupTableName, itemCategory, productGroup, manufacturer))

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'.

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:

Filter.or(
  Filter.isNull("attribute1"), 
  Filter.notEqual("attribute1", "A")
)

Further Reading

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/