Bitrix Framework

CIBlockElement::GetList

Please, be advised, the method CIBlockElement::GetList of Information blocks module can handle product's data (if Commercial Catalog is available). This is detailed in the documentation and actively used in both public and administrative pages and scripts. However, due to system architectural specifics, this causes significant decrease of performance.

Let's make different calls of CIBlockElement::GetList and see, which queries will be executed at the end.

1. First, retrieve an empty selection of products from iblock with ID = 2:
   

   $iterator = \CIBlockElement::GetList(
      array(),
      array('IBLOCK_ID' => 2),
      false,
      false,
      array('ID', 'NAME', 'IBLOCK_ID')
   );
   

   Query, sent to the database:

   SELECT  BE.ID as ID,BE.NAME as NAME,BE.IBLOCK_ID as IBLOCK_ID
   FROM b_iblock B
   INNER JOIN b_lang L ON B.LID=L.LID
   INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
   WHERE 1=1 
      AND (
         ((((BE.IBLOCK_ID = '2'))))
      )
      AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
   

2. Now, add filtering by the product availability to the fetch:
   

   $iterator = \CIBlockElement::GetList(
      array(),
      array('IBLOCK_ID' => 2, 'CATALOG_AVAILABLE' => 'Y'),
      false,
      false,
      array('ID', 'NAME', 'IBLOCK_ID')
   );
   

   This leads to the query as follows:

   SELECT  BE.ID as ID,BE.NAME as NAME,BE.IBLOCK_ID as IBLOCK_ID ,CAT_PR.QUANTITY as CATALOG_QUANTITY,
   IF (CAT_PR.QUANTITY_TRACE = 'D', 'Y', CAT_PR.QUANTITY_TRACE) as CATALOG_QUANTITY_TRACE,
   CAT_PR.QUANTITY_TRACE as CATALOG_QUANTITY_TRACE_ORIG, CAT_PR.WEIGHT as CATALOG_WEIGHT,
   CAT_PR.VAT_ID as CATALOG_VAT_ID, CAT_PR.VAT_INCLUDED as CATALOG_VAT_INCLUDED,
   IF (CAT_PR.CAN_BUY_ZERO = 'D', 'N', CAT_PR.CAN_BUY_ZERO) as CATALOG_CAN_BUY_ZERO,
   CAT_PR.CAN_BUY_ZERO as CATALOG_CAN_BUY_ZERO_ORIG,
   CAT_PR.PURCHASING_PRICE as CATALOG_PURCHASING_PRICE, CAT_PR.PURCHASING_CURRENCY as CATALOG_PURCHASING_CURRENCY,
   CAT_PR.QUANTITY_RESERVED as CATALOG_QUANTITY_RESERVED,
   IF (CAT_PR.SUBSCRIBE = 'D', 'Y', CAT_PR.SUBSCRIBE) as CATALOG_SUBSCRIBE, CAT_PR.SUBSCRIBE as CATALOG_SUBSCRIBE_ORIG,
   CAT_PR.WIDTH as CATALOG_WIDTH, CAT_PR.LENGTH as CATALOG_LENGTH, CAT_PR.HEIGHT as CATALOG_HEIGHT,
   CAT_PR.MEASURE as CATALOG_MEASURE, CAT_PR.TYPE as CATALOG_TYPE, CAT_PR.AVAILABLE as CATALOG_AVAILABLE,
   CAT_PR.BUNDLE as CATALOG_BUNDLE, CAT_PR.PRICE_TYPE as CATALOG_PRICE_TYPE,
   CAT_PR.RECUR_SCHEME_LENGTH as CATALOG_RECUR_SCHEME_LENGTH, CAT_PR.RECUR_SCHEME_TYPE as CATALOG_RECUR_SCHEME_TYPE,
   CAT_PR.TRIAL_PRICE_ID as CATALOG_TRIAL_PRICE_ID, CAT_PR.WITHOUT_ORDER as CATALOG_WITHOUT_ORDER,
   CAT_PR.SELECT_BEST_PRICE as CATALOG_SELECT_BEST_PRICE,
   IF (CAT_PR.NEGATIVE_AMOUNT_TRACE = 'D', 'N', CAT_PR.NEGATIVE_AMOUNT_TRACE) as CATALOG_NEGATIVE_AMOUNT_TRACE,
   CAT_PR.NEGATIVE_AMOUNT_TRACE as CATALOG_NEGATIVE_AMOUNT_TRACE_ORIG, CAT_VAT.RATE as CATALOG_VAT
   
      FROM b_iblock B
      INNER JOIN b_lang L ON B.LID=L.LID
      INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
   
      left join b_catalog_product as CAT_PR on (CAT_PR.ID = BE.ID)
      left join b_catalog_iblock as CAT_IB on ((CAT_PR.VAT_ID IS NULL or CAT_PR.VAT_ID = 0) and CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
      left join b_catalog_vat as CAT_VAT on
            (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
   
      WHERE 1=1 
      AND (
         ((((BE.IBLOCK_ID = '2'))))
         AND ((((CAT_PR.AVAILABLE='Y'))))
      )
      AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
   

   
   This way, any query to product data (filtering, sorting, fetching a field) leads to join of three tables and selection of all product fields.
3. Remove filtration and select a one price type (price type ID - 1):
   

   $iterator = \CIBlockElement::GetList(
      array(),
      array('IBLOCK_ID' => 2),
      false,
      false,
      array('ID', 'NAME', 'IBLOCK_ID', 'CATALOG_CATALOG_GROUP_ID_1')
   );
   

   We'll see that situation became worse:

   SELECT BE.ID as ID,BE.NAME as NAME,BE.IBLOCK_ID as IBLOCK_ID,
   CAT_P1.CATALOG_GROUP_ID as CATALOG_GROUP_ID_1, CAT_P1.ID as CATALOG_PRICE_ID_1,
   CAT_P1.PRICE as CATALOG_PRICE_1, CAT_P1.CURRENCY as CATALOG_CURRENCY_1,
   CAT_P1.QUANTITY_FROM as CATALOG_QUANTITY_FROM_1, CAT_P1.QUANTITY_TO as CATALOG_QUANTITY_TO_1,
   CAT_P1.EXTRA_ID as CATALOG_EXTRA_ID_1,
   'Базовая цена' as CATALOG_GROUP_NAME_1, 'Y' as CATALOG_CAN_ACCESS_1, 'Y' as CATALOG_CAN_BUY_1,
   CAT_PR.QUANTITY as CATALOG_QUANTITY, IF (CAT_PR.QUANTITY_TRACE = 'D', 'Y', CAT_PR.QUANTITY_TRACE) as CATALOG_QUANTITY_TRACE,
   CAT_PR.QUANTITY_TRACE as CATALOG_QUANTITY_TRACE_ORIG, CAT_PR.WEIGHT as CATALOG_WEIGHT,
   CAT_PR.VAT_ID as CATALOG_VAT_ID, CAT_PR.VAT_INCLUDED as CATALOG_VAT_INCLUDED,
   IF (CAT_PR.CAN_BUY_ZERO = 'D', 'N', CAT_PR.CAN_BUY_ZERO) as CATALOG_CAN_BUY_ZERO,
   CAT_PR.CAN_BUY_ZERO as CATALOG_CAN_BUY_ZERO_ORIG, CAT_PR.PURCHASING_PRICE as CATALOG_PURCHASING_PRICE,
   CAT_PR.PURCHASING_CURRENCY as CATALOG_PURCHASING_CURRENCY, CAT_PR.QUANTITY_RESERVED as CATALOG_QUANTITY_RESERVED,
   IF (CAT_PR.SUBSCRIBE = 'D', 'Y', CAT_PR.SUBSCRIBE) as CATALOG_SUBSCRIBE, CAT_PR.SUBSCRIBE as CATALOG_SUBSCRIBE_ORIG,
   CAT_PR.WIDTH as CATALOG_WIDTH, CAT_PR.LENGTH as CATALOG_LENGTH, CAT_PR.HEIGHT as CATALOG_HEIGHT,
   CAT_PR.MEASURE as CATALOG_MEASURE, CAT_PR.TYPE as CATALOG_TYPE, CAT_PR.AVAILABLE as CATALOG_AVAILABLE,
   CAT_PR.BUNDLE as CATALOG_BUNDLE, CAT_PR.PRICE_TYPE as CATALOG_PRICE_TYPE,
   CAT_PR.RECUR_SCHEME_LENGTH as CATALOG_RECUR_SCHEME_LENGTH, CAT_PR.RECUR_SCHEME_TYPE as CATALOG_RECUR_SCHEME_TYPE,
   CAT_PR.TRIAL_PRICE_ID as CATALOG_TRIAL_PRICE_ID, CAT_PR.WITHOUT_ORDER as CATALOG_WITHOUT_ORDER,
   CAT_PR.SELECT_BEST_PRICE as CATALOG_SELECT_BEST_PRICE,
   IF (CAT_PR.NEGATIVE_AMOUNT_TRACE = 'D', 'N', CAT_PR.NEGATIVE_AMOUNT_TRACE) as CATALOG_NEGATIVE_AMOUNT_TRACE,
   CAT_PR.NEGATIVE_AMOUNT_TRACE as CATALOG_NEGATIVE_AMOUNT_TRACE_ORIG, CAT_VAT.RATE as CATALOG_VAT
   FR OM b_iblock B
   INNER JOIN b_lang L ON B.LID=L.LID
   INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
   left join b_catalog_price as CAT_P1 on (CAT_P1.PRODUCT_ID = BE.ID and CAT_P1.CATALOG_GROUP_ID = 1)
   left join b_catalog_product as CAT_PR on (CAT_PR.ID = BE.ID)
   left join b_catalog_iblock as CAT_IB on ((CAT_PR.VAT_ID IS NULL or CAT_PR.VAT_ID = 0) and CAT_IB.IBLOCK_ID = BE.IBLOCK_ID)
   left join b_catalog_vat as CAT_VAT on (CAT_VAT.ID = IF((CAT_PR.VAT_ID IS NULL OR CAT_PR.VAT_ID = 0), CAT_IB.VAT_ID, CAT_PR.VAT_ID))
   WH ERE 1=1
      AND (
         ((((BE.IBLOCK_ID = '2'))))
      )
      AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))
   

   
   If we attempt to additionally retrieve warehouse data as well, the situation will be similar.

