For the new API to be more familiar to a developer, the most popular method name was saved: getList. When previously each getList had its own set of parameters and individual uncustomizable behavior, now this method is the same for all entities and is subject to the same rules.
The entity BookTable, sourced as an example, is not an exception. Which parameters are received by the method BookTable::getList?
BookTable::getList(array(
'select' => ... // fields name to be retrieved in the result
'filter' => ... // filter description for WHERE and HAVING
'group' => ... // directly set fields used to group the result
'order' => ... // sorting parameters
'limit' => ... // number of entries
'offset' => ... // offset for limit
'runtime' => ... // dynamically determined fields
));
getList always returns the object DB\Result, used to retrieve data via the method fetch():
In this case, only the fields ScalarField will be used, and ExpressionField and associations with other entities won't be affected - they always can be indicated directly.
Calculated field in 'select', skipping runtime
Example below highlights the abovementioned grouping.
When you need the calculated fields only in the `select` section (which is most likely), the section `runtime` is optional: you can save time by placing the expression directly into 'select'.
The system allows to use the nested expression to be sequentially expanded in the final SQL code:
BookTable::getList(array(
'select' => array(
'runtime' => array(
new ORM\Fields\ExpressionField('MAX_AGE', 'MAX(%s)', array('AGE_DAYS'))
)
));
// SELECT MAX(DATEDIFF(NOW(), PUBLISH_DATE)) AS MAX_AGE FROM my_book
Be advised, the new Expression's MAX_AGE field has used an already existing Expression field AGE_DAYS. This way, the system allows using nested expressions to be sequentially expanded in the final SQL code.
Already existing Expression field AGE_DAYS was used in the the new Expression field MAX_AGE.
The 'runtime' section can register not only Expression fields, but also the fields of any other types. The 'runtime' mechanism operates to add new field to the entity as if it was initially described in the method getMap. But this field is visible only within a single query - in the next getList call it won't be accessible, you will have to re-register it.
filter
Parameter `filter` inherited the format 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 multi-levelled with 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', // elements are conjoined via AND
'=ID' => 1,
'=ISBN' => '9780321127426'
),
array(
'=ID' => 2,
'=ISBN' => '9781449314286'
)
)
));
Full list of comparative operators that can be used in filter:
= equal (works with array as well)
% substring
> more
< less
@ IN (EXPR), DB\SqlExpression array or object is passed as a value
!@ NOT IN (EXPR), array or object DB\SqlExpression is passed as value
!= not equal to
!% not substring
>< between, passes array (MIN, MAX) as a value
>= more or equal
<= less or equal
=% LIKE
%= LIKE
== boolean expression for ExpressionField (for example, for EXISTS() or NOT EXISTS())
!>< not between, passes array(MIN, MAX) as a value
!=% NOT LIKE
!%= NOT LIKE
'==ID' => null strict comparison with NULL by ID
'!==NAME' => null strict comparison with NULL by NAME
Attention! When comparison operator = is not indicated directly, executes LIKE by default. In this case, uses filter code from Iblock module.
Fields type int have:
-
before released object ORM
Starting from main module version 18.0.3.
: = (equality comparison, array expands into set of OR conditions =)
- after object ORM release: IN().
In majority of cases, there is no need to directly indicate grouping - system does it automatically. Find more details below in section dynamically determined fields.
order
The parameter `order` allows indicating sorting order:
Calculated fields (ExpressionField), mentioned in the first section of this lesson are needed mostly not in the entity description, but for selecting various calculations with grouping.
The most simple example, calculation of number of entities, can be performed as follows:
BookTable::getList(array(
'select' => array('CNT'),
'runtime' => array(
new ORM\Fields\ExpressionField('CNT', 'COUNT(*)')
)
));
// SELECT COUNT(*) AS CNT FROM my_book
Calculated field in this example not just converts a field value, but implements arbitrary SQL expression with function COUNT.
After registering a field in`runtime`, it can be referred 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 ORM\Fields\ExpressionField('CNT', 'COUNT(*)')
)
));
// select days, when more than 5 books were released
// SELECT PUBLISH_DATE, COUNT(*) AS CNT FROM my_book GROUP BY PUBLISH_DATE HAVING COUNT(*) > 5
Note. This example shows the above mentioned automatic grouping - system has identified, what must be grouped by the field PUBLISH_DATE.
When calculated field is required only in section `select` (as in most cases), the section `runtime` is optional: you can save time by inserting expression directly into `select`.
BookTable::getList(array(
'select' => array(
new ORM\Fields\ExpressionField('MAX_AGE', 'MAX(%s)', array('AGE_DAYS'))
)
));
// SELECT MAX(DATEDIFF(NOW(), PUBLISH_DATE)) AS MAX_AGE FROM my_book
Note, another already existing Expression field AGE_DAYS was used inside the new Expression field MAX_AGE. This way, the system allows using inserted expressions, which will be sequentially expanded in the final SQL code.
Fields of any other types can be registered in the section `runtime`, not only Expression fields. The `runtime` mechanism operates by adding a new field to an entity as if it was originally described in the method `getMap`. However, such files are going to be visible only once in a single query: in the next call for getList such field won't be available, it must be registered again.
Selection caching
Caching of a specific section is available from version 16.5.9. No need to describe anything in the entity itself. No caching by default.