Filtering records

There are three ways to define what records an item dataset will get from the database table when the open method is called:

• to specify where parameter (option) of the open method,

• call the set_where method, before calling the open method,

• or use filters.

When where parameter is specified, it is always used even if the set_where method was called or item has filters whose values have been set.

When where parameter is omitted the parameter passed to the set_where method are used.

For example on the client in the following code in the first call of the open method the where option will be used to filter records, in the second call the parameters passed to set_where and only the third time the value of invoicedate1 filter will be used

function test(invoices) {
    var date = new Date(new Date().setYear(new Date().getFullYear() - 1));

    invoices.clear_filters();
    invoices.filters.invoicedate1.value = date;

    invoices.open({where: {invoicedate__ge: date}});

    invoices.set_where({invoicedate__ge: date});
    invoices.open();

    invoices.open();
}


date = datetime.datetime.now() - datetime.timedelta(days=3*365)

The same code on the server looks the following way:

from datetime import datetime

def test(invoices):
    date = datetime.now()
    date = date.replace(year=date.year-1)

    invoices.clear_filters()
    invoices.filters.invoicedate1.value = date

    invoices.open(where={'invoicedate__ge': date})

    invoices.set_where(invoicedate__ge=date)
    invoices.open()

    invoices.open()

In the framework, the following symbols and corresponding constants are defined to filter records:

Filter type	Filter symbol	Constant	SQL Operator
EQ	‘eq’	FILTER_EQ	=
NE	‘ne’	FILTER_NE	<>
LT	‘lt’	FILTER_LT	<
LE	‘le’	FILTER_LE	<=
GT	‘gt’	FILTER_GT	>
GE	‘ge’	FILTER_GE	>=
IN	‘in’	FILTER_IN	IN
NOT IN	‘not_in’	FILTER_NOT_IN	NOT IN
RANGE	‘range’	FILTER_RANGE	BETWEEN
ISNULL	‘isnull’	FILTER_ISNULL	IS NULL
EXACT	‘exact’	FILTER_EXACT	=
CONTAINS	‘contains’	FILTER_CONTAINS	uses LIKE with the “%” sign to find records where field value contains a search string
STARTWITH	‘startwith’	FILTER_STARTWITH	uses LIKE with the “%” sign to find records where field value starts with a search string
ENDWITH	‘endwith’	FILTER_ENDWITH	uses LIKE with the “%” sign to find records where field value ends with a search string
CONTAINS ALL	‘contains_all’	FILTER_CONTAINS_ALL	uses LIKE with the “%” sign to find records where field value contains all words of a search string

The where the parameter of the open method is a dictionary, whose keys are the names of the fields that are followed, after double underscore, by a filter symbol. For EQ filter the filtering symbol ‘__eq’ can be omitted. For example {'id': 100} is equivalent to {'id__eq': 100}.

See also

Dataset

Filters

Client

open

set_where

Server

open

set_where