Let's highlight interim results:

• You must strictly avoid retrieving prices and available warehouse stock in CIBlockElement::GetList (specifically, if sorting is used, no matter by which fields). This data must be retrieved using separate API calls. By the way, this can be done in standard components starting from version 17.0.
• Filtering and sorting by product fields, prices, warehouses remains. Querying product fields by keys CATALOG_ gives an extra join of three tables. Querying N types of prices or warehouses - join N+3 tables. In addition to increased query time, you can get an error MySql "Too many tables; MySQL can only use 61 tables in a join".

Before the release of catalog 18.6.100 + iblock 18.6.200, all of the abovementioned is related to standard components and Iblock module admin lists (specifically, in the joint view mode for sections and elements, view the details below). New product handling features become available after the these updates are released in CIBlockElement::GetList.

  Startng from Catalog version 18.6.100 + iblock 18.6.200

Starting from iblock version 18.6.200, method keys are updated. You can filter, sort, get by all keys.

Now calling method with filtration by availability looks as follows:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y'),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID')
);

and the query contains only requested data and only a single join:

   SELECT  BE.ID as ID,BE.NAME as NAME,BE.IBLOCK_ID as IBLOCK_ID

   FROM b_iblock B
   INNER JOIN b_lang L ON B.LID=L.LID
   INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
   left join b_catalog_product as PRD on (PRD.ID = BE.ID)

   WHERE 1=1 
   AND (
      ((((BE.IBLOCK_ID = '2'))))
      AND ((((PRD.AVAILABLE='Y'))))
   )
   AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))

