Filterable properties

Performance considerations

Before getting into the details of how to add filters, it’s a good idea to think about the size of the collection you’re filtering on. Filtering on very large collections can come with performance costs.When you update the schema to specify a property as filterable, the filter is automatically assigned all possible filter operators, such as equals (eq), starts with (sw), greater than (gt), and so on. Using some of these filters on large collections can sometimes be problematic because they can create performance issues.

To avoid this problem, if you’re adding a filter to a query-backed collection that has or is expected to have a large amount of data, it might be better to create the filter using resource files rather than the schema file, regardless of what type of property it is. This will enable you to specify which operators are allowed on the property when you filter.

Adding filterable properties through the resource file also enables you to specify whether the filter is case sensitive for a given operator. Making a filter case sensitive is another way to potentially improve performance when filtering large collections.

Query-backed collections vs stream-backed collections

The way in which you make a property filterable depends not only on the type of property, but also on the type of the collection. Collections can be query-backed or stream-backed.

• Query-backed collections extend RestQueryCollectionResource.

• Stream-backed collections extend RestStreamCollectionResource.

• Resources that have both stream-backed and query-backed collections extend RestDualCollectionResource.

For all collection types, use the schema file to make a property filterable when the property exists directly on the data model entity.

For query-backed collections, use resource files to filter on a property that is derived at run time, derived from a join of multiple data model entities, or when you want to limit the filter operators.

For stream-backed collections, always use the schema files. If you want to sort based on a property that’s accessed through a join of data model entities, create an enhancement property with the join, then add that property to the schema file (as well as the mapping file).

For dual collections, perform the required actions for both query-backed and stream-backed. For example, if you create a filter based on a property that requires a join between multiple data model entities, you need to update the resource file (for query-backed) and create an enhancement and update the schema file (for stream-backed).

Adding filters through the schema file

Properties that are directly on a data model entity (or created through an enhancement for stream-backed collections) can easily be made filterable by updating the schema file associated with the API to which the entity belongs. For example, to make a property on the User data model entity filterable, you update the admin_ext-1.0.schema.json file.

Suppose you’ve created a CustomEntity_Ext entity with an ExpirationDate field, and you want to filter on that field. Within the schema file, you need to set the entity’s filterable property within the x-gw-extensions object to true:

