Views: 1160 (Data available since 06.02.2017)

Data sampling with various conditions for filtering, grouping, and sorting is the most common task.

getList

In order to make a new API look less frightening and more familiar for the developer, the name of the most popular method getList has been left unchanged. However, contrary to the getList of earlier days with its set of parameters and closed nontransparent behavior, now this method is common for all entities and follows the same rules. The entity developer will not be able to alter getList even if s/he wants to.

The entity BookTable taken as an example is not an exception. Which parameters are acceptable for the method BookTable::getList?

BookTable::getList(array(
	'select'  => ... // field names to be obtained as a result
	'filter'  => ... // filter description for WHERE and HAVING
        'group'   => ... // explicit indication of the field to be used in order to group the result
        'order'   => ... // sorting parameters
        'limit'   => ... // number of entries
        'offset'  => ... // limit shifting
        'runtime' => ... // dynamically defined fields

));

getList always returns the object DB\Result, from which the data can be obtained:

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

In order to obtain all entries at once the method fetchAll() can be used:

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

// or even shorter:
$rows = BookTable::getList($parameters)->fetchAll();

Now, let us consider all the parameters in more detail.

select

The select parameter is set up as an array with the names of entity fields:

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

// SELECT ISBN, TITLE, PUBLISH_DATE FROM my_book

The following aliases can be used if you are not satisfied with the original field names in the result due to any reasons:

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 is replaced with PUBLICATION, and it is this name that will be used in the resulting array.

If all the fields must be selected, the symbol *can be used:

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

In this case, only the fields ScalarField will be selected, and the ExpressionField fields and relations with other entities will not be affected since they always have to be indicated explicitly.

filter

The parameter filter has inherited the infoblock filter format:

// WHERE ID = 1
BookTable::getList(array(
	'filter' => array('=ID' => 1)
));

// WHERE TITLE LIKE 'Patterns%'
BookTable::getList(array(
	'filter' => array('%=TITLE' => 'Patterns%')
));

The filter can be a multilevel array where AND/OR are merged:

// 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 the elements are merged through AND
			'=ID' => 1,
			'ISBN' => '9780321127426'
		),
		array(
			'=ID' => 2,
			'ISBN' => '9781449314286'
		)
	)
));

The complete list of comparison operators which can be used in a filter:

  • = - equal
  • % - substring
  • > - more
  • < - less
  • @ IN (EXPR) - the object DB\SqlExpression is submitted as a value
  • != - not equal
  • !% - not substring
  • >< - between, the array(MIN, MAX) is submitted as a value
  • >= - more or equal
  • <= - less or equal
  • =% - LIKE
  • %= - LIKE
  • >!< - not between, the array(MIN, MAX) is submitted as a value
  • !=% - NOT LIKE
  • !%= - NOT LIKE

group

The parameter group contains the list of fields for grouping:

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

More often than not there is no need to indicate the grouping explicitly, the system will do it automatically. For more details, please refer to the section below regarding dynamically defined fields.

order

The parameter order permits to indicate the sorting order:

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

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

offset/limit

The parameters offset and limit will help to limit the number of selected records or implement a page by page selection:

// 10 last 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

The calculated fields (ExpressionField) mentioned in the first part are often more necessary during selection for various calculations with grouping rather than in the description of an entity.

The simplest example, counting the number of entries in an entity, can be performed 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 calculated field not just simply transforms the value of a field but implements an arbitrary SQL expression with the COUNT function.

After the registration of a field in the section runtime it can be referred to in the select section as well as in other sections:

BookTable::getList(array(
	'select' => array('PUBLISH_DATE'),
	'filter' => array('>CNT' => 5)
	'runtime' => array(
		new Entity\ExpressionField('CNT', 'COUNT(*)')
	)
));
// choose the days on which more than 5 books were issued
// SELECT PUBLISH_DATE, COUNT(*) AS CNT FROM my_book GROUP BY PUBLISH_DATE HAVING COUNT(*) > 5

Note. This example shows the automatic grouping mentioned above. The system itself has recognized that grouping should be performed by the field PUBLISH_DATE.

If a calculated field is needed only in the section select (as often is the case) the use of the ‘runtime’ section is not required. You can save time by putting the expression directly into 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 the already existing other Expression field AGE_DAYS was used. Thus, the system permits using nested expressions which will be successively unfolded in the final SQL code.

