Complex logic in filter

Use of queries

You can enter requests in some filter fields (mainly, text or character ones).

A query is a logical expression. For example, retrieving users by field user full name. If you enter

John & Peterson

then all users are filtered that have both these words in the full name field, either fully, or partially. Such result can be achieved by spelling these words with whitespace: John Peterson.

If you need to filter all users with full name field having John or Peterson, you need to input the following query:

John | Peterson

When you need to use negation, you can use a logical operator "~". For example: 

(John | Peterson) & ~Johnson

This query indicates: show all users with full name field containing combination of letters John or Peterson but at the same time, without Johnson.

The last query used round brackets. This is due to the operator "|" having lower priority, than "&" (see table Logical operator priorities). Otherwise, the query Peter | Johnson & ~Peterson would have had the following value without round brackets: show all users with full name field having combination of letters Peterson and without Johnson, or show all users, having the full name field with Peter.

You can use brackets for building more complex queries.

In addition to the operators described above, you can use the operators for specifying masks. For example, you do not remember full name of the requested person, but precisely sure that the last name of this person starts at "Pe" and ends at "son", but the first name starts starts at "Je" or "Ji". To describe these conditions, you need to use the following query: Pe%son & Je_ny. This query will use two operators: "%", inidcating "null, or more any characters" and operator "_", indicating "any single character".

In case, if you need to use a search for words, containing reserved characters, use double or simple quotes, for example: "~NICK~" | '&Leon&'. In you need to find a phrase containing spaces, select it by double or simple quotes, for example: "Microsoft Internet Explorer" | 'Netscape Navigator'.

Character's case has no importance in all the query's expressions.

Для фильтрации пользователей с незаполненным значением поля Город (с другими текстовыми полями аналогично).

Operators, used in queries

Operator	Description
&	Logical operator "and" can be skipped: query Peter & Johnson is fully equivalent to the query John Peterson.
|	Logical operator "or" allows searching records, containing at least one of operands.
~	Logical operator "does not" allow searching records without the word, indicated immediately after operator, or negates expression, immediately following this operator.
( )	Round brackets set sequence of action for logical operators.
%	Mask operator. Indicates "0 or any more characters".
_	Set mask operator. Indicates "1 any character".

Logical operator priorities

Operator	Priority
~	1
&	2
|	3

Example

Filtering users with incomplete City field value (and other text fields in the similar manner).

$filter = array("PERSONAL_CITY" => " ~_% ");