src/SearchBundle/objects/db/TShopSearchIndexer.class.php
protected static function GetWordListQuery($aTerms, $sLanguageId, $aFieldRestrictions = null, $sTypeOfFieldRestriction = 'OR')
    {
        $aTableQueries = array();
        // get affected tables
        $aTableList = array();
        foreach ($aTerms as $sTerm) {
            $sTableName = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sTerm));
            if (!in_array($sTableName, $aTableList)) {
                $aTableList[] = $sTableName;
            }

            if (false == TdbShopSearchIndexer::searchWithAND()) {
                $sSoundEX = TdbShopSearchIndexer::GetSoundexForWord($sTerm);
                $sSoundTable = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sSoundEX));
                if (!in_array($sSoundTable, $aTableList)) {
                    $aTableList[] = $sSoundTable;
                }
            }
        }

        $aFieldRestrictionQueries = array();
        if (!is_null($aFieldRestrictions)) {
            reset($aTableList);
            foreach ($aTableList as $sTableName) {
                $aFieldRestrictionQueries[$sTableName] = " ('".implode("','", $aFieldRestrictions)."')";
            }
        }

        // build query
        $aTableQueries = array();
        reset($aTerms);
        $sLanguageId = MySqlLegacySupport::getInstance()->real_escape_string($sLanguageId);
        foreach ($aTerms as $sTerm) {
            $sTermTable = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sTerm));
            if (!array_key_exists($sTermTable, $aTableQueries)) {
                $aTableQueries[$sTermTable] = array();
            }
            $sTmpQuery = '';
            $sTmpQuery .= " (`{$sTermTable}`.`substring` = '".MySqlLegacySupport::getInstance()->real_escape_string($sTerm)."' AND (`{$sTermTable}`.`cms_language_id` = '{$sLanguageId}' OR `{$sTermTable}`.`cms_language_id` = '')";
            if (is_array($aFieldRestrictions)) {
                $sTmpQuery .= " AND `{$sTermTable}`.`shop_search_field_weight_id` IN {$aFieldRestrictionQueries[$sTermTable]}";
            }
            $sTmpQuery .= ')';
            $aTableQueries[$sTermTable][] = $sTmpQuery;

            if (false == TdbShopSearchIndexer::searchWithAND()) {
                //OR `{$sSoundTable}`.`substring` = '".MySqlLegacySupport::getInstance()->real_escape_string($sSoundEX)."')
                $sSoundEX = TdbShopSearchIndexer::GetSoundexForWord($sTerm);
                if ('0000' != $sSoundEX) {
                    $sSoundTable = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sSoundEX));

                    $sTmpQuerySoundex = '';
                    $sTmpQuerySoundex .= " (`{$sSoundTable}`.`substring` = '".MySqlLegacySupport::getInstance()->real_escape_string($sSoundEX)."' AND (`{$sSoundTable}`.`cms_language_id` = '{$sLanguageId}' OR `{$sSoundTable}`.`cms_language_id` = '')";
                    if (is_array($aFieldRestrictions)) {
                        $sTmpQuerySoundex .= " AND `{$sSoundTable}`.`shop_search_field_weight_id` IN {$aFieldRestrictionQueries[$sSoundTable]}";
                    }
                    $sTmpQuerySoundex .= ')';
                    $aTableQueries[$sSoundTable][] = $sTmpQuerySoundex;
                }
            }
        }

        return $aTableQueries;
    }
protected static function GetWordListQuery($aTerms, $sLanguageId, $aFieldRestrictions = null, $sTypeOfFieldRestriction = 'OR')
    {
        $aTableQueries = array();
        // get affected tables
        $aTableList = array();
        foreach ($aTerms as $sTerm) {
            $sTableName = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sTerm));
            if (!in_array($sTableName, $aTableList)) {
                $aTableList[] = $sTableName;
            }

            if (false == TdbShopSearchIndexer::searchWithAND()) {
                $sSoundEX = TdbShopSearchIndexer::GetSoundexForWord($sTerm);
                $sSoundTable = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sSoundEX));
                if (!in_array($sSoundTable, $aTableList)) {
                    $aTableList[] = $sSoundTable;
                }
            }
        }

        $aFieldRestrictionQueries = array();
        if (!is_null($aFieldRestrictions)) {
            reset($aTableList);
            foreach ($aTableList as $sTableName) {
                $aFieldRestrictionQueries[$sTableName] = " ('".implode("','", $aFieldRestrictions)."')";
            }
        }

        // build query
        $aTableQueries = array();
        reset($aTerms);
        $sLanguageId = MySqlLegacySupport::getInstance()->real_escape_string($sLanguageId);
        foreach ($aTerms as $sTerm) {
            $sTermTable = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sTerm));
            if (!array_key_exists($sTermTable, $aTableQueries)) {
                $aTableQueries[$sTermTable] = array();
            }
            $sTmpQuery = '';
            $sTmpQuery .= " (`{$sTermTable}`.`substring` = '".MySqlLegacySupport::getInstance()->real_escape_string($sTerm)."' AND (`{$sTermTable}`.`cms_language_id` = '{$sLanguageId}' OR `{$sTermTable}`.`cms_language_id` = '')";
            if (is_array($aFieldRestrictions)) {
                $sTmpQuery .= " AND `{$sTermTable}`.`shop_search_field_weight_id` IN {$aFieldRestrictionQueries[$sTermTable]}";
            }
            $sTmpQuery .= ')';
            $aTableQueries[$sTermTable][] = $sTmpQuery;

            if (false == TdbShopSearchIndexer::searchWithAND()) {
                //OR `{$sSoundTable}`.`substring` = '".MySqlLegacySupport::getInstance()->real_escape_string($sSoundEX)."')
                $sSoundEX = TdbShopSearchIndexer::GetSoundexForWord($sTerm);
                if ('0000' != $sSoundEX) {
                    $sSoundTable = TdbShopSearchIndexer::GetIndexTableNameForIndexLength(mb_strlen($sSoundEX));

                    $sTmpQuerySoundex = '';
                    $sTmpQuerySoundex .= " (`{$sSoundTable}`.`substring` = '".MySqlLegacySupport::getInstance()->real_escape_string($sSoundEX)."' AND (`{$sSoundTable}`.`cms_language_id` = '{$sLanguageId}' OR `{$sSoundTable}`.`cms_language_id` = '')";
                    if (is_array($aFieldRestrictions)) {
                        $sTmpQuerySoundex .= " AND `{$sSoundTable}`.`shop_search_field_weight_id` IN {$aFieldRestrictionQueries[$sSoundTable]}";
                    }
                    $sTmpQuerySoundex .= ')';
                    $aTableQueries[$sSoundTable][] = $sTmpQuerySoundex;
                }
            }
        }

        return $aTableQueries;
    }