Retrieve selection of sizes and weight of available standard products:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y', '=TYPE' => 1),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID', 'WEIGHT', 'WIDTH', 'HEIGHT', 'LENGTH')
);

Query is as follows:

   SELECT  BE.ID as ID,BE.NAME as NAME,BE.IBLOCK_ID as IBLOCK_ID ,
   PRD.WEIGHT as WEIGHT, PRD.WIDTH as WIDTH, PRD.HEIGHT as HEIGHT, PRD.LENGTH as LENGTH

   FROM b_iblock B
   INNER JOIN b_lang L ON B.LID=L.LID
   INNER JOIN b_iblock_element BE ON BE.IBLOCK_ID = B.ID
   left join b_catalog_product as PRD on (PRD.ID = BE.ID)

   WHERE 1=1 
   AND (
      ((((BE.IBLOCK_ID = '2'))))
      AND ((((PRD.AVAILABLE='Y'))))
      AND ((((PRD.TYPE = '1'))))
   )
   AND (((BE.WF_STATUS_ID=1 AND BE.WF_PARENT_ELEMENT_ID IS NULL)))

  Examples

Filtering by price of any type:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y', '=TYPE' => 1, '>PRICE' => 500),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID', 'WEIGHT', 'WIDTH', 'HEIGHT', 'LENGTH')
);

