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.