Servoy provides high-level filtering functionality which may be applied to any database table using the addTableFilterParam method of the databaseManager API. Table filters have the following properties:
Note |
---|
There are only two ways to circumvent a table filter: # by using the rawSQL plugin Therefore, if one wishes to maintain the effects of a filter, it is important to remember to modify queries with an appropriate SQL WHERE clause in case any of the above two cases apply |
Data Provider Name - This is the left-hand operand. It is the name of a single column by which to filter. When filtering an entire server connection, only tables which contain the named column will be filtered.
Operator - The following operators are supported
= | Only records whose column equals the specified value |
< | Only records whose column is less than the specified value |
> | Only records whose column greater than the specified value |
>= | Only records whose column greater than or equals the specified value |
<= | Only records whose column less than or equalsthe specified value |
!= | Only records whose column does not equal the specified value |
^ | Only records whose column value is null |
LIKE | Only records whose column matches using the SQL LIKE construct (use wildcard '%' characters) |
IN | Only records whose column value is in (using the SQL IN construct) a list of values |
# | Modifier, used to make case-insensitive queries |
|| | Modifier used to concatenate two conditions w/ a logical OR |
Note |
---|
Operators and modifiers may be combined, producing more complex conditions. For example #^||!= would translate to: is null OR case-insensitive not equals |
Note |
---|
When using the IN operator, one should provide an array of values or a String, which may be used as a sub select for the SQL IN clause. |
Example This is a simple example which filters records in a products table based on the criterion that the status is not discontinued
Code Block |
---|
var success = databaseManager.addTableFilterParam('crm_server','products','product_status','!=',globals.STATUS_DISCONTINUED,'productfilter'); |
Example This example shows a two filters using the IN operator
Code Block |
---|
// Filter products within an array of product codes var success = databaseManager.addTableFilterParam('crm', 'products', 'productcode', 'in', [120, 144, 200]); // Filter orders using a subselect var success = databaseManager.addTableFilterParam('crm', 'orders', 'countrycode', 'in', 'select country code from countries where region = "Europe"') |
Example This example shows how to filter an entire server connection by passing <null> for the table name. This is ideal for multi-tenant architectures as an entire server connection can be filtered by a single expression, i.e. the current company
Code Block |
---|
// all tables that have the companyid column should be filtered var success = databaseManager.addTableFilterParam('crm', null, 'companyidid', '=', globals.currentCompanyID) |