Views: 4676
Last Modified: 24.08.2023

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():

$rows = array();
$result = BookTable::getList(array(
	...
));
while ($row = $result->fetch())
{
	$rows[] = $row;
}

To get all entries, you can use the method fetchAll():

$result = BookTable::getList($parameters);
$rows = $result->fetchAll();

// or just as follows:
$rows = BookTable::getList($parameters)->fetchAll();

Now, let's review all parameters in more detail.

select

Parameter `select` is defined as an array with entity field names:

BookTable::getList(array(
	'select' => array('ISBN', 'TITLE', 'PUBLISH_DATE')
));

// SELECT ISBN, TITLE, PUBLISH_DATE FROM my_book

When due to some reasons you do not like original field names in the result, you can use aliases:

BookTable::getList(array(
	'select' => array('ISBN', 'TITLE', 'PUBLICATION' => 'PUBLISH_DATE')
));

// SELECT ISBN, TITLE, PUBLISH_DATE AS PUBLICATION FROM my_book

In this example, the field name `PUBLISH_DATE` replaced with `PUBLICATION` and specifically this name will be used in the resulting array.

When all fields must be selected, you can use the '*' character:

BookTable::getList(array(
	'select' => array('*')
));

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().

group

The parameter `group` lists fields to be grouped:

BookTable::getList(array(
	'group' => array('PUBLISH_DATE')
));

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:

BookTable::getList(array(
	'order' => array('PUBLISH_DATE' => 'DESC', 'TITLE' => 'ASC')
));

BookTable::getList(array(
	'order' => array('ID') // sorting direction - ASC
));

offset/limit

Parameters `offset` and `limit` help limiting number of selected entries or implementing per page selection:

// 10 recent entries
BookTable::getList(array(
	'order' => array('ID' => 'DESC')
	'limit' => 10
));

// 5th page with entries, 20 per page
BookTable::getList(array(
	'order' => array('ID')
	'limit' => 20,
	'offset' => 80
));

runtime

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.

The key cache is added to getList parameters:

$res = \Bitrix\Main\GroupTable::getList(array("filter"=>array("=ID"=>1), "cache"=>array("ttl"=>3600)));

The same is implemented in via 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 cached selection result will contain the object ArrayResult.

Selections with JOIN are not cached by default. However, you can optionally cache it:

"cache"=>array("ttl"=>3600, "cache_joins"=>true);
//or
$query->cacheJoins(true);

Cache reset is performed in any method add/update/delete. Forced cache reset for the table:

/* Example for user table */
\Bitrix\Main\UserTable::getEntity()->cleanCache();

Administrators can restrict TTL update and caching.


Note: When selection data formatting is required, use fetch_data_modification.


Fetching all elements

Use the parameter count_total with the value true to get list of all elements without pagination.

$res = MyTable::getList(...'count_total' => true, );

This allows getting the complete list via single query.

$res->getCount(); // all elements without pagination



Courses developed by Bitrix24