Jam.py documentation

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