The runtime section permits registering Expression fields as well as the fields of any other types. The runtime mechanism works in such a way that a new field is added to the entity as if it were described in it in the first place in the method getMap. However, such field is located in the field of vision only within the limits of a single query. It will not be available in the next getList call and will have to be registered anew.

Short calls

There is a form of a short call getById and getByPrimary for rather popular sampling:

BookTable::getById(1);
BookTable::getByPrimary(array('ID' => 1));

// such calls will be similar to the following getList call:
BookTable::getList(array(
	'filter' => array('=ID' => 1)
));

There exists an even shorter version of sampling by ID: getRowById which returns an array with data at once instead of the object DB\Result:

$row = BookTable::getRowById($id);

// the same result can be obtained as follows:
$result = BookTable::getById($id);
$row = $result->fetch();

// or
$result = BookTable::getList(array(
	'filter' => array('=ID' => 1)
));

$row = $result->fetch();

If sampling is not performed by the primary key but rather by any other parameters but still only one entry needs to be obtained, the method getRow can be used:

$row = BookTable:getRow(array(
	'filter' => array('%=TITLE' => 'Patterns%'),
	'order' => array('ID')
));

// a similar result can be obtained as follows:
$result = BookTable::getList(array(
	'filter' => array('%=TITLE' => 'Patterns%'),
	'order' => array('ID')
	'limit' => 1
));

$row = $result->fetch();

Query Object

All the parameters of getList, getRow, and others are submitted at once. The query is executed and the result is returned within the same call. However, there is an alternative configuration option for a query and control over its execution. It is the Entity\Query object:

// obtaining data through getList
$result = BookTable::getList(array(
	'select' => array('ISBN', 'TITLE', 'PUBLISH_DATE')
	'filter' => array('=ID' => 1)
));

// similarly through Entity\Query
$q = new Entity\Query(BookTable::getEntity());
$q->setSelect(array('ISBN', 'TITLE', 'PUBLISH_DATE'));
$q->setFilter(array('=ID' => 1));
$result = $q->exec();

Such an approach may come handy if flexibility is needed for query building. For example, if query parameters are not known in advance and are generated by the program, you can use one Query object instead of variety of arguments and store query parameters in it:

$q = new Entity\Query(BookTable::getEntity());
attachSelect($query);
attachOthers($query);
$result = $query->exec();

function attachSelect(Entity\Query $query)
{
	$query->addSelect('ID');
	
	if (...)
	{
		$query->addSelect('ISBN');
	}
}

function attachOthers(Entity\Query $query)
{
	if (...)
	{
		$query->setFilter(...);
	}
	
	if (...)
	{
		$query->setOrder(...);
	}
}

Also the object Entity\Query permits building a query without executing it. It can be useful in order to execute subqueries or just to obtain query text for its subsequent use:

$q = new Entity\Query(BookTable::getEntity());
$q->setSelect('ID');
$q->setFilter('=PUBLISH_DATE' => new Type\Date);
$sql = $q->getQuery();

file_put_contents('/tmp/today_books.sql', $sql);

// thus, the query "SELECT ID FROM my_book WHERE PUBLISH_DATE='2014-12-31'" will be saved in the file but will not be executed.

The complete list of the methods Entity\Query for implementation of the options described above:

select, group:

  • setSelect, setGroup – establishes an array with field names
  • addSelect, addGroup – adds a field name
  • getSelect, getGroup – returns an array with field names

filter:

  • setFilter – sets up single- or multi-dimensional array with filter description
  • addFilter – adds one filter parameter with a value
  • getFilter – returns current filter description

order:

  • setOrder – sets up an array with field names and sorting order
  • addOrder – adds one field with sorting order
  • getOrder – returns current sorting description

limit/offset:

  • setLimit, setOffset – sets up a value
  • getLimit, getOffset – returns the current value

runtime fields:

  • registerRuntimeField – registers a new temporary field for the original entity

The Query object is the basic element for data sampling. It is also used inside the standard getList. That is why the efficiency of hacks of getList comes to nothing: a hack may work in case a relevant method is called, but it will not work if a similar query is submitted directly through a Query.




Courses developed by «Bitrix», Inc.