"definitions": {
  "CustomEntityExt": {
    ...
      "properties": {
	    ...
	    "expirationDate": {
	      "x-gw-extensions": {
	        "filterable": true
	      }
	    }

Now when retrieving a collection of CustomEntity_Ext instances, the caller application can opt to filter the collection based on the expiration date using a call such as this:

GET /common/v1/customentity-exts?filter=expirationDate:gt:2020-05-11T07::00::00.000Z

Adding filters through resource files

Some properties cannot be made filterable by simply updating the schema file, either because their values are calculated at run-time and not stored in the database, or their values are the result of a join on multiple datasets. In these cases, updating the schema file won’t make the property filterable. Instead, you need to create a new filter class and instantiate it from the resource file associated with the collection on which you want to filter. (This applies only to query-backed collections.)

As an example, suppose you want to filter users based on their employee number. You would need to use the /admin/v1/users endpoint to retrieve the collection of users, then apply a filter to that collection. However, although the Users collection returns an employeeNumber (in the default configuration), that number does not exist on the User data model entity itself. Instead, EmployeeNumber is being pulled from the UserContact data model entity, which User references through a foreign key to the UserContact#ID. So in order to create a filter, you need to do the same thing: query for EmployeeNumber in UserContact by matching the User#Contact field to the UserContact#ID field.

Here’s an example of how to do that.

package gw.rest.ext.cc.admin.v1.user

uses gw.api.database.IQueryBuilder
uses gw.api.database.InOperation
uses gw.api.database.Queries
uses gw.api.database.Relop
uses gw.api.filters.IQueryFilter
uses gw.api.filters.StandardQueryFilter
uses gw.api.modules.rest.framework.v1.query.filters.QueryFilterOp
uses gw.api.modules.rest.framework.v1.query.filters.RestQueryFilter

class EmployeeNumberFilter_Ext extends RestQueryFilter {

   override function createQueryFilter(propertyName : String, queryFilterOp : QueryFilterOp, parseValue : Object) : IQueryFilter {

 return new StandardQueryFilter("employeeNumber_Ext", \q -> {
   var empQuery = Queries.createQuery(entity.UserContact).compare(UserContact#EmployeeNumber, Relop.Equals, parseValue)
   q.subselect(User#Contact, InOperation.CompareIn, empQuery, UserContact#ID)
 })
}

   override property get AllowedOperators() : Set<QueryFilterOp> {

     return {
         QueryFilterOp.eq

     }
   }

   override function parseValue(op : QueryFilterOp, employeeNumber : String) : Object {
     if (employeeNumber == "null" && op.getAllowNulls()) {
       return null;
     }

     return employeeNumber
   }

}

Walking through the example a step at a time, here’s how it works.

The first thing you need to do is create a new class that extends RestQueryFilter.

class EmployeeNumberFilter_Ext extends RestQueryFilter {

When you create the class, you need to decide in which package to store it. In this example, it’s stored in the same place as the resource file that’s being extended:

package gw.rest.ext.cc.admin.v1.user

Next you need to add references to the objects you’re going to use:

uses gw.api.database.IQueryBuilder
uses gw.api.database.InOperation
uses gw.api.database.Queries
uses gw.api.database.Relop
uses gw.api.filters.IQueryFilter
uses gw.api.filters.StandardQueryFilter
uses gw.api.modules.rest.framework.v1.query.filters.QueryFilterOp
uses gw.api.modules.rest.framework.v1.query.filters.RestQueryFilter

Within this new class, you override the createQueryFilter function, which is where you’ll define the query that retrieves the employee number.

override function createQueryFilter(propertyName : String, queryFilterOp : QueryFilterOp, parseValue : Object) : IQueryFilter {

In this example the query is created as a StandardQueryFilter. In creating this filter you first give it a name (in this case exmployeeNumber_Ext) and then a query. This query retrieves the UserContact information for each User, matching the Contact property foreign key to the ID, then compares the UserContact EmployeeNumber to the filter value (parseValue).

return new StandardQueryFilter("employeeNumber_Ext", \q -> {
   var empQuery = Queries.createQuery(entity.UserContact).compare(UserContact#EmployeeNumber, Relop.Equals, parseValue)
   q.subselect(User#Contact, InOperation.CompareIn, empQuery, UserContact#ID)
 })

Notice in the query that UserContact#EmployeeNumber is being compared to the filter value with Relop.Equals. This means that any filter applied will look only for employee numbers that exactly match the filter value. To enforce this restriction, you must also include getAllowedOperators and specify the equality operator:

getAllowedOperators and specify the equality operator:

   override property get AllowedOperators() : Set<QueryFilterOp> {

     return {
         QueryFilterOp.eq
     }
   }

By specifying only the eq operator as an allowed operator, any other operator used with this filter will produce an error.

See “Add filter operators” below for an example of how to allow multiple operators.

Next, you need to include the parseValue function. This function ensures that the filter value entered into the query string is converted to the proper datatype. (For example, a value of 2023-06-13T20:07:44.243Z would need to be parsed from a string to a Date object in order to filter on a Date field.) In this example, there is also a check for null to ensure the string "null" is parsed to an actual null (or empty) value rather than interpreted as the literal string "null".

override function parseValue(op : QueryFilterOp, employeeNumber : String) : Object {
  if (employeeNumber == "null" && op.getAllowNulls()) {
     return null;
  }

  return employeeNumber
}

The final step is to update the resource file associated with the collection. For this example you’ll update the UsersExtResource.gs file.

In the resource file, override getCustomFilters to enable the new filter:

override property get CustomFilters() : Map<String, RestQueryFilter> {

  var customFilters = new HashMap<String, RestQueryFilter>(super.getCustomFilters())
  customFilters.put("employeeNumber_Ext", new EmployeeNumberFilter_Ext())
  return customFilters;
}

Be sure to call super first, to ensure you don’t overwrite any existing filters that could already be in place. Then add your new filter, giving it a name (employeeNumber_Ext) and an instance of the new class you just made.

You can now filter on a specific employee number within the Users collection, like this:

GET /admin/v1/users?filter=employeeNumber_Ext:eq:1001

Add filter operators

The preceding example created a filter that allows you to filter on an exact match of an employee number. But in some cases, you might not want an exact match, you might want to find all values that are greater than or less than a certain value, or that contain or start with a string of characters. The following example demonstrates how to allow multiple operators with your filter.

This example modifies the previous example by adding the “starts with” (sw) operator to the allowed operators when filtering on an employee number.

class EmployeeNumberFilter_Ext extends RestQueryFilter {

   override function createQueryFilter(propertyName : String, queryFilterOp : QueryFilterOp, parseValue : Object) : IQueryFilter {

     var eFilter : StandardQueryFilter

     switch (queryFilterOp) {
       case QueryFilterOp.eq:
         eFilter = new StandardQueryFilter("employeeNumber_Ext", \q -> {
           var empQuery = Queries.createQuery(entity.UserContact).compare(UserContact#EmployeeNumber, Relop.Equals, parseValue)
           q.subselect(User#Contact, InOperation.CompareIn, empQuery, UserContact#ID)
         })
         break
       case QueryFilterOp.sw:
         eFilter = new StandardQueryFilter("employeeNumber_Ext", \q -> {
           var empQuery = Queries.createQuery(entity.UserContact).startsWith(UserContact#EmployeeNumber, String.valueOf(parseValue), true)
           q.subselect(User#Contact, InOperation.CompareIn, empQuery, UserContact#ID)
         })
         break

     }
     return eFilter
   }

   override property get AllowedOperators() : Set<QueryFilterOp> {
     return {
         QueryFilterOp.sw,
         QueryFilterOp.eq
     }
   }

   override function parseValue(op : QueryFilterOp, employeeNumber : String) : Object {
     if (employeeNumber == "null" && op.getAllowNulls()) {
       return null;
     }

     return employeeNumber
   }

}

When you allow multiple operators on a filter, you need to create a separate query for each filter. This example uses a switch statement to return the query that matches the operator that is being used.

switch (queryFilterOp) {

The first case applies to the eq operator, and is identical to the employee number query shown previously.

case QueryFilterOp.eq:
  eFilter = new StandardQueryFilter("employeeNumber_Ext", \q -> {
    var empQuery = Queries.createQuery(entity.UserContact).compare(UserContact#EmployeeNumber, Relop.Equals, parseValue)
    q.subselect(User#Contact, InOperation.CompareIn, empQuery, UserContact#ID)
  })
  break

The second case applies to the sw operator. This query is similar to the equality case, but instead of performing a query using Relop.Equals, you need to use the startsWith method on the query.

case QueryFilterOp.sw:
  eFilter = new StandardQueryFilter("employeeNumber_Ext", \q -> {
    var empQuery = Queries.createQuery(entity.UserContact).startsWith(UserContact#EmployeeNumber, String.valueOf(parseValue), true)
    q.subselect(User#Contact, InOperation.CompareIn, empQuery, UserContact#ID)
  })
  break

Note that the last parameter in the call to startsWith is set to true. This parameter specifies whether the filter is case insensitive. Set this value to false to make the filter case sensitive.

In addition to adding a new query in the createQueryFilter function, you also need to add the additional operator to the list of AllowedOperators:

override property get AllowedOperators() : Set<QueryFilterOp> {
  return {
     QueryFilterOp.eq,
     QueryFilterOp.sw
  }
}

The rest of the code is created in the same way as in the earlier example.

Now you can use endpoints like either of the following to retrieve users based on their employee number:

GET /admin/v1/users?fields=lastName,employeeNumber&filter=employeeNumber_Ext:eq:US-10011

GET /admin/v1/users?fields=lastName,employeeNumber&filter=employeeNumber_Ext:sw:US