Package com.googlecode.genericdao.search

Class Filter

Object
Filter
All Implemented Interfaces:
Serializable
public class Filter extends Object implements Serializable

A Filter is used by the Search class to specify a restriction on what results should be returned in the search. For example, if a filter Filter.equal("name","Paul") were added to the search, only objects with the property "name" equal to the string "Paul" would be returned.

Nested properties can also be specified, for example Filter.greaterThan("employee.age",65).

Nested properties of corresponding JSON structure can be also used for filtering. It applies to objects containing inputs and outputs (typically Quotes, Rebate Agreements, Claims, Contracts, and Sales Compensations).
Note: We recommend using these nested filter properties instead of fetching the entire item (to get inputs/outputs) in your logic.

Example of filtering by inputs: 


     Filter.equal("inputs.Customer.value", endCustomerId)
Example of filtering by outputs: 

     Filter.equal("outputs.EndCustomer.result", endCustomerId)
If you use a filter for dates, the date values must be string formatted by the ISO format.

The property is the name of a field, for example "sku", "label" or "attribute3". Some methods that use the filter, for example api.find(), also accept the name configured for a field, such as the label of an attributeX column. Other methods do not.

Create filters with the static methods of this class, for example equal(String, Object), in(String, Collection) or and(Filter...). Then pass them to a data-reading method such as api.find(), api.stream() or api.count(). Logics can use the Filter class without an import.

Example: 


 def filters = [
     Filter.equal("name", "ListPrices"),
     Filter.equal("sku", sku),
     Filter.lessOrEqual("attribute1", 200)
 ]
 def prices = api.find("PX", *filters)
