Making properties filterable

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. Here are some suggestions for minimizing or avoiding this issue.

Add database indexes. Filtering on a property that’s indexed in the database is much more efficient than filtering on a property that is not indexed. If you’re adding a filter to a collection that is or could potentially be very large, the best option for avoiding system problems is to index the property. See Configuration for more information on adding indexes.

However, in some cases indexing alone doesn’t solve the problem. You must take additional steps to be certain that your filter works in such as way that the index will be leveraged by the database.

Limit the operators that can be used with the filter. When you update the schema to specify a property as filterable, the filter is automatically assigned all possible filter operators, such as starts with (sw) and greater than (gt). However, these operators will likely not be able to leverage the index at the database level. Filtering on large collections aught to be limited to allow only the equals (eq) operator. To enforce this limitation, you must create the filter using resource files rather than the schema file. This will enable you to specify which operators are allowed on the property when you filter.

Make the filter case sensitive. By default, filtering is not case sensitive. However, case-insensitive searches will likely not be able to leverage the index at the database level. This means that even if you’ve indexed a property and limited filter operators to only eq, your filter can still cause serious performance issues. You must take the additional step of making the filter case-sensitive to ensure the index is used, thus avoiding most performance issues. As with limiting operators, making filters case-sensitive requires you to use resource files rather than the schema to define your filter.

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

Create a new class

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
}

Update the collection class

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 function getCustomFilters(config: ApiConfiguration) : Map<String, RestQueryFilter> {
    var customFilters = new HashMap<String, RestQueryFilter>(super.getCustomFilters(config))
    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.

Grant access to the filter

API role files determine which endpoints and fields a caller can access. By default, a caller has no access to any endpoints nor to any fields on resources returned by any endpoint. You must specifically grant access to each endpoint and then to one or more fields in the resources returned by an endpoint.

In an API role file, the accessibleFields section identifies which fields are accessible. There are different ways that you can grant access to fields:

• Listing each accessible field explicitly
• Using the "*" wildcard, which grants access to every field in the resource

When you add a custom filter through resource files, a caller will be able to access it if they have a role that grants view access to all fields using the "*" wildcard, including customer filters. If they do not have view access to all fields, then the filter must be added explicitly. If neither of these things are done, the filter will be inaccessible to the caller.

  Users:
    view:
      - "*"

  Users:
    view:
      - "employeeNumber"
      ...

For more information on how to grant access to fields in API role files, see API role accessible fields.

Using the filter

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("policyNumber_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