Finder::findUsingIndex

static function findUsingIndex($parameters)
{
	$query = array();

	$dbConnection = MainHttpApplication::getConnection();
	$dbHelper = MainHttpApplication::getConnection()->getSqlHelper();

	$filter = static::parseFilter($parameters['filter']);

	$filterByPhrase = isset($filter['PHRASE']) && mb_strlen($filter['PHRASE']['VALUE']);

	if($filterByPhrase) // filter by phrase
	{
		$bounds = WordTable::getBoundsForPhrase($filter['PHRASE']['VALUE']);
		if (!empty($bounds))
		{
			$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 (

					".($bound['INF'] == $bound['SUP']
						? " A".$k.".POSITION = '".$bound['INF']."'"
						: " A".$k.".POSITION >= '".$bound['INF']."' and A".$k.".POSITION <= '".$bound['SUP']."'"
					)."
				)";
				$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 (
		isset($filter['SITE_ID']['VALUE'])
		&& is_string($filter['SITE_ID']['VALUE'])
		&& $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 = LocationLocationTable::getMap();
	$nameRequired = false;
	$locationRequired = 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]);
			}

			$locationRequired = 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]);
		}

		$locationRequired = true;
	}

	// data join, only if extended select specified

	if($locationRequired && $filterByPhrase)
		$query['JOIN'][] = "inner join ".LocationLocationTable::getTableName()." L on A.LOCATION_ID = L.ID";

	if($nameRequired)
		$query['JOIN'][] = "inner join ".LocationNameLocationTable::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);

		$values = $params['VALUE'];

		if(!is_array($values))
			$values = array($values);

		foreach($values as $value)
			$query['WHERE'][] = $field.' '.$params['OP']." '".$dbHelper->forSql($value)."'";
	}

	if($filterByPhrase)
	{
		$sql = "
			select ".($dbConnection->getType() != 'mysql' ? '' : 'distinct')/*fix this in more clever way later*/." 
				".$sqlSelect.(BitrixSaleLocationDBHelper::needSelectFieldsInOrderByWhenDistinct() ? ', A.RELEVANCY' : '')."

			from ".ChainTable::getTableName()." A

				".implode(' ', $query['JOIN'])."

			".(!empty($query['WHERE']) ? 'where ' : '').implode(' and ', $query['WHERE'])."

			order by A.RELEVANCY asc
		";
	}
	else
	{
		$sql = "

			select 
				".$sqlSelect."

			from ".LocationLocationTable::getTableName()." L

				".implode(' ', $query['JOIN'])."

			".(!empty($query['WHERE']) ? 'where ' : '').implode(' and ', $query['WHERE'])."
		";
	}

	$offset = (int)($parameters['offset'] ?? 0);
	$limit = (int)($parameters['limit'] ?? 0);

	if ($limit)
	{
		$sql = $dbHelper->getTopSql($sql, $limit, $offset);
	}

	return $dbConnection->query($sql);
}