/**
* 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;
}
