Bitrix Framework

The Main module version update 17.5.2 introduced new filter in ORM.

Single conditions

Example of a simple query:

\Bitrix\Main\UserTable::query()
   ->where("ID", 1)
   ->exec();

// WHERE `main_user`.`ID` = 1

When you need another comparison operator, it's indicated directly:

\Bitrix\Main\UserTable::query()
   ->where("ID", "<", 10)
   ->exec();

// WHERE `main_user`.`ID` < 10

Example with using IS NULL:

\Bitrix\Main\UserTable::query()
   ->whereNull("ID")
   ->exec();

// WHERE `main_user`.`ID` IS NULL

There are whereNot* similar methods for all where* methods. Example:

\Bitrix\Main\UserTable::query()
   ->whereNotNull("ID")
   ->exec();

// WHERE `main_user`.`ID` IS NOT NULL

In addition to shared where, you can use the following operational methods:

whereNull($column) / whereNotNull($column)

whereIn($column, $values|Query|SqlExpression) / whereNotIn($column, $values|Query|SqlExpression)

whereBetween($column, $valueMin, $valueMax) / whereNotBetween($column, $valueMin, $valueMax)

whereLike($column, $value) / whereNotLike($column, $value)

whereExists($query|SqlExpression) / whereNotExists($query|SqlExpression)

For arbitrary expression with binding to entity fields, use the method whereExpr Available from the main module version 20.400.0. :

\Bitrix\Main\UserTable::query()
->whereExpr('JSON_CONTAINS(%s, 4)', ['SOME_JSON_FIELD'])
->exec();

// WHERE JSON_CONTAINS(`main_user`.`SOME_JSON_FIELD`, 4)

Expression arguments and multipart fields are similar to the ExpressionField constructor and are inserted using the function sprintf sprintf — Returns formatted string.

Learn more in the PHP documentation. .

List of operators is stored at \Bitrix\Main\ORM\Query\Filter\Operator::$operators (see array keys):

= , <> , != , < , <= , > , >= , in , between , like , exists

Comparison with another field

Separate method whereColumn simplifies the field comparison to one another:

\Bitrix\Main\UserTable::query()
   ->whereColumn('NAME', 'LOGIN')
   ->exec();

// WHERE `main_user`.`NAME` = `main_user`.`LOGIN`

This method is not significantly different from the where method, and formally it's the same call with a small wrapper:

\Bitrix\Main\UserTable::query()
   ->where('NAME', new Query\Filter\Expression\Column('LOGIN'))
   ->exec();

// WHERE `main_user`.`NAME` = `main_user`.`LOGIN`

whereColumn provides a flexible use for columns used in filter, for example:

\Bitrix\Main\UserTable::query()
   ->whereIn('LOGIN', [
      new Column('NAME'),
      new Column('LAST_NAME')
   ])
   ->exec();

// WHERE `main_user`.`LOGIN` IN (`main_user`.`NAME`, `main_user`.`LAST_NAME`)

Columns can be used in any operator. They can be perceived specifically as fields for specific entities and not just arbitrary SQL expression.

Multiple conditions

The following record is presented for several conditions:

\Bitrix\Main\UserTable::query()
   ->where('ID', '>', 1)
   ->where('ACTIVE', true)
   ->whereNotNull('PERSONAL_BIRTHDAY')
   ->whereLike('NAME', 'A%')
   ->exec();

// WHERE `main_user`.`ID` > 1 AND `main_user`.`ACTIVE` = 'Y' AND `main_user`.`PERSONAL_BIRTHDAY` IS NOT NULL AND `main_user`.`NAME` LIKE 'A%'

When you need to indicate several conditions in a single call, use the following format: (operator methods can be replaced by operator codes)

\Bitrix\Main\UserTable::query()
   ->where([
      ['ID', '>', 1],
      ['ACTIVE', true],
      ['PERSONAL_BIRTHDAY', '<>', null],
      ['NAME', 'like', 'A%']
   ])
   ->exec();

// WHERE `main_user`.`ID` > 1 AND `main_user`.`ACTIVE` = 'Y' AND `main_user`.`PERSONAL_BIRTHDAY` IS NOT NULL AND `main_user`.`NAME` LIKE 'A%'

OR and nested filters

Storage of all filter conditions in Query uses the condition container \Bitrix\Main\Entity\Query\Filter\ConditionTree. In addition to standard conditions, allows to add several instances of ConditionTree, thus creating any levels of branching and nesting depth.

All calls of where shown above - is proxying to base container. Next two calls will lead to completely identical result:

\Bitrix\Main\UserTable::query()
   ->where([
      ['ID', '>', 1],
      ['ACTIVE', true]
   ])
   ->exec();