protected static function GetWordListQuery ( $ aTerms , $ sLanguageId , $ aFieldRestrictions = null , $ sTypeOfFieldRestriction = 'OR' ) { $ aTableQueries = array ( ) ; // get affected tables $ aTableList = array ( ) ; foreach ( $ aTerms as $ sTerm ) { $ sTableName = TdbShopSearchIndexer :: GetIndexTableNameForIndexLength ( mb_strlen ( $ sTerm ) ) ; if ( ! in_array ( $ sTableName , $ aTableList ) ) { $ aTableList [ ] = $ sTableName ; } if ( false == TdbShopSearchIndexer :: searchWithAND ( ) ) { $ sSoundEX = TdbShopSearchIndexer :: GetSoundexForWord ( $ sTerm ) ; $ sSoundTable = TdbShopSearchIndexer :: GetIndexTableNameForIndexLength ( mb_strlen ( $ sSoundEX ) ) ; if ( ! in_array ( $ sSoundTable , $ aTableList ) ) { $ aTableList [ ] = $ sSoundTable ; } } } $ aFieldRestrictionQueries = array ( ) ; if ( ! is_null ( $ aFieldRestrictions ) ) { reset ( $ aTableList ) ; foreach ( $ aTableList as $ sTableName ) { $ aFieldRestrictionQueries [ $ sTableName ] = " ('" . implode ( "','" , $ aFieldRestrictions ) . "')" ; } } // build query $ aTableQueries = array ( ) ; reset ( $ aTerms ) ; $ sLanguageId = MySqlLegacySupport :: getInstance ( ) -> real_escape_string ( $ sLanguageId ) ; foreach ( $ aTerms as $ sTerm ) { $ sTermTable = TdbShopSearchIndexer :: GetIndexTableNameForIndexLength ( mb_strlen ( $ sTerm ) ) ; if ( ! array_key_exists ( $ sTermTable , $ aTableQueries ) ) { $ aTableQueries [ $ sTermTable ] = array ( ) ; } $ sTmpQuery = '' ; $ sTmpQuery .= " (`{$sTermTable}`.`substring` = '" . MySqlLegacySupport :: getInstance ( ) -> real_escape_string ( $ sTerm ) . "' AND (`{$sTermTable}`.`cms_language_id` = '{$sLanguageId}' OR `{$sTermTable}`.`cms_language_id` = '')" ; if ( is_array ( $ aFieldRestrictions ) ) { $ sTmpQuery .= " AND `{$sTermTable}`.`shop_search_field_weight_id` IN {$aFieldRestrictionQueries[$sTermTable]}" ; } $ sTmpQuery .= ')' ; $ aTableQueries [ $ sTermTable ] [ ] = $ sTmpQuery ; if ( false == TdbShopSearchIndexer :: searchWithAND ( ) ) { //OR `{$sSoundTable}`.`substring` = '".MySqlLegacySupport::getInstance()->real_escape_string($sSoundEX)."') $ sSoundEX = TdbShopSearchIndexer :: GetSoundexForWord ( $ sTerm ) ; if ( '0000' != $ sSoundEX ) { $ sSoundTable = TdbShopSearchIndexer :: GetIndexTableNameForIndexLength ( mb_strlen ( $ sSoundEX ) ) ; $ sTmpQuerySoundex = '' ; $ sTmpQuerySoundex .= " (`{$sSoundTable}`.`substring` = '" . MySqlLegacySupport :: getInstance ( ) -> real_escape_string ( $ sSoundEX ) . "' AND (`{$sSoundTable}`.`cms_language_id` = '{$sLanguageId}' OR `{$sSoundTable}`.`cms_language_id` = '')" ; if ( is_array ( $ aFieldRestrictions ) ) { $ sTmpQuerySoundex .= " AND `{$sSoundTable}`.`shop_search_field_weight_id` IN {$aFieldRestrictionQueries[$sSoundTable]}" ; } $ sTmpQuerySoundex .= ')' ; $ aTableQueries [ $ sSoundTable ] [ ] = $ sTmpQuerySoundex ; } } } return $ aTableQueries ; }
return subquery that searches for the terms in aTerms in all fields defined by aFieldRestriction
if no restrctions are passed, we search in all.

@param array  $aTerms
@param string $sLanguage               - search in which language?
@param array  $aFieldRestrictions
@param string $sTypeOfFieldRestriction - if the field restrictions are ORed or ANDed

@return array - returns an array with one parameter holding the query, the other a list of relevant table names
return subquery that searches for the terms in aTerms in all fields defined by aFieldRestriction if no restrctions are passed we search in all .