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%'
Note: boolean fields with values Y/N, 1/0 and etc. can use true and false.
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]]
Attention: exercise close caution when using arrays. Do not insert unchecked data, passed by user, as a filter due to the possible dangerous conditions for data deployment in the database. Please, verify all input conditions using the field whitelist.