/** * Returns filter join with index tables. * <p> * $filter parameters same as for CIBlockElement::getList * <p> * $facetTypes allows to get only "checkboxes" or "prices" and such. * * @param array $filter Filter to apply additionally to filter elements. * @param array $facetTypes Which facet types will be used. * @param integer $facetId Which facet category filter should not be applied. * * @return \Bitrix\Main\DB\Result */ public function query(array $filter, array $facetTypes = array(), $facetId = 0) { $connection = \Bitrix\Main\Application::getConnection(); $sqlHelper = $connection->getSqlHelper(); $facetFilter = $this->getFacetFilter($facetTypes); if (!$facetFilter) { return false; } if ($filter) { $filter["IBLOCK_ID"] = $this->iblockId; $element = new \CIBlockElement(); $element->strField = "ID"; $element->getList(array(), $filter, false, false, array("ID")); $elementFrom = $element->sFrom; $elementWhere = $element->sWhere; } else { $elementFrom = ""; $elementWhere = ""; } $facets = array(); if ($facetId) { $facets[] = array("where" => $this->getWhere($facetId), "facet" => array($facetId)); } else { foreach ($facetFilter as $facetId) { $where = $this->getWhere($facetId); $found = false; foreach ($facets as $i => $facetWhereAndFacets) { if ($facetWhereAndFacets["where"] == $where) { $facets[$i]["facet"][] = $facetId; $found = true; break; } } if (!$found) { $facets[] = array("where" => $where, "facet" => array($facetId)); } } } $sqlUnion = array(); foreach ($facets as $facetWhereAndFacets) { $where = $facetWhereAndFacets["where"]; $facetFilter = $facetWhereAndFacets["facet"]; $sqlSearch = array("1=1"); if (empty($where)) { $sqlUnion[] = "\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tF.FACET_ID\n\t\t\t\t\t\t,F.VALUE\n\t\t\t\t\t\t,MIN(F.VALUE_NUM) MIN_VALUE_NUM\n\t\t\t\t\t\t,MAX(F.VALUE_NUM) MAX_VALUE_NUM\n\t\t\t\t\t\t" . ($connection instanceof \Bitrix\Main\DB\MysqlCommonConnection ? ",MAX(case when LOCATE('.', F.VALUE_NUM) > 0 then LENGTH(SUBSTRING_INDEX(F.VALUE_NUM, '.', -1)) else 0 end)" : ",MAX(" . $sqlHelper->getLengthFunction("ABS(F.VALUE_NUM) - FLOOR(ABS(F.VALUE_NUM))") . "+1-" . $sqlHelper->getLengthFunction("0.1") . ")") . " VALUE_FRAC_LEN\n\t\t\t\t\t\t,COUNT(DISTINCT F.ELEMENT_ID) ELEMENT_COUNT\n\t\t\t\t\tFROM\n\t\t\t\t\t\t" . ($elementFrom ? $elementFrom . "\n\t\t\t\t\t\t\tINNER JOIN " . $this->storage->getTableName() . " F ON BE.ID = F.ELEMENT_ID" : $this->storage->getTableName() . " F") . "\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tF.SECTION_ID = " . $this->sectionId . "\n\t\t\t\t\t\tand F.FACET_ID in (" . implode(",", $facetFilter) . ")\n\t\t\t\t\t\t" . $elementWhere . "\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\tF.FACET_ID, F.VALUE\n\t\t\t\t"; continue; } elseif (count($where) == 1) { $fcJoin = "INNER JOIN " . $this->storage->getTableName() . " FC on FC.ELEMENT_ID = BE.ID"; foreach ($where as $facetWhere) { $sqlWhere = $this->whereToSql($facetWhere, "FC"); if ($sqlWhere) { $sqlSearch[] = $sqlWhere; } } } elseif (count($where) <= 5) { $subJoin = ""; $subWhere = ""; $i = 0; foreach ($where as $facetWhere) { if ($i == 0) { $subJoin .= "FROM " . $this->storage->getTableName() . " FC{$i}\n"; } else { $subJoin .= "INNER JOIN " . $this->storage->getTableName() . " FC{$i} ON FC{$i}.ELEMENT_ID = FC0.ELEMENT_ID\n"; } $sqlWhere = $this->whereToSql($facetWhere, "FC{$i}"); if ($sqlWhere) { if ($subWhere) { $subWhere .= "\nAND " . $sqlWhere; } else { $subWhere .= $sqlWhere; } } $i++; } $fcJoin = "\n\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\tSELECT FC0.ELEMENT_ID\n\t\t\t\t\t\t{$subJoin}\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t{$subWhere}\n\t\t\t\t\t) FC on FC.ELEMENT_ID = BE.ID\n\t\t\t\t"; } else { $condition = array(); foreach ($where as $facetWhere) { $sqlWhere = $this->whereToSql($facetWhere, "FC0"); if ($sqlWhere) { $condition[] = $sqlWhere; } } $fcJoin = "\n\t\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\t\tSELECT FC0.ELEMENT_ID\n\t\t\t\t\t\t\tFROM " . $this->storage->getTableName() . " FC0\n\t\t\t\t\t\t\tWHERE FC0.SECTION_ID = " . $this->sectionId . "\n\t\t\t\t\t\t\tAND (\n\t\t\t\t\t\t\t(" . implode(")OR(", $condition) . ")\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\tGROUP BY FC0.ELEMENT_ID\n\t\t\t\t\t\tHAVING count(DISTINCT FC0.FACET_ID) = " . count($condition) . "\n\t\t\t\t\t\t) FC on FC.ELEMENT_ID = BE.ID\n\t\t\t\t\t"; } $sqlUnion[] = "\n\t\t\t\tSELECT\n\t\t\t\t\tF.FACET_ID\n\t\t\t\t\t,F.VALUE\n\t\t\t\t\t,MIN(F.VALUE_NUM) MIN_VALUE_NUM\n\t\t\t\t\t,MAX(F.VALUE_NUM) MAX_VALUE_NUM\n\t\t\t\t\t" . ($connection instanceof \Bitrix\Main\DB\MysqlCommonConnection ? ",MAX(case when LOCATE('.', F.VALUE_NUM) > 0 then LENGTH(SUBSTRING_INDEX(F.VALUE_NUM, '.', -1)) else 0 end)" : ",MAX(" . $sqlHelper->getLengthFunction("ABS(F.VALUE_NUM) - FLOOR(ABS(F.VALUE_NUM))") . "+1-" . $sqlHelper->getLengthFunction("0.1") . ")") . " VALUE_FRAC_LEN\n\t\t\t\t\t,COUNT(DISTINCT F.ELEMENT_ID) ELEMENT_COUNT\n\t\t\t\tFROM\n\t\t\t\t\t" . $this->storage->getTableName() . " F\n\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\tSELECT BE.ID\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t" . ($elementFrom ? $elementFrom : "b_iblock_element BE") . "\n\t\t\t\t\t\t\t" . $fcJoin . "\n\t\t\t\t\t\tWHERE " . implode(" AND ", $sqlSearch) . "\n\t\t\t\t\t\t" . $elementWhere . "\n\t\t\t\t\t) E ON E.ID = F.ELEMENT_ID\n\t\t\t\tWHERE\n\t\t\t\t\tF.SECTION_ID = " . $this->sectionId . "\n\t\t\t\t\tand F.FACET_ID in (" . implode(",", $facetFilter) . ")\n\t\t\t\tGROUP BY\n\t\t\t\t\tF.FACET_ID, F.VALUE\n\t\t\t"; } $result = $connection->query(implode("\nUNION ALL\n", $sqlUnion)); return $result; }
/** * Returns filter join with index tables. * * @param array &$filter Filter which may be rewritten. * @param array &$sqlSearch Additional result of rewrite. * * @return string */ public function getFilterSql(&$filter, &$sqlSearch) { if (array_key_exists("FACET_OPTIONS", $filter)) { if (is_array($filter["FACET_OPTIONS"])) { $this->options = $filter["FACET_OPTIONS"]; } unset($filter["FACET_OPTIONS"]); } $this->distinct = false; $fcJoin = ""; $toUnset = array(); if (!is_array($filter["IBLOCK_ID"]) && $filter["IBLOCK_ID"] > 0 && !is_array($filter["SECTION_ID"]) && $filter["SECTION_ID"] > 0 && isset($filter["ACTIVE"]) && $filter["ACTIVE"] === "Y") { $where = array(); $toUnset[] = array(&$filter, "SECTION_ID"); if ($filter["INCLUDE_SUBSECTIONS"] === "Y") { $subsectionsCondition = ""; $toUnset[] = array(&$filter, "INCLUDE_SUBSECTIONS"); } else { $subsectionsCondition = "INCLUDE_SUBSECTIONS=1"; if (array_key_exists("INCLUDE_SUBSECTIONS", $filter)) { $toUnset[] = array(&$filter, "INCLUDE_SUBSECTIONS"); } } $hasAdditionalFilters = false; $this->fillWhere($where, $hasAdditionalFilters, $toUnset, $filter); if (!$where) { $where[] = array("TYPE" => Storage::DICTIONARY, "OP" => "=", "FACET_ID" => 1, "VALUES" => array(0)); } if (isset($filter["=ID"]) && is_object($filter["=ID"]) && $filter["=ID"]->arFilter["IBLOCK_ID"] == $this->facet->getSkuIblockId() && $filter["=ID"]->strField === "PROPERTY_" . $this->facet->getSkuPropertyId()) { $hasAdditionalFilters = false; $this->fillWhere($where, $hasAdditionalFilters, $toUnset, $filter["=ID"]->arFilter); if (!$hasAdditionalFilters) { $toUnset[] = array(&$filter, "=ID"); } } if ($where) { $filter["SECTION_ID"] = isset($filter["SECTION_ID"]) ? (int) $filter["SECTION_ID"] : 0; $this->facet->setSectionId($filter["SECTION_ID"]); if ($this->options) { if ($this->options["CURRENCY_CONVERSION"]) { $this->facet->enableCurrencyConversion($this->options["CURRENCY_CONVERSION"]["TO"], $this->options["CURRENCY_CONVERSION"]["FROM"]); } } $distinctSelectCapable = \Bitrix\Main\Application::getConnection()->getType() == "mysql"; if (count($where) == 1 && $distinctSelectCapable) { $this->distinct = true; $fcJoin = "INNER JOIN " . $this->storage->getTableName() . " FC on FC.ELEMENT_ID = BE.ID"; foreach ($where as $facetFilter) { $sqlWhere = $this->facet->whereToSql($facetFilter, "FC", $subsectionsCondition); if ($sqlWhere) { $sqlSearch[] = $sqlWhere; } } } elseif (count($where) <= 5) { $subJoin = ""; $subWhere = ""; $i = 0; foreach ($where as $facetFilter) { if ($i == 0) { $subJoin .= "FROM " . $this->storage->getTableName() . " FC{$i}\n"; } else { $subJoin .= "INNER JOIN " . $this->storage->getTableName() . " FC{$i} ON FC{$i}.ELEMENT_ID = FC0.ELEMENT_ID\n"; } $sqlWhere = $this->facet->whereToSql($facetFilter, "FC{$i}", $subsectionsCondition); if ($sqlWhere) { if ($subWhere) { $subWhere .= "\nAND " . $sqlWhere; } else { $subWhere .= $sqlWhere; } } $i++; } $fcJoin = "\n\t\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\t\tSELECT " . ($distinctSelectCapable ? "" : "DISTINCT") . " FC0.ELEMENT_ID\n\t\t\t\t\t\t\t{$subJoin}\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t{$subWhere}\n\t\t\t\t\t\t) FC on FC.ELEMENT_ID = BE.ID\n\t\t\t\t\t"; } else { $condition = array(); foreach ($where as $facetFilter) { $sqlWhere = $this->facet->whereToSql($facetFilter, "FC0", $subsectionsCondition); if ($sqlWhere) { $condition[] = $sqlWhere; } } $fcJoin = "\n\t\t\t\t\t\tINNER JOIN (\n\t\t\t\t\t\t\tSELECT FC0.ELEMENT_ID\n\t\t\t\t\t\t\tFROM " . $this->storage->getTableName() . " FC0\n\t\t\t\t\t\t\tWHERE FC0.SECTION_ID = " . $filter["SECTION_ID"] . "\n\t\t\t\t\t\t\tAND (\n\t\t\t\t\t\t\t(" . implode(")OR(", $condition) . ")\n\t\t\t\t\t\t\t)\n\t\t\t\t\t\tGROUP BY FC0.ELEMENT_ID\n\t\t\t\t\t\tHAVING count(DISTINCT FC0.FACET_ID) = " . count($condition) . "\n\t\t\t\t\t\t) FC on FC.ELEMENT_ID = BE.ID\n\t\t\t\t\t"; } foreach ($toUnset as $command) { unset($command[0][$command[1]]); } } else { $fcJoin = ""; } } return $fcJoin; }