コード例 #1
0
ファイル: finder.php プロジェクト: DarneoStudio/bitrix
 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;
 }