Author:
dwolverton
See Also:

  • Field Details

    • ROOT_ENTITY

      public static final String ROOT_ENTITY
      Property string representing the root entity of the search. This is just the empty string ("").
      See Also:

    • property

      protected String property
      The name of the property to filter on. It may be nested. Examples: "name", "dateOfBirth", "employee.age", "employee.spouse.job.title"

    • value

      protected Object value
      The value to compare the property with. Should be of a compatible type with the property. Examples: "Fred", new Date(), 45

    • operator

      protected int operator
      The type of comparison to do between the property and the value. The options are limited to the integer constants on this class: OP_EQAUL, OP_NOT_EQUAL, OP_LESS_THAN, OP_GREATER_THAN, LESS_OR_EQUAL, OP_GREATER_OR_EQUAL, OP_IN, OP_NOT_IN, OP_LIKE, OP_ILIKE, OP_NULL, OP_NOT_NULL, OP_EMPTY, OP_NOT_EMPTY, OP_SOME, OP_ALL, OP_NONE, OP_AND, OP_OR, OP_NOT .

    • DELIMITER

      public static final String DELIMITER
      See Also:

    • OP_EQUAL

      public static final int OP_EQUAL
      See Also:

    • OP_NOT_EQUAL

      public static final int OP_NOT_EQUAL
      See Also:

    • OP_LESS_THAN

      public static final int OP_LESS_THAN
      See Also:

    • OP_GREATER_THAN

      public static final int OP_GREATER_THAN
      See Also:

    • OP_LESS_OR_EQUAL

      public static final int OP_LESS_OR_EQUAL
      See Also:

    • OP_GREATER_OR_EQUAL

      public static final int OP_GREATER_OR_EQUAL
      See Also:

    • OP_LIKE

      public static final int OP_LIKE
      See Also:

    • OP_ILIKE

      public static final int OP_ILIKE
      See Also:

    • OP_IN

      public static final int OP_IN
      See Also:

    • OP_NOT_IN

      public static final int OP_NOT_IN
      See Also:

    • OP_NULL

      public static final int OP_NULL
      See Also:

    • OP_NOT_NULL

      public static final int OP_NOT_NULL
      See Also:

    • OP_EMPTY

      public static final int OP_EMPTY
      See Also:

    • OP_NOT_EMPTY

      public static final int OP_NOT_EMPTY
      See Also:

    • OP_AND

      public static final int OP_AND
      See Also:

    • OP_OR

      public static final int OP_OR
      See Also:

    • OP_NOT

      public static final int OP_NOT
      See Also:

    • OP_SOME

      public static final int OP_SOME
      See Also:

    • OP_ALL

      public static final int OP_ALL
      See Also:

    • OP_NONE

      public static final int OP_NONE
      See Also:

    • OP_CUSTOM

      public static final int OP_CUSTOM
      See Also:

  • Constructor Details

    • Filter

      public Filter()
      Not intended for public use. This constructor exists for JSON deserialization. Use the static methods of this class, such as equal(String, Object), instead.

    • Filter

      public Filter(String property, Object value, int operator)
      Creates a filter with the given property, value and operator. In logics, use the static methods of this class, such as equal(String, Object) or in(String, Collection), instead. They set the operator for you.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column
      operator - One of the OP_* constants of this class, for example OP_EQUAL

    • Filter

      public Filter(String property, Object value)
      Creates a filter that uses the == operator. In logics, use equal(String, Object) instead.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column

    • Filter

      public Filter(Map<String,?> filterAsMap)
      Not intended for use in logics. Use api.filterFromMap() instead.

      Creates a filter from its map form, with the keys property, value and operator. Nested sub-filters are not converted.

      See Also:

  • Method Details

    • reconstructFromMap

      public static Filter reconstructFromMap(Map<String,?> filterAsMap)
      Not intended for use in logics. Use api.filterFromMap() instead.

      Creates a filter from its map form, with the keys property, value and operator, including the nested sub-filters of AND, OR, NOT, SOME, ALL and NONE filters.

      See Also:

    • isFilter

      public static boolean isFilter(Map<String,?> filter)
      Not intended for use in logics. Use api.filterFromMap() instead.

      Checks whether a map is the map form of a filter, that is, whether it contains the keys property, value and operator.

      See Also:

    • equal

      public static Filter equal(String property, Object value)
      Create a new Filter using the == operator.

      In api.find() and api.stream(), Filter.equal("attribute1", null) is turned into Filter.isNull("attribute1"). Other methods, for example api.findLookupTableValues(), do not do this, and the filter then matches no rows. To match empty values, use isNull(String).

      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column.
      Returns:
      the new filter

    • lessThan

      public static Filter lessThan(String property, Object value)
      Create a new Filter using the < operator.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column.
      Returns:
      the new filter

    • greaterThan

      public static Filter greaterThan(String property, Object value)
      Create a new Filter using the > operator.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column.
      Returns:
      the new filter

    • lessOrEqual

      public static Filter lessOrEqual(String property, Object value)
      Create a new Filter using the <= operator.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column.
      Returns:
      the new filter

    • greaterOrEqual

      public static Filter greaterOrEqual(String property, Object value)
      Create a new Filter using the >= operator.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column.
      Returns:
      the new filter

    • in

      public static Filter in(String property, Collection<?> value)
      Create a new Filter using the IN operator. The filter matches when the column contains one of the values.

      The collection can contain any number of values. In api.find(), an empty collection matches no rows.

      Example: 

      
 def skus = ["MB-0001", "MB-0002", "MB-0003"]
 def products = api.find("P", Filter.in("sku", skus))
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Collection of values to be compared with content of the given column
      Returns:
      the new filter
      See Also:

    • in

      public static Filter in(String property, Object... value)
      Create a new Filter using the IN operator.

      This takes a variable number of parameters. Any number of values can be specified.

      Example: 

      
 def products = api.find("P", Filter.in("sku", "MB-0001", "MB-0002"))
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Values to be compared with content of the given column
      Returns:
      the new filter
      See Also:

    • notIn

      public static Filter notIn(String property, Collection<?> value)
      Create a new Filter using the NOT IN operator. The filter matches when the column contains none of the values.

      The collection can contain any number of values. In api.find(), an empty collection matches all rows.

      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Collection of values to be compared with content of the given column
      Returns:
      the new filter
      See Also:

    • notIn

      public static Filter notIn(String property, Object... value)
      Create a new Filter using the NOT IN operator.

      This takes a variable number of parameters. Any number of values can be specified.

      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Values to be compared with content of the given column
      Returns:
      the new filter
      See Also:

    • like

      public static Filter like(String property, String value)
      Create a new Filter using the LIKE operator. The value is an SQL LIKE pattern, in which % stands for any sequence of characters. To ignore case, use ilike(String, String).

      Example: 

      
 def products = api.find("P", Filter.like("label", "Mouse%"))
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - SQL LIKE pattern to be compared with content of the given column
      Returns:
      the new filter
      See Also:

    • ilike

      public static Filter ilike(String property, String value)
      Create a new Filter using the ILIKE operator. It works like like(String, String), but ignores case.

      Example: 

      
 def products = api.find("P", Filter.ilike("label", "%mouse%"))
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - SQL LIKE pattern to be compared with content of the given column
      Returns:
      the new filter
      See Also:

    • notEqual

      public static Filter notEqual(String property, Object value)
      Create a new Filter using the != operator.

      In a database query, rows where the column is null do not match this filter, because the database treats null as an unknown value. To include them, combine this filter with isNull(String).

      Example: 

      
 def filter = Filter.or(
     Filter.isNull("attribute1"),
     Filter.notEqual("attribute1", "A")
 )
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      value - Value to be compared with content of the given column
      Returns:
      the new filter
      See Also:

    • isNull

      public static Filter isNull(String property)
      Create a new Filter using the IS NULL operator.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      Returns:
      the new filter
      See Also:

    • isNotNull

      public static Filter isNotNull(String property)
      Create a new Filter using the IS NOT NULL operator.
      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      Returns:
      the new filter
      See Also:

    • isEmpty

      public static Filter isEmpty(String property)
      Create a new Filter using the IS EMPTY operator. This filter is not supported in the Analytics module (PA).

      The filter matches a null value, an empty string, or a collection without elements.

      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      Returns:
      the new filter
      See Also:

    • isNotEmpty

      public static Filter isNotEmpty(String property)
      Create a new Filter using the IS NOT EMPTY operator. This filter is not supported in the Analytics module (PA).

      The filter matches a value that is not null and not an empty string, or a collection with at least one element.

      Parameters:
      property - Name of the property to filter on, for example "sku" or "attribute3"
      Returns:
      the new filter
      See Also:

    • and

      public static Filter and(Filter... filters)
      Create a new Filter using the AND operator.

      This takes a variable number of parameters. Any number of Filters can be specified.

      Example: 

      
 def filter = Filter.and(
     Filter.equal("attribute1", "Accessories"),
     Filter.greaterOrEqual("attribute2", 100)
 )
 def products = api.find("P", filter)
      Parameters:
      filters - Filters that must all match
      Returns:
      the new filter
      See Also:

    • or

      public static Filter or(Filter... filters)
      Create a new Filter using the OR operator.

      This takes a variable number of parameters. Any number of Filters can be specified.

      Example: 

      
 def filter = Filter.or(
     Filter.equal("label", "Jack"),
     Filter.and(
         Filter.equal("label", "Jill"),
         Filter.like("attribute1", "%Chicago%")
     )
 )
      Parameters:
      filters - Filters of which at least one must match
      Returns:
      the new filter
      See Also:

    • not

      public static Filter not(Filter filter)
      Create a new Filter using the NOT operator.

      Example: 

      
 def filter = Filter.not(Filter.ilike("label", "test%"))
      Parameters:
      filter - Filter that must not match
      Returns:
      the new filter

    • some

      public static Filter some(String property, Filter filter)
      Create a new Filter using the SOME operator. This filter is not supported in the Analytics module (PA).

      The property is a collection of the searched object, for example the roles of a user. The filter matches when at least one element of the collection matches the given filter, which refers to the properties of that element. The property cannot be a nested path such as "businessRoles.roles". Nest the filters instead, as in the example. The SOME, ALL and NONE filters cannot be used in an api.find() call that returns distinct values only.

      Example: 

      
 def users = api.find("U", 0, "loginName",
     Filter.some("businessRoles", Filter.some("roles", Filter.equal("uniqueName", "CUSTOM_FORM_ADMIN")))
 )
      Parameters:
      property - Name of the collection property
      filter - Filter that at least one element of the collection must match
      Returns:
      the new filter
      See Also:

    • all

      public static Filter all(String property, Filter filter)
      Create a new Filter using the ALL operator. This filter is not supported in the Analytics module (PA).

      The filter matches when every element of the collection matches the given filter. A collection without elements matches. The restrictions of some(String, Filter) apply.

      Example: 

      
 def users = api.find("U", 0, "loginName", Filter.all("roles", Filter.equal("uniqueName", "CUSTOM_FORM_ADMIN")))
      Parameters:
      property - Name of the collection property
      filter - Filter that every element of the collection must match
      Returns:
      the new filter
      See Also:

    • none

      public static Filter none(String property, Filter filter)
      Create a new Filter using the NONE operator. This is equivalent to NOT SOME. This filter is not supported in the Analytics module (PA).

      The restrictions of some(String, Filter) apply.

      Example: 

      
 def users = api.find("U", 0, "loginName", Filter.none("roles", Filter.equal("uniqueName", "DASHBOARD_RO")))
      Parameters:
      property - Name of the collection property
      filter - Filter that no element of the collection may match
      Returns:
      the new filter
      See Also:

    • custom

      public static Filter custom(String expression)

      Create a new Filter using a custom JPQL/HQL expression. This can be any valid where-clause type expression. Reference properties by wrapping them with curly braces ({}).

      Here are some examples: 

      
 // Referencing a property in a custom expression
 Filter.custom("{serialNumber} like '%4780%'");
 Filter.custom("{RebateCategory} like ?1", "%Promotional%");
 // comparing two properties
 Filter.custom("{attribute10} < {attribute34}");
 Filter.custom("{parent.spotCount} > {spotCount} + 4");
 // A constant
 Filter.custom("1 = 1");
 // A function
 Filter.custom("{dueDate} > current_date()");
 // A subquery
 Filter.custom(" AND {clicId} IN (SELECT id FROM Quote WHERE workflowStatus = 3 or workflowStatus = 4)") // draft = 0, submitted = 1, denied = 2, approved = 3, no_approval_required = 4, withdrawn = 5, invalidated = 6;
 Filter.custom(" {claimId} IN (SELECT id FROM Claims WHERE workflowStatus = 1 or workflowStatus = 3 or workflowStatus = 4");
 Filter.custom("{sku} IN (SELECT sku FROM PX30 WHERE attribute6 NOT IN ('NA', 'Custom')");
      Parameters:
      expression - JPQL/HQL where-clause expression
      Returns:
      the new filter

    • custom

      public static Filter custom(String expression, Object... values)

      Create a new Filter using a custom JPQL/HQL expression. This can be any valid where-clause type expression. Reference properties by wrapping them with curly braces ({}). The expression can also contain place holders for the Filter values; these are indicated by JPQL-style positional parameters (i.e. a question mark (?) followed by a number indicating the parameter order, starting with one).

      Here are some examples: 

      
 // Referencing a property in a custom expression
 Filter.custom("{serialNumber} like ?1", "%4780%");
 // comparing two properties
 Filter.custom("{parent.spotCount} + ?1 > {spotCount} + ?2", 0, 4);
 // A constant
 Filter.custom("?1 = ?2", 1, 1);
 // A function
 Filter.custom("?1 > current_date()", someDate);
 // A subquery
 Filter.custom("{id} in (select pc.cat_id from popular_cats pc where pc.color = ?1)", "blue");
      Parameters:
      expression - JPQL/HQL where-clause expression
      values - one or more values to fill in the numbered placeholders in the expression
      Returns:
      the new filter

    • custom

      public static Filter custom(String expression, Collection<?> values)

      Create a new Filter using a custom JPQL/HQL expression. This can be any valid where-clause type expression. Reference properties by wrapping them with curly braces ({}). The expression can also contain place holders for the Filter values; these are indicated by JPQL-style positional parameters (i.e. a question mark (?) followed by a number indicating the parameter order, starting with one).

      Here are some examples: 

      
 // Referencing a property in a custom expression
 Filter.custom("{serialNumber} like ?1", Collections.singleton("%4780%"));
 // comparing two properties
 Filter.custom("{parent.spotCount} + ?1 > {spotCount} + ?2", Arrays.asList(0, 4));
 // A constant
 Filter.custom("?1 = ?2", Arrays.asList(1, 1));
 // A function
 Filter.custom("?1 > current_date()", Collections.singleton(someDate));
 // A subquery
 Filter.custom("{id} in (select pc.cat_id from popular_cats pc where pc.color = ?1)", Collections.singleton("blue"));
      Parameters:
      expression - JPQL/HQL where-clause expression
      values - one or more values to fill in the numbered placeholders in the expression
      Returns:
      the new filter

    • alwaysMatch

      public static Filter alwaysMatch()
      Creates a filter that matches every row of a query. Use it where a filter is required but the query must not be restricted, for example when an optional condition is not set.

      Example: 

      
 def filter = category ? Filter.equal("attribute1", category) : Filter.alwaysMatch()
 def products = api.find("P", filter)
      Returns:
      a Filter that alwaysMatch
      Since:
      9.3 - Hurricane
      See Also:

    • neverMatch

      public static Filter neverMatch()
      Creates a filter that matches no row of a query.
      Returns:
      a Filter that neverMatch
      Since:
      9.3 - Hurricane
      See Also:

    • add

      public void add(Filter filter)
      Used with OP_OR and OP_AND filters. These filters take a collection of filters as their value. This method adds a filter to that list.
      Parameters:
      filter - Filter to add
      See Also:

    • remove

      public void remove(Filter filter)
      Used with OP_OR and OP_AND filters. These filters take a collection of filters as their value. This method removes a filter from that list.
      Parameters:
      filter - Filter to remove
      See Also:

    • getProperty

      public String getProperty()
      Returns the name of the property to filter on.
      Returns:
      the name of the property, for example "sku" or "employee.age"
      See Also:

    • setProperty

      public void setProperty(String property)
      Sets the name of the property to filter on. It may be nested.
      Parameters:
      property - Name of the property, for example "sku" or "employee.age"
      See Also:

    • getValue

      public Object getValue()
      Returns the value to compare the property with. For AND and OR filters, the value is the list of sub-filters. For NOT, SOME, ALL and NONE filters, it is the sub-filter.
      Returns:
      the value of this filter
      See Also:

    • setValue

      public void setValue(Object value)
      Sets the value to compare the property with. The value should be of a type compatible with the property.
      Parameters:
      value - Value to compare the property with, for example "Fred", a date or 45
      See Also:

    • getOperator

      public int getOperator()
      Returns the type of comparison this filter does, as one of the OP_* constants of this class.
      Returns:
      the operator, for example OP_EQUAL
      See Also:

    • setOperator

      public void setOperator(int operator)
      Sets the type of comparison this filter does.
      Parameters:
      operator - One of the OP_* constants of this class, for example OP_EQUAL
      See Also:

    • getValuesAsList

      public List<?> getValuesAsList()
      Returns the value as a List, converting if necessary. If the value is a List, it will be returned directly. If it is any other Collection type or if it is an Array, an ArrayList will be returned with all the same elements. If the value is any other non-null value, a List containing just that one value will be returned. If it is null, null will be returned.
      Returns:
      the value as a list, or null if the value is null
      See Also:

    • getValuesAsCollection

      public Collection<?> getValuesAsCollection()
      Returns the value as a Collection, converting if necessary. If the value is a Collection, it will be returned directly. If it is an Array, an ArrayList will be returned with all the same elements. If the value is any other non-null value, a Set containing just that one value will be returned. If it is null, null will be returned.
      Returns:
      the value as a collection, or null if the value is null
      See Also:

    • isTakesSingleValue

      public boolean isTakesSingleValue()
      Returns:
      true if the operator should have a single value specified.

      EQUAL, NOT_EQUAL, LESS_THAN, LESS_OR_EQUAL, GREATER_THAN, GREATER_OR_EQUAL, LIKE, ILIKE

    • isTakesListOfValues

      public boolean isTakesListOfValues()
      Returns:
      true if the operator should have a list of values specified.

      IN, NOT_IN

    • isTakesNoValue

      public boolean isTakesNoValue()
      Returns:
      true if the operator does not require a value to be specified.

      NULL, NOT_NULL, EMPTY, NOT_EMPTY, CUSTOM

    • isTakesSingleSubFilter

      public boolean isTakesSingleSubFilter()
      Returns:
      true if the operator should have a single Filter specified for the value.

      NOT, ALL, SOME, NONE

    • isTakesListOfSubFilters

      public boolean isTakesListOfSubFilters()
      Returns:
      true if the operator should have a list of Filters specified for the value.

      AND, OR

    • isTakesNoProperty

      public boolean isTakesNoProperty()
      Returns:
      true if the operator does not require a property to be specified.

      AND, OR, NOT

    • hashCode

      public int hashCode()
      Overrides:
      hashCode in class Object

    • equals

      public boolean equals(Object obj)
      Compares this filter with another object. Two filters are equal when they have the same operator, property and value.
      Overrides:
      equals in class Object
      Parameters:
      obj - Object to compare with
      Returns:
      true if the object is an equal filter, false otherwise

    • matches

      public boolean matches(Map<String,Object> values)
      The method verifies if this Filter would successfully match the provided combination of values.

      For example, the following code would return true: 

      
     Filter.equal("myField", 5).matches([myField: 5])

      And the following would return false: 

      
     Filter.in("myField", 3, 4).matches([myField: 5])

      Of course, composite Filters can be used with and(Filter...), or(Filter...), etc: 

      
     def filter = Filter.or(
         Filter.notIn("field1", "a", "b"),
         Filter.ilike("field2", "hey%")
     )
     // returns false
     filter.matches([field1: "a"])
     // returns true
     filter.matches([field1: "a", field2: "heyo"])
      Parameters:
      values - A Map of values for the fields being referenced in this Filter.
      Returns:
      true if the Filter matches for the values in values.
      Since:
      9.0 - Hurricane

    • matches

      public boolean matches(MatcherContext context)
      Not intended for public use. Use matches(Map) instead.

    • toString

      public String toString()
      Overrides:
      toString in class Object

    • toQueryString

      public String toQueryString()
      Not intended for public use. Returns this filter as a condition string in an SQL-like syntax, as the application uses it internally.
      Returns:
      the condition string