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