For the new API to be less intimidating and more familiar to a developer, the name for the most popular method was preserved: getList. However, if previously each getList had its own set of parameters and closed-off opaque behaviour, now this method is uniform for all entities and is subject to general rules. Even if a developer wants to add some "scaffolding" in getList, he or she won't be able to do it.
The BookTable entity, taken as an example, is not an exception. Such parameters are accepted by the method BookTable::getList?
BookTable::getList(array(
'select' => ... // field names to be retrieved in the result
'filter' => ... // filter description for WHERE and HAVING
'group' => ... // direct indication of fields for result grouping
'order' => ... // sorting parameters
'limit' => ... // number of records
'offset' => ... // offset for limit
'runtime' => ... // dynamically-defined fields
));
getList always returns the object DB\Result
to retrieve data using the method fetch():
$rows = array();
$result = BookTable::getList(array(
...
));
while ($row = $result->fetch())
{
$rows[] = $row;
}
To fetch data for all records, use the method fetchAll():
$result = BookTable::getList($parameters);
$rows = $result->fetchAll();
// or just briefly:
$rows = BookTable::getList($parameters)->fetchAll();
Now let's overview all parameters in detail.
select
Parameter `select`
is defined as array with entity fields array:
BookTable::getList(array(
'select' => array('ISBN', 'TITLE', 'PUBLISH_DATE')
));
// SELECT ISBN, TITLE, PUBLISH_DATE FROM my_book
If original field names included into the result are not satisfactory due to some reasons - you can use aliases:
BookTable::getList(array(
'select' => array('ISBN', 'TITLE', 'PUBLICATION' => 'PUBLISH_DATE')
));
// SELECT ISBN, TITLE, PUBLISH_DATE AS PUBLICATION FROM my_book
This example the field name `PUBLISH_DATE` is replaced with `PUBLICATION` and specifically such name will be used in the resulting array.
When all fields must be selected, you can use the character '*':
BookTable::getList(array(
'select' => array('*')
));
Select only the "ScalarField" fields and expression "ExpressionField" fields and relations with other entities won't be affected: they can always be indicated directly.
filter
Parameter `filter`
inherited the format for iblock filter:
// WHERE ID = 1
BookTable::getList(array(
'filter' => array('=ID' => 1)
));
// WHERE TITLE LIKE 'Patterns%'
BookTable::getList(array(
'filter' => array('%=TITLE' => 'Patterns%')
));
Filter can be a multi-level array with merging expressions AND/OR:
// WHERE ID = 1 AND ISBN = '9780321127426'
BookTable::getList(array(
'filter' => array(
'=ID' => 1,
'=ISBN' => '9780321127426'
)
));
// WHERE (ID=1 AND ISBN='9780321127426') OR (ID=2 AND ISBN='9781449314286')
BookTable::getList(array(
'filter' => array(
'LOGIC' => 'OR',
array(
// 'LOGIC' => 'AND', // by default, items are patched using AND
'=ID' => 1,
'=ISBN' => '9780321127426'
),
array(
'=ID' => 2,
'=ISBN' => '9781449314286'
)
)
));
Full list of comparison operators that may be used in filter:
- = equal (works with array as well)
- % substring
- > more
- < less
- @ IN (EXPR), passes an array or object DB\SqlExpression as a value
- !@ NOT IN (EXPR) passes an array or object DB\SqlExpression as a value
- != not equal
- !% not substring
- >< between, passes an array(MIN, MAX) as a value
- >= more or equal
- <= less or equal
- =% LIKE
- %= LIKE
Clarification to prefixes
|
Prefixes %= and =% are equivalent, all examples for a single prefix are suitable for the second:
'%=NAME' => 'test' - fetching of record by LIKE (NOT SUBSTRING)
'%=NAME' => 'test%' - fetching of records, containing "test" at the start of field NAME
'%=NAME' => '%тест' - fetching of records, containing "test" at the end of field NAME
'%=NAME' => '%тест%' - fetching of records, containing "test" in the field NAME
The last variant is different from %NAME => test by final SQL query. |
- == boolean expression for ExpressionField (for example, for EXISTS() or NOT EXISTS())
- !>< not between, passes the array(MIN, MAX)
- !=% NOT LIKE
- !%= NOT LIKE
- '==ID' => null - condition for the field ID equals NULL (converts to ID IS NULL in SQL query)
- '!==NAME' => null - condition for the field NAME not equal NULL (converts to NAME IS NOT NULL in SQL query)
Attention! Without the directly set comparison operator
= executes
LIKE by default. In this case, uses the filter building code from the Iblocks module that presupposes such behaviour due to historical reasons.
For the field type int the following is set:
-
pre-ORM release
From the module main version 18.0.3.
: = (comparison, array is deployed into set of "OR" conditions =)
- after the release: IN().
group
The parameter `group`
enumerates fields for grouping:
BookTable::getList(array(
'group' => array('PUBLISH_DATE')
));
In majority of cases, you don't have to directly set the grouping: system automatically does this for you. You can find more details below in section about dynamically defined fields.
order
Parameter `order`
allows specifying sort order:
BookTable::getList(array(
'order' => array('PUBLISH_DATE' => 'DESC', 'TITLE' => 'ASC')
));
BookTable::getList(array(
'order' => array('ID') // default sort order - ASC
));
offset/limit
Parameters `offset`
and `limit`
help to limit number of fetched records or to implement pagewise selection:
// 10 last records
BookTable::getList(array(
'order' => array('ID' => 'DESC')
'limit' => 10
));
// 5th page with records, 20 per page
BookTable::getList(array(
'order' => array('ID')
'limit' => 20,
'offset' => 80
));
runtime
Calculated fields (ExpressionField) mentioned in the first portion of this article are needed not for entity description, but for fetching for different calculations with grouping.
The most simple example, calculation of number of records in an entity can be executed as follows:
BookTable::getList(array(
'select' => array('CNT'),
'runtime' => array(
new Entity\ExpressionField('CNT', 'COUNT(*)')
)
));
// SELECT COUNT(*) AS CNT FROM my_book
In this example, the field not only converts the value of a field, but implements an arbitrary SQL expression with COUNT function.
After registering a field in section `runtime`
, it can be referenced not only in `select`
section, but in other sections as well:
BookTable::getList(array(
'select' => array('PUBLISH_DATE'),
'filter' => array('>CNT' => 5),
'runtime' => array(
new Entity\ExpressionField('CNT', 'COUNT(*)')
)
));
// select days, when to issue more than 5 books
// SELECT PUBLISH_DATE, COUNT(*) AS CNT FROM my_book GROUP BY PUBLISH_DATE HAVING COUNT(*) > 5
Note. This example demonstrates the abovementioned automatic grouping: the system itself has identified it to be grouped by the field PUBLISH_DATE. You can find more details on such behaviour
here.
When calculated field is required only in the section `select`
(as it often happens), the section `runtime`
is optional: you can save time, placing the expression directly to `select`
.
BookTable::getList(array(
'select' => array(
new Entity\ExpressionField('MAX_AGE', 'MAX(%s)', array('AGE_DAYS'))
)
));
// SELECT MAX(DATEDIFF(NOW(), PUBLISH_DATE)) AS MAX_AGE FROM my_book
Please note, that inside the new Expression field MAX_AGE an already existing other Expression field AGE_DAYS is used. This way, the system allows using inserted expressions that will be sequentially deployed in the final SQL code.
The `runtime`
section can register not only Expression fields, but also the fields of any other types. The `runtime`
mechanism adds new field to an entity as if it was described in it originally inside the method `getMap`. However, such field is located in the visibility scope only within a single query; such field will be unavailable in the next getList query and you'll have re-register it again.
Fetched data caching
Caching of specific fetched data is available starting from module version 16.5.9. No need to describe it in the entity itself. By default, it's not cached.
The key cache
was added to parameters in getList:
$res = \Bitrix\Main\GroupTable::getList(array("filter"=>array("=ID"=>1), "cache"=>array("ttl"=>3600)));
The same is implemented using the Query:
$query = \Bitrix\Main\GroupTable::query();
$query->setSelect(array('*'));
$query->setFilter(array("=ID"=>1));
$query->setCacheTtl(150);
$res = $query->exec();
It's possible that the cached retrieved data will contain the object ArrayResult.
By default, the retrieved data from JOIN aren't cached. However, if you believe that your actions are correct, you can cache it directly:
"cache"=>array("ttl"=>3600, "cache_joins"=>true);
//or
$query->cacheJoins(true);
Cache reset occurs in any method add/update/delete. Forced cache reset for a table:
/* Example for user table */
\Bitrix\Main\UserTable::getEntity()->cleanCache();
Project admin can restrict the caching or updating of TTL.