getList

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

== 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.

Note: Retrieved data formatting can be done using fetch_data_modification.

Bitrix Framework

Fetched data caching