Bitrix Framework

Logic for LIMIT

Intuitive standby for LIMIT logic:

   $iblockEntity = IblockTable::compileEntity(…);
   
    $query = $iblockEntity->getDataClass()::query()
        ->addSelect('NAME')
        ->addSelect('MULTI_PROP_1')
    ->setLimit(5);

    $elements = $query->fetchCollection();

You may not expect to get 5 items in this example. The retrieved data limit is indicated at the SQL query level, not at the object level:

    SELECT ... FROM `b_iblock_element`
        LEFT JOIN `b_iblock_element_property` ...
    LIMIT 5

In fact, retrieving 5 property values with corresponding items results in error. That's why the retrieved selection may contain less than 5 items, or a single item without partially fetched property values.

Field selection for relations in a single query

Selecting several relation fields in a single query results in Cartesian join for all records. For example:

    $iblockEntity = IblockTable::compileEntity(…);
    
    $query = $iblockEntity->getDataClass()::query()
        ->addSelect('NAME')
        ->addSelect('MULTI_PROP_1')
        ->addSelect('MULTI_PROP_2')
        ->addSelect('MULTI_PROP_3');
    
    $elements = $query->fetchCollection();

Executes the following query:

    SELECT ... FROM `b_iblock_element`
        LEFT JOIN `b_iblock_element_property` ... // 15 property values
        LEFT JOIN `b_iblock_element_property` ... // 7 property values
        LEFT JOIN `b_iblock_element_property` ... // 11 property values 

And if intuitively it seems that 15 + 7 + 11 = 33 strings are retrieved, in fact, retrieves 15 * 7 * 11 = 1155 strings. In cases when query contains a lot more properties or values, the resulting records count can be in millions, subsequently leading to the insufficient app's memory for getting a complete result.

Solving the issues

To avoid such issues, the following class Bitrix\Main\ORM\Query\QueryHelper was added along with a universal method decompose:

    /**
    ** Query decomposition with relations 1:N and N:M
    ** 
    ** @param Query $query
    ** @param bool $fairLimit. Setting this option first selects object IDs and the following query selects the rest of data with ID filter
    ** @param bool $separateRelations. Each 1:N or N:M relation is selected using separate queries under this option
    ** @return Collection
    **/
    public static function decompose(Query $query, $fairLimit = true, $separateRelations = true)

Parameter fairLimit leads to two queries: first, selects record primary with query-defined Limit / Offset, and then all relations are selected by a single query for all primary.

Additional parameter separateRelations allows executing a separate query per each relation, to avoid Cartesian join for all records.

Returns a complete object collection with already merged data as the result.

Sorting is applied at the primary selection with corresponding formatting. Sorting of relation objects at the top level is not that significant compared to when working with arrays.