================= Filtering records ================= There are three ways to define what records an item :doc:`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 :doc:`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 .. code-block:: js 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: .. code-block:: py 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: .. csv-table:: :header: Filter type, Filter symbol, Constant, SQL Operator :widths: 20, 15, 10, 70 ``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 ======== :doc:`Dataset ` :doc:`Filters ` Client ------ :doc:`open ` :doc:`set_where ` Server ------ :doc:`open ` :doc:`set_where `