\Bitrix\Main\UserTable::query()
   ->where(Query::filter()->where([
      ["ID", '>', 1],
      ['ACTIVE', true]
   ]))->exec();

// WHERE `main_user`.`ID` > 1 AND `main_user`.`ACTIVE` = 'Y'

Uses filter object instead of array. This allows to create subfilters and change logic from AND to OR:

\Bitrix\Main\UserTable::query()
   ->where('ACTIVE', true)
   ->where(Query::filter()
      ->logic('or')
      ->where([
         ['ID', 1],
         ['LOGIN', 'admin']
      ])
   )->exec();

// WHERE `main_user`.`ACTIVE` = 'Y' AND (`main_user`.`ID` = 1 OR `main_user`.`LOGIN` = 'admin')

The following chain of calls is permitted:

\Bitrix\Main\UserTable::query()
   ->where('ACTIVE', true)
   ->where(Query::filter()
      ->logic('or')
      ->where('ID', 1)
      ->where('LOGIN', 'admin')
   )
   ->exec();

// WHERE `main_user`.`ACTIVE` = 'Y' AND (`main_user`.`ID` = 1 OR `main_user`.`LOGIN` = 'admin')

Expressions

Filter allows ExpressionField used as field names. Such fields are automatically registered as entity field runtime.

\Bitrix\Main\UserTable::query()
   ->where(new ExpressionField('LNG', 'LENGTH(%s)', 'LAST_NAME'), '>', 10)
   ->exec();

// WHERE LENGTH(`main_user`.`LAST_NAME`) > '10'

Helper is added to simplify such constructions. The helper builds calculated fields:

\Bitrix\Main\UserTable::query()
   ->where(Query::expr()->length("LAST_NAME"), '>', 10)
   ->exec();

// WHERE LENGTH(`main_user`.`LAST_NAME`) > '10'

\Bitrix\Main\UserTable::query()
   ->addSelect(Query::expr()->count("ID"), 'CNT')
   ->exec();

// SELECT COUNT(`main_user`.`ID`) AS `CNT` FROM `b_user` `main_user`

Helper has the most popular SQL expressions:

• count
• countDistinct
• sum
• min
• avg
• max
• length
• lower
• upper
• concat

Compatibility with getList

If you use getList instead of Query call chain, inserts filter instead of array:

\Bitrix\Main\UserTable::getList([
   'filter' => ['=ID' => 1]
]);

\Bitrix\Main\UserTable::getList([
   'filter' => Query::filter()
      ->where('ID', 1)
]);

// WHERE `main_user`.`ID` = 1

JOIN conditions

Descriptions of references is provided in the following format:

new Entity\ReferenceField('GROUP', GroupTable::class,
    Join::on('this.GROUP_ID', 'ref.ID')
)

The method `on` is a brief and more semantically proper record Query::filter() with preset condition per columns. Returns filter instance and can build any conditions for JOIN:

new Entity\ReferenceField('GROUP', GroupTable::class,
    Join::on('this.GROUP_ID', 'ref.ID')
        ->where('ref.TYPE', 'admin')
        ->whereIn('ref.OPTION', [
            new Column('this.OPTION1'),
            new Column('this.OPTION2'),
            new Column('this.OPTION3')
        ]
)

Anywhere with indicated field names its implied that you can specify any chain of branching:

->whereColumn('this.AUTHOR.UserGroup:USER.GROUP.OWNER.ID', 'ref.ID');

Array format

There is an existing method for conversion from array to object \Bitrix\Main\ORM\Query\Filter\ConditionTree::createFromArray to use filter as an array. Array format looks as follows:

$filter = [
    ['FIELD', '>', 2],
    [
        'logic' => 'or',
        ['FIELD', '<', 8],
        ['SOME', 9]
    ],
    ['FIELD', 'in', [5, 7, 11]],
    ['FIELD', '=', ['column' => 'FIELD2']],
    ['FIELD', 'in', [
        ['column' => 'FIELD1'],
        ['value' => 'FIELD2'],
        'FIELD3']
    ],
    [
        'negative' => true,
        ['FIELD', '>', 19]
    ],
 ];

With standard comparison, pass the value either directly:

['FIELD', '>', 2]

or as an array with key value:

['FIELD', '>', ['value' => 2]]

Use the array with key column in a comparison with column:

['FIELD1', '>', ['column' => 'FIELD2']]

Nested filter are passed as similar nested arrays. Use self-title keys for replacement of object methods for negative() and to update the logic():

$filter = [
    ['FIELD', '>', 2],
    [
        'logic' => 'or',
        ['FIELD', '<', 8],
        ['SOME', 9]
    ],
    [
        'negative' => true,
        ['FIELD', '>', 19]
    ]
]

the rest of methods where* are replaced by corresponding comparison operators in, between, like and etc.

['FIELD', 'in', [5, 7, 11]]