protected static function findUsingIndex($parameters)
{
$query = array();
$dbConnection = Main\HttpApplication::getConnection();
$dbHelper = Main\HttpApplication::getConnection()->getSqlHelper();
$filter = static::parseFilter($parameters['filter']);
$filterByPhrase = isset($filter['PHRASE']) && strlen($filter['PHRASE']['VALUE']);
if ($filterByPhrase) {
$bounds = WordTable::getBoundsForPhrase($filter['PHRASE']['VALUE']);
$firstBound = array_shift($bounds);
$k = 0;
foreach ($bounds as $bound) {
$query['JOIN'][] = " inner join " . ChainTable::getTableName() . " A" . $k . " on A.LOCATION_ID = A" . $k . ".LOCATION_ID and (\n\n\t\t\t\t\t" . ($bound['INF'] == $bound['SUP'] ? " A" . $k . ".POSITION = '" . $bound['INF'] . "'" : " A" . $k . ".POSITION >= '" . $bound['INF'] . "' and A" . $k . ".POSITION <= '" . $bound['SUP'] . "'") . "\n\t\t\t\t)";
$k++;
}
$query['WHERE'][] = $firstBound['INF'] == $firstBound['SUP'] ? " A.POSITION = '" . $firstBound['INF'] . "'" : " A.POSITION >= '" . $firstBound['INF'] . "' and A.POSITION <= '" . $firstBound['SUP'] . "'";
$mainTableJoinCondition = 'A.LOCATION_ID';
} else {
$mainTableJoinCondition = 'L.ID';
}
// site link search
if (strlen($filter['SITE_ID']['VALUE']) && SiteLinkTable::checkTableExists()) {
$query['JOIN'][] = "inner join " . SiteLinkTable::getTableName() . " SL on SL.LOCATION_ID = " . $mainTableJoinCondition . " and SL.SITE_ID = '" . $dbHelper->forSql($filter['SITE_ID']['VALUE']) . "'";
}
// process filter and select statements
// at least, we support here basic field selection and filtration + NAME.NAME and NAME.LANGUAGE_ID
$map = Location\LocationTable::getMap();
$nameRequired = false;
$locationRequred = false;
if (is_array($parameters['select'])) {
foreach ($parameters['select'] as $alias => $field) {
if ($field == 'NAME.NAME' || $field == 'NAME.LANGUAGE_ID') {
$nameRequired = true;
continue;
}
if (!isset($map[$field]) || !in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean')) || isset($map[$field]['expression'])) {
unset($parameters['select'][$alias]);
}
$locationRequred = true;
}
}
foreach ($filter as $field => $params) {
if ($field == 'NAME.NAME' || $field == 'NAME.LANGUAGE_ID') {
$nameRequired = true;
continue;
}
if (!isset($map[$field]) || !in_array($map[$field]['data_type'], array('integer', 'string', 'float', 'boolean')) || isset($map[$field]['expression'])) {
unset($filter[$field]);
}
$locationRequred = true;
}
// data join, only if extended select specified
if ($locationRequred && $filterByPhrase) {
$query['JOIN'][] = "inner join " . Location\LocationTable::getTableName() . " L on A.LOCATION_ID = L.ID";
}
if ($nameRequired) {
$query['JOIN'][] = "inner join " . Location\Name\LocationTable::getTableName() . " NAME on NAME.LOCATION_ID = " . $mainTableJoinCondition;
}
// and N.LANGUAGE_ID = 'ru'
// making select
if (is_array($parameters['select'])) {
$select = array();
foreach ($parameters['select'] as $alias => $field) {
if ($field != 'NAME.NAME' && $field != 'NAME.LANGUAGE_ID') {
$field = 'L.' . $dbHelper->forSql($field);
}
if ((string) $alias === (string) intval($alias)) {
$select[] = $field;
} else {
$select[] = $field . ' as ' . $dbHelper->forSql($alias);
}
}
$sqlSelect = implode(', ', $select);
} else {
$sqlSelect = $mainTableJoinCondition . ' as ID';
}
// making filter
foreach ($filter as $field => $params) {
if ($field != 'NAME.NAME' && $field != 'NAME.LANGUAGE_ID') {
$field = 'L.' . $dbHelper->forSql($field);
}
$query['WHERE'][] = $field . ' ' . $params['OP'] . " '" . $dbHelper->forSql($params['VALUE']) . "'";
}
if ($filterByPhrase) {
$sql = "\n\t\t\t\tselect " . ($dbConnection->getType() == 'oracle' ? '' : 'distinct') . " \n\t\t\t\t\t" . $sqlSelect . (\Bitrix\Sale\Location\DB\Helper::needSelectFieldsInOrderByWhenDistinct() ? ', A.RELEVANCY' : '') . "\n\n\t\t\t\tfrom " . ChainTable::getTableName() . " A\n\n\t\t\t\t\t" . implode(' ', $query['JOIN']) . "\n\n\t\t\t\t" . (count($query['WHERE']) ? 'where ' : '') . implode(' and ', $query['WHERE']) . "\n\n\t\t\t\torder by A.RELEVANCY asc\n\t\t\t";
} else {
$sql = "\n\n\t\t\t\tselect \n\t\t\t\t\t" . $sqlSelect . "\n\n\t\t\t\tfrom " . Location\LocationTable::getTableName() . " L\n\n\t\t\t\t\t" . implode(' ', $query['JOIN']) . "\n\n\t\t\t\t" . (count($query['WHERE']) ? 'where ' : '') . implode(' and ', $query['WHERE']) . "\n\t\t\t";
}
$offset = intval($parameters['offset']);
$limit = intval($parameters['limit']);
if ($limit) {
$sql = $dbHelper->getTopSql($sql, $limit, $offset);
}
$res = $dbConnection->query($sql);
return $res;
}