DEPRECATION WARNING

This documentation is not using the current rendering mechanism and is probably outdated. The extension maintainer should switch to the new system. Details on how to use the rendering mechanism can be found here.

Operators and TCA

The are different operators for different types of data, enabling filtering as the editor wishes. In general the TCA definition of the field defines the possible operators, as e.g. greater is only possible for numbers and datetime.

The concrete list of possible inputs in combination with the operators:

equal:            'string', 'number', 'datetime', 'boolean'
not_equal:        'string', 'number', 'datetime', 'boolean'
in:               'string', 'number', 'datetime'
not_in:           'string', 'number', 'datetime'
less:             'number', 'datetime'
less_or_equal:    'number', 'datetime'
greater:          'number', 'datetime'
greater_or_equal: 'number', 'datetime'
between:          'number', 'datetime'
not_between:      'number', 'datetime'
begins_with:      'string'
not_begins_with:  'string'
contains:         'string'
not_contains:     'string'
ends_with:        'string'
not_ends_with:    'string'
is_empty:         'string'
is_not_empty:     'string'
is_null:          'string', 'number', 'datetime', 'boolean'
is_not_null:      'string', 'number', 'datetime', 'boolean'

Type definition and not supported types

number = integer and double

datetime = date, time and datetime

Note

Not supported types:

Timesec - is handled like a simple time eval.

Year - is handled as string/text (this means no greater, less or similar operators)

TCA Configuration

Most important for the querybuilder is the TCA definition of the filtered field itself, as it influences the input options. For example eval=datetime causes the filter to generate a datepicker for fitting input and easier usage.

../_images/Datetimepicker-Querybuilder.png

datetime-picker example

Datatypes with influence on the input:

  • date, time and datetime: a datetimepicker is created (adapted to the individual case)
  • double and integer: a number input filed is created
  • boolean: a checkbox input field is created
  • select: a select input field is created

Operators with influence on the input:

  • between: two input fields are created for the less and the greater value
  • empty and null: no input field is created

Tip

To get individual table fields into the querybuilder you just need to adapt the table TCA.

$TCA[‘ctrl’][‘queryFilterFields’] enables the wanted fields as filter option. If not defined $TCA[‘ctrl’][‘searchFields’] acts as default.

Specific operator information

IN

The IN operator is a more specific operator. You can filter for more than one value by only one input. The complete input is split by separators to the individual values, which are severally checked with equal.

Database-Query logic:

title IN (foo;bar;dong)  =>  title = foo OR title = bar OR title = dong

To separate the different values you have to use the defined chars. Possible separators are:

  • ‘;’ (semicolon)
  • ‘+’ (plus)
  • ‘#’ (hash)
  • ‘|’ (pipe)
  • ‘!’ (exclamation mark)
Input examples:
  • foo;bar;dong
  • foo! bar+ dong
  • hello world; that is| the new+ querybuilder

Note

Proper usages for IN are only string input types, as date, integer etc. won’t allow the seperator chars.

EMPTY and NULL

Note

Notice the different definition of empty and null:

  • Null: title = null
  • Empty: title = null OR title = ‘’