Filtering by price type with code 1 (usually it's a base price):

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y', '=TYPE' => 1, '>PRICE' => 500, '=PRICE_TYPE' => 1),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID', 'WEIGHT', 'WIDTH', 'HEIGHT', 'LENGTH')
);

Or in variant as follows:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y', '=TYPE' => 1, '>PRICE_1' => 500),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID', 'WEIGHT', 'WIDTH', 'HEIGHT', 'LENGTH')
);

Now you can set previously unavailable filters. Select all products with prices of any type from 500 to 1000:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y', '=TYPE' => 1, '>=PRICE' => 500, '<=PRICE' => 1000),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID')
);

Or only price types with code 1,4,5:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y', '=TYPE' => 1, '>=PRICE' => 500, '<=PRICE' => 1000, '@PRICE_TYPE' => [1,4,5]),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID')
);

Select all products with prices in any currency, equivalent to range from 100 to 200 USD:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '=AVAILABLE' => 'Y', '=TYPE' => 1, '>=PRICE' => 100, '<=PRICE' => 200, 'CURRENCY_FOR_SCALE' => 'USD'),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID')
);

Select products with quantity stored at any warehouse not ore than 3:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '<=STORE_AMOUNT' => 3),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID')
);

Select products, stored at 17th warehouse from 5 to 7:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '<=STORE_AMOUNT' => 7, '>=STORE_AMOUNT' => 5, 'STORE_NUMBER' => 17),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID')
);

Or as follows:

$iterator = \CIBlockElement::GetList(
   array(),
   array('IBLOCK_ID' => 2, '<=STORE_AMOUNT_17' => 7, '>=STORE_AMOUNT_17' => 5),
   false,
   false,
   array('ID', 'NAME', 'IBLOCK_ID')
);

Product filtering by availability at specific warehouses:

if(!empty($arParams['STORES'])){ 
    $GLOBALS[$arParams['FILTER_NAME']]['@STORE_NUMBER'] => $arParams['STORES'];
   $GLOBALS[$arParams['FILTER_NAME']]['>STORE_AMOUNT'] = 0;
}

Other example of product filtering by availability at specific warehouses:

if(!empty($arParams['STORES'])){

$storesFilter = [
                     'LOGIC'=>'OR'
            ];
            foreach ($arParams['STORES'] as $store_id){
                $storesFilter[] = ['STORE_NUMBER' => intval($store_id),'>STORE_AMOUNT'=>0];
            }

  $GLOBALS[$arParams['FILTER_NAME']][] = $storesFilter;
        }

Upon installing updates catalog 18.6.100 + iblock 18.6.200, it's strongly recommended to transition your components and scripts to new keys. Performance increase is directly proportional to the number of products in catalog. Accordingly, test section with 1,5 thousand of products resulted in execution speed increase was 30%. Standard components ( catalog.section Компонент выводит список элементов раздела с указанным набором свойств.

Описание компонента «Элементы раздела» в пользовательской документации. , catalog.element Компонент выводит детальную информацию по элементу каталога.

Описание компонента «Элемент каталога детально» в пользовательской документации. , catalog.top Компонент выводит в таблице top элементов из всех разделов в соответствии с заданной сортировкой (используется как правило на главной странице сайта).

Описание компонента «top элементов каталога» в пользовательской документации. ), as well as all descendant components \Bitrix\Iblock\Component\Base are moved to new filters.