$sql = [
'select' => [
"ID",
],
'runtime' => [
new \Bitrix\Main\Entity\ReferenceField( // получим свойство связи с SKU
'CML2_LINK',
'\Bitrix\Iblock\PropertyTable',
[
"=ref.CODE" => new \Bitrix\Main\DB\SqlExpression('?', 'CML2_LINK'),
],
["join_type" => "left"]
),
new \Bitrix\Main\Entity\ReferenceField( // получим свойство артикула
'CML2_ARTICLE',
'\Bitrix\Iblock\PropertyTable',
[
"=ref.CODE" => new \Bitrix\Main\DB\SqlExpression('?', 'CML2_ARTICLE'),
],
["join_type" => "left"]
),
new \Bitrix\Main\Entity\ReferenceField( // получим свойство штрихкода
'CML2_BAR_CODE',
'\Bitrix\Iblock\PropertyTable',
[
"=ref.CODE" => new \Bitrix\Main\DB\SqlExpression('?', 'CML2_BAR_CODE'),
],
["join_type" => "left"]
),
new \Bitrix\Main\Entity\ReferenceField(
'SKU_LINK_ID',
'\Bitrix\Iblock\ElementPropertyTable',
[
"=this.ID" => "ref.VALUE",
"=this.CML2_LINK.ID" => "ref.IBLOCK_PROPERTY_ID",
],
["join_type" => "inner"]
),
new \Bitrix\Main\Entity\ReferenceField(
'SKU_ITEM',
'\Bitrix\Iblock\ElementTable',
[
"=this.SKU_LINK_ID.IBLOCK_ELEMENT_ID" => "ref.ID",
],
["join_type" => "left"]
),
new \Bitrix\Main\Entity\ReferenceField(
'SKU_ITEM_PROPERTY_CML2_ARTICLE',
'\Bitrix\Iblock\ElementPropertyTable',
[
"=this.SKU_ITEM.ID" => "ref.IBLOCK_ELEMENT_ID",
"=this.CML2_ARTICLE.ID" => "ref.IBLOCK_PROPERTY_ID",
],
["join_type" => "left"]
),
new \Bitrix\Main\Entity\ReferenceField(
'SKU_ITEM_PROPERTY_CML2_BAR_CODE',
'\Bitrix\Iblock\ElementPropertyTable',
[
"=this.SKU_ITEM.ID" => "ref.IBLOCK_ELEMENT_ID",
"=this.CML2_BAR_CODE.ID" => "ref.IBLOCK_PROPERTY_ID",
],
["join_type" => "left"]
),
new \Bitrix\Main\Entity\ReferenceField(
'SECTION',
'\Bitrix\Iblock\SectionTable',
[
"=this.IBLOCK_SECTION_ID" => "ref.ID",
],
["join_type" => "left"]
),
new \Bitrix\Main\Entity\ReferenceField(
'TOP_SECTION',
'\Bitrix\Iblock\SectionTable',
[
">this.SECTION.LEFT_MARGIN" => "ref.LEFT_MARGIN",
"<this.SECTION.RIGHT_MARGIN" => "ref.RIGHT_MARGIN",
"=ref.DEPTH_LEVEL" => new \Bitrix\Main\DB\SqlExpression('?', '1'),
],
["join_type" => "left"]
),
],
'group' => [
'ID',
//'SKU_ITEM.ID',
],
'filter' => [
"ACTIVE" => "Y",
"IBLOCK_ID" => $arParams["IBLOCK_ID"], // только товары
"!=TOP_SECTION.CODE" => $_SESSION["SKIP_INDEX_SECTION_CODE"],
],
'order' => ['ID' => "ASC"],
'cache' => ($arParams["CACHE_TYPE"] != "N" ? ["ttl" => $arParams["CACHE_TIME"], "cache_joins" => true] : null),
'limit' => 50000
];
$iterator = \Bitrix\Iblock\ElementTable::getList($sql);
while ($filtredElementArr = $iterator->fetch()) {
$ids[] = $filtredElementArr["ID"];
}
|
Генерируется примерно такой запрос:
SELECT
`iblock_element`.`ID` AS `ELEMENT_ID`
FROM `b_iblock_element` `iblock_element`
LEFT JOIN `b_iblock_property` `iblock_element_cml2_link` ON `iblock_element_cml2_link`.`CODE` = 'CML2_LINK'
LEFT JOIN `b_iblock_property` `iblock_element_cml2_article` ON `iblock_element_cml2_article`.`CODE` = 'CML2_ARTICLE'
LEFT JOIN `b_iblock_property` `iblock_element_cml2_bar_code` ON `iblock_element_cml2_bar_code`.`CODE` = 'CML2_BAR_CODE'
INNER JOIN `b_iblock_element_property` `iblock_element_sku_link_id` ON `iblock_element`.`ID` = `iblock_element_sku_link_id`.`VALUE`
AND `iblock_element_cml2_link`.`ID` = `iblock_element_sku_link_id`.`IBLOCK_PROPERTY_ID`
LEFT JOIN `b_iblock_element` `iblock_element_sku_item` ON `iblock_element_sku_link_id`.`IBLOCK_ELEMENT_ID` = `iblock_element_sku_item`.`ID`
LEFT JOIN `b_iblock_element_property` `iblock_element_sku_item_property_cml2_article` ON `iblock_element_sku_item`.`ID` = `iblock_element_sku_item_property_cml2_article`.`IBLOCK_ELEMENT_ID`
AND `iblock_element_cml2_article`.`ID` = `iblock_element_sku_item_property_cml2_article`.`IBLOCK_PROPERTY_ID`
LEFT JOIN `b_iblock_element_property` `iblock_element_sku_item_property_cml2_bar_code` ON `iblock_element_sku_item`.`ID` = `iblock_element_sku_item_property_cml2_bar_code`.`IBLOCK_ELEMENT_ID`
AND `iblock_element_cml2_bar_code`.`ID` = `iblock_element_sku_item_property_cml2_bar_code`.`IBLOCK_PROPERTY_ID`
LEFT JOIN `b_iblock_section` `iblock_element_section` ON `iblock_element`.`IBLOCK_SECTION_ID` = `iblock_element_section`.`ID`
LEFT JOIN `b_iblock_section` `iblock_element_top_section` ON `iblock_element_section`.`LEFT_MARGIN` > `iblock_element_top_section`.`LEFT_MARGIN`
AND `iblock_element_section`.`RIGHT_MARGIN` < `iblock_element_top_section`.`RIGHT_MARGIN`
AND `iblock_element_top_section`.`DEPTH_LEVEL` = '1'
WHERE UPPER(`iblock_element`.`ACTIVE`) like upper('Y')
AND `iblock_element`.`IBLOCK_ID` = 17
AND (`iblock_element_top_section`.`CODE` IS NULL OR `iblock_element_top_section`.`CODE` not in ('rasprodazha_20'))
ORDER BY `ELEMENT_ID` ASC
LIMIT 0, 50 |