public function searchTitle($phrase = "", $nTopCount = 5, $arParams = array(), $bNotFilter = false, $order = "") { $DB = CDatabase::GetModuleConnection('search'); $bOrderByRank = $order == "rank"; $sqlHaving = array(); $sqlWords = array(); if (!empty($this->_arPhrase)) { $last = true; foreach (array_reverse($this->_arPhrase, true) as $word => $pos) { if ($last && !preg_match("/[\\n\\r \\t]\$/", $phrase)) { $last = false; if (strlen($word) >= $this->minLength) { $s = $sqlWords[] = "ct.WORD like '" . $DB->ForSQL($word) . "%'"; } else { $s = ""; } } else { $s = $sqlWords[] = "ct.WORD = '" . $DB->ForSQL($word) . "'"; } if ($s) { $sqlHaving[] = "(sum(" . $s . ") > 0)"; } } } if (!empty($sqlWords)) { $bIncSites = false; $strSqlWhere = CSearch::__PrepareFilter($arParams, $bIncSites); if ($bNotFilter) { if (!empty($strSqlWhere)) { $strSqlWhere = "NOT (" . $strSqlWhere . ")"; } else { $strSqlWhere = "1=0"; } } $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tsc.ID\n\t\t\t\t\t,sc.MODULE_ID\n\t\t\t\t\t,sc.ITEM_ID\n\t\t\t\t\t,sc.TITLE\n\t\t\t\t\t,sc.PARAM1\n\t\t\t\t\t,sc.PARAM2\n\t\t\t\t\t,sc.DATE_CHANGE\n\t\t\t\t\t,L.DIR\n\t\t\t\t\t,L.SERVER_NAME\n\t\t\t\t\t,sc.URL as URL\n\t\t\t\t\t,scsite.URL as SITE_URL\n\t\t\t\t\t,scsite.SITE_ID\n\t\t\t\t\t,if(locate('" . $DB->ForSQL(ToUpper($phrase)) . "', upper(sc.TITLE)) > 0, 1, 0) RANK1\n\t\t\t\t\t,count(1) RANK2\n\t\t\t\t\t,min(ct.POS) RANK3\n\t\t\t\tFROM\n\t\t\t\t\tb_search_content_title ct\n\t\t\t\t\tINNER JOIN b_lang L ON ct.SITE_ID = L.LID\n\t\t\t\t\tinner join b_search_content sc on sc.ID = ct.SEARCH_CONTENT_ID\n\t\t\t\t\tINNER JOIN b_search_content_site scsite ON sc.ID = scsite.SEARCH_CONTENT_ID and ct.SITE_ID = scsite.SITE_ID\n\t\t\t\tWHERE\n\t\t\t\t\t" . CSearch::CheckPermissions("sc.ID") . "\n\t\t\t\t\tAND ct.SITE_ID = '" . SITE_ID . "'\n\t\t\t\t\tAND (" . implode(" OR ", $sqlWords) . ")\n\t\t\t\t\t" . (!empty($strSqlWhere) ? "AND " . $strSqlWhere : "") . "\n\t\t\t\tGROUP BY\n\t\t\t\t\tID, MODULE_ID, ITEM_ID, TITLE, PARAM1, PARAM2, DATE_CHANGE, DIR, SERVER_NAME, URL, SITE_URL, SITE_ID\n\t\t\t\t" . (count($sqlHaving) > 1 ? "HAVING " . implode(" AND ", $sqlHaving) : "") . "\n\t\t\t\tORDER BY " . ($bOrderByRank ? "RANK1 DESC, RANK2 DESC, RANK3 ASC, TITLE" : "DATE_CHANGE DESC, RANK1 DESC, RANK2 DESC, RANK3 ASC, TITLE") . "\n\t\t\t\tLIMIT 0, " . ($nTopCount + 1) . "\n\t\t\t"; $r = $DB->Query($strSql); parent::CDBResult($r); return true; } else { return false; } }
function tagsMakeSQL($query, $strSqlWhere, $strSort, $bIncSites, $bStem, $limit = 100) { global $USER; $DB = CDatabase::GetModuleConnection('search'); $limit = intVal($limit); if ($bStem && count($this->Query->m_stemmed_words) > 1) { //We have to make some magic in case quotes was used in query //We have to move (sc.searchable_content LIKE '%".ToUpper($word)."%') from $query to $strSqlWhere $arMatches = array(); while (preg_match("/(AND\\s+\\([sct]+.searchable_content LIKE \\'\\%.+?\\%\\'\\))/", $query, $arMatches)) { $strSqlWhere .= $arMatches[0]; $query = str_replace($arMatches[0], "", $query); $arMatches = array(); } } if ($bStem) { if (BX_SEARCH_VERSION > 1) { $strStemList = implode(", ", $this->Query->m_stemmed_words_id); } else { $strStemList = "'" . implode("' ,'", $this->Query->m_stemmed_words) . "'"; } } if ($bIncSites && $bStem) { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tstags.NAME\n\t\t\t\t\t,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT\n\t\t\t\t\t,MAX(sc.DATE_CHANGE) DC_TMP\n\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)") . " as FULL_DATE_CHANGE\n\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT") . " as DATE_CHANGE\n\t\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 && strpos($query, "searchable_content") !== false ? BX_SEARCH_VERSION > 1 ? ",sct.SEARCHABLE_CONTENT" : ",sc.SEARCHABLE_CONTENT" : "") . "\n\t\t\t\tFROM b_search_tags stags\n\t\t\t\t\tINNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID)\n\t\t\t\t\t" . ($this->Query->bText ? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID" : "") . "\n\t\t\t\t\tINNER JOIN b_search_content_site scsite ON sc.ID=scsite.SEARCH_CONTENT_ID\n\t\t\t\t\tINNER JOIN b_search_content_stem st ON sc.id = st.search_content_id\n\t\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 ? "INNER JOIN b_search_content_freq sf ON\n\t\t\t\t\t\t\tst.language_id = sf.language_id\n\t\t\t\t\t\t\tand st.stem=sf.stem\n\t\t\t\t\t\t\t" . (strlen($this->tf_hwm_site_id) > 0 ? "and sf.SITE_ID = '" . $DB->ForSQL($this->tf_hwm_site_id, 2) . "'" : "and sf.SITE_ID IS NULL") : "") . "\n\t\t\t\tWHERE\n\t\t\t\t\t" . CSearch::CheckPermissions("sc.ID") . "\n\t\t\t\t\tAND st.STEM in (" . $strStemList . ")\n\t\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 ? "AND sf.STEM in (" . $strStemList . ")" : "") . "\n\t\t\t\t\tAND st.language_id='" . $this->Query->m_lang . "'\n\t\t\t\t\tAND stags.SITE_ID = scsite.SITE_ID\n\t\t\t\t\t" . $strSqlWhere . "\n\t\t\t\tGROUP BY\n\t\t\t\t\tstags.NAME\n\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 ? "\n\t\t\t\tHAVING\n\t\t\t\t\t(" . $query . ") " : "") . "\n\t\t\t\t" . $strSort . "\n\t\t\t"; } elseif ($bIncSites && !$bStem) { //Copy first exists into inner join in hopeless try to defeat MySQL optimizer $strSqlJoin2 = ""; $match = array(); if ($strSqlWhere && preg_match('#\\s*EXISTS (\\(SELECT \\* FROM b_search_content_param WHERE SEARCH_CONTENT_ID = sc\\.ID AND PARAM_NAME = \'[^\']+\' AND PARAM_VALUE(\\s*= \'[^\']+\'|\\s+in \\(\'[^\']+\'\\))\\))#', $strSqlWhere, $match)) { $subTable = str_replace("SEARCH_CONTENT_ID = sc.ID AND", "", $match[1]); $strSqlJoin2 = "INNER JOIN " . $subTable . " p1 ON p1.SEARCH_CONTENT_ID = sc.ID"; } if ($query == "1=1") { $strSql = "\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tstags2.NAME\n\t\t\t\t\t\t,COUNT(DISTINCT stags2.SEARCH_CONTENT_ID) as CNT\n\t\t\t\t\t\t,MAX(sc.DATE_CHANGE) DC_TMP\n\t\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)") . " as FULL_DATE_CHANGE\n\t\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT") . " as DATE_CHANGE\n\t\t\t\t\tFROM b_search_tags stags2\n\t\t\t\t\t\tINNER JOIN b_search_content sc ON (stags2.SEARCH_CONTENT_ID=sc.ID)\n\t\t\t\t\t\t" . ($this->Query->bText ? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID" : "") . "\n\t\t\t\t\t\tINNER JOIN b_search_content_site scsite ON (sc.ID=scsite.SEARCH_CONTENT_ID AND stags2.SITE_ID=scsite.SITE_ID)\n\t\t\t\t\t\t" . $strSqlJoin2 . "\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t" . CSearch::CheckPermissions("sc.ID") . "\n\t\t\t\t\t\tAND " . ($this->Query->bTagsSearch ? is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words) ? "stags.name in ('" . implode("', '", $this->Query->m_tags_words) . "')" : "(1=1)" : "(" . $query . ")") . " " . $strSqlWhere . "\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\tstags2.NAME\n\t\t\t\t\t" . $strSort . "\n\t\t\t\t"; } else { $strSql = "\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tstags2.NAME\n\t\t\t\t\t\t,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT\n\t\t\t\t\t\t,MAX(sc.DATE_CHANGE) DC_TMP\n\t\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)") . " as FULL_DATE_CHANGE\n\t\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT") . " as DATE_CHANGE\n\t\t\t\t\tFROM b_search_tags stags2\n\t\t\t\t\t\tINNER JOIN b_search_tags stags ON (stags.SEARCH_CONTENT_ID=stags2.SEARCH_CONTENT_ID and stags.SITE_ID=stags2.SITE_ID)\n\t\t\t\t\t\tINNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID)\n\t\t\t\t\t\t" . ($this->Query->bText ? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID" : "") . "\n\t\t\t\t\t\tINNER JOIN b_search_content_site scsite ON (sc.ID=scsite.SEARCH_CONTENT_ID AND stags.SITE_ID=scsite.SITE_ID)\n\t\t\t\t\t\t" . $strSqlJoin2 . "\n\t\t\t\t\tWHERE\n\t\t\t\t\t\t" . CSearch::CheckPermissions("sc.ID") . "\n\t\t\t\t\t\tAND " . ($this->Query->bTagsSearch ? is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words) ? "stags.name in ('" . implode("', '", $this->Query->m_tags_words) . "')" : "(1=1)" : "(" . $query . ")") . " " . $strSqlWhere . "\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\tstags2.NAME\n\t\t\t\t\t\t" . ($this->Query->bTagsSearch ? "\n\t\t\t\t\tHAVING\n\t\t\t\t\t\t(" . $query . ")" : "") . "\n\t\t\t\t\t" . $strSort . "\n\t\t\t\t"; } } elseif (!$bIncSites && $bStem) { $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tstags.NAME\n\t\t\t\t\t,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT\n\t\t\t\t\t,MAX(sc.DATE_CHANGE) DC_TMP\n\t\t\t\t\t, " . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)") . " as FULL_DATE_CHANGE\n\t\t\t\t\t, " . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT") . " as DATE_CHANGE\n\t\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 && strpos($query, "searchable_content") !== false ? BX_SEARCH_VERSION > 1 ? ",sct.SEARCHABLE_CONTENT" : ",sc.SEARCHABLE_CONTENT" : "") . "\n\t\t\t\tFROM b_search_tags stags\n\t\t\t\t\tINNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID)\n\t\t\t\t\t" . ($this->Query->bText ? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID" : "") . "\n\t\t\t\t\tINNER JOIN b_search_content_stem st ON sc.id = st.search_content_id\n\t\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 ? "INNER JOIN b_search_content_freq sf ON\n\t\t\t\t\t\t\tst.language_id = sf.language_id\n\t\t\t\t\t\t\tand st.stem=sf.stem\n\t\t\t\t\t\t\t" . (strlen($this->tf_hwm_site_id) > 0 ? "and sf.SITE_ID = '" . $DB->ForSQL($this->tf_hwm_site_id, 2) . "'" : "and sf.SITE_ID IS NULL") : "") . "\n\t\t\t\tWHERE\n\t\t\t\t\t" . CSearch::CheckPermissions("sc.ID") . "\n\t\t\t\t\tAND st.STEM in (" . $strStemList . ")\n\t\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 ? "AND sf.STEM in (" . $strStemList . ")" : "") . "\n\t\t\t\t\tAND st.language_id='" . $this->Query->m_lang . "'\n\t\t\t\t\t" . $strSqlWhere . "\n\t\t\t\tGROUP BY\n\t\t\t\t\tstags.NAME\n\t\t\t\t" . (count($this->Query->m_stemmed_words) > 1 ? "\n\t\t\t\t\t,sc.ID\n\t\t\t\tHAVING\n\t\t\t\t\t(" . $query . ") " : "") . "\n\t\t\t\t" . $strSort . "\n\t\t\t"; } else { //if(!$bIncSites && !$bStem) $strSql = "\n\t\t\t\tSELECT\n\t\t\t\t\tstags2.NAME\n\t\t\t\t\t,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT\n\t\t\t\t\t,MAX(sc.DATE_CHANGE) DC_TMP\n\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)") . " as FULL_DATE_CHANGE\n\t\t\t\t\t," . $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT") . " as DATE_CHANGE\n\t\t\t\tFROM b_search_tags stags2\n\t\t\t\t\tINNER JOIN b_search_tags stags ON (stags.SEARCH_CONTENT_ID=stags2.SEARCH_CONTENT_ID and stags.SITE_ID=stags2.SITE_ID)\n\t\t\t\t\tINNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID)\n\t\t\t\t\t" . ($this->Query->bText ? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID" : "") . "\n\t\t\t\tWHERE\n\t\t\t\t\t" . CSearch::CheckPermissions("sc.ID") . "\n\t\t\t\t\tAND " . ($this->Query->bTagsSearch ? is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words) ? "stags.name in ('" . implode("', '", $this->Query->m_tags_words) . "')" : "(1=1)" : "(" . $query . ")") . " " . $strSqlWhere . "\n\t\t\t\tGROUP BY\n\t\t\t\t\tstags2.NAME\n\t\t\t\t\t" . ($this->Query->bTagsSearch ? "\n\t\t\t\tHAVING\n\t\t\t\t\t(" . $query . ")" : "") . "\n\t\t\t\t" . $strSort . "\n\t\t\t"; } if ($limit < 1) { $limit = 150; } return $strSql . "LIMIT " . $limit; }
function tagsMakeSQL($query, $strSqlWhere, $strSort, $bIncSites, $bStem, $limit = 100) { global $USER; $DB = CDatabase::GetModuleConnection('search'); $limit = intVal($limit); if($bStem && count($this->Query->m_stemmed_words)>1) {//We have to make some magic in case quotes was used in query //We have to move (sc.searchable_content LIKE '%".ToUpper($word)."%') from $query to $strSqlWhere while(preg_match("/(AND\s+\([sct]+.searchable_content LIKE \'\%.+?\%\'\))/", $query, $arMatches)) { $strSqlWhere .= $arMatches[0]; $query = str_replace($arMatches[0], "", $query); } } if($bStem) { if(BX_SEARCH_VERSION > 1) $strStemList = implode(", ", $this->Query->m_stemmed_words_id); else $strStemList = "'".implode("' ,'", $this->Query->m_stemmed_words)."'"; } if($bIncSites && $bStem) $strSql = " SELECT stags.NAME ,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT ,MAX(sc.DATE_CHANGE) DC_TMP ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)")." as FULL_DATE_CHANGE ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT")." as DATE_CHANGE ".(count($this->Query->m_stemmed_words)>1 && strpos($query, "searchable_content")!==false ?(BX_SEARCH_VERSION > 1? ",sct.SEARCHABLE_CONTENT": ",sc.SEARCHABLE_CONTENT") : "" )." FROM b_search_tags stags INNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID) ".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")." INNER JOIN b_search_content_site scsite ON sc.ID=scsite.SEARCH_CONTENT_ID INNER JOIN b_search_content_stem st ON sc.id = st.search_content_id ".(count($this->Query->m_stemmed_words)>1? "INNER JOIN b_search_content_freq sf ON st.language_id = sf.language_id and st.stem=sf.stem ".(strlen($this->tf_hwm_site_id) > 0? "and sf.SITE_ID = '".$DB->ForSQL($this->tf_hwm_site_id, 2)."'": "and sf.SITE_ID IS NULL" ): "" )." WHERE ".CSearch::CheckPermissions("sc.ID")." AND st.STEM in (".$strStemList.") ".(count($this->Query->m_stemmed_words)>1? "AND sf.STEM in (".$strStemList.")": "")." AND st.language_id='".$this->Query->m_lang."' AND stags.SITE_ID = scsite.SITE_ID ".$strSqlWhere." GROUP BY stags.NAME ".((count($this->Query->m_stemmed_words)>1)?" HAVING (".$query.") ": "")." ".$strSort." "; elseif($bIncSites && !$bStem) { if($query == "1=1") { $strSql = " SELECT stags2.NAME ,COUNT(DISTINCT stags2.SEARCH_CONTENT_ID) as CNT ,MAX(sc.DATE_CHANGE) DC_TMP ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)")." as FULL_DATE_CHANGE ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT")." as DATE_CHANGE FROM b_search_tags stags2 INNER JOIN b_search_content sc ON (stags2.SEARCH_CONTENT_ID=sc.ID) ".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")." INNER JOIN b_search_content_site scsite ON (sc.ID=scsite.SEARCH_CONTENT_ID AND stags2.SITE_ID=scsite.SITE_ID) WHERE ".CSearch::CheckPermissions("sc.ID")." AND ".($this->Query->bTagsSearch? ( //Index range scan optimization (make it for other queries ???) is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words)? "stags.name in ('".implode("', '", $this->Query->m_tags_words)."')": "(1=1)" ) : "(".$query.")")." ".$strSqlWhere." GROUP BY stags2.NAME ".$strSort." "; } else { $strSql = " SELECT stags2.NAME ,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT ,MAX(sc.DATE_CHANGE) DC_TMP ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)")." as FULL_DATE_CHANGE ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT")." as DATE_CHANGE FROM b_search_tags stags2 INNER JOIN b_search_tags stags ON (stags.SEARCH_CONTENT_ID=stags2.SEARCH_CONTENT_ID and stags.SITE_ID=stags2.SITE_ID) INNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID) ".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")." INNER JOIN b_search_content_site scsite ON (sc.ID=scsite.SEARCH_CONTENT_ID AND stags.SITE_ID=scsite.SITE_ID) WHERE ".CSearch::CheckPermissions("sc.ID")." AND ".($this->Query->bTagsSearch? ( //Index range scan optimization (make it for other queries ???) is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words)? "stags.name in ('".implode("', '", $this->Query->m_tags_words)."')": "(1=1)" ) : "(".$query.")")." ".$strSqlWhere." GROUP BY stags2.NAME ".($this->Query->bTagsSearch? " HAVING (".$query.")": "")." ".$strSort." "; } } elseif(!$bIncSites && $bStem) $strSql = " SELECT stags.NAME ,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT ,MAX(sc.DATE_CHANGE) DC_TMP , ".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)")." as FULL_DATE_CHANGE , ".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT")." as DATE_CHANGE ".(count($this->Query->m_stemmed_words)>1 && strpos($query, "searchable_content")!==false ?(BX_SEARCH_VERSION > 1? ",sct.SEARCHABLE_CONTENT": ",sc.SEARCHABLE_CONTENT") : "" )." FROM b_search_tags stags INNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID) ".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")." INNER JOIN b_search_content_stem st ON sc.id = st.search_content_id ".(count($this->Query->m_stemmed_words)>1? "INNER JOIN b_search_content_freq sf ON st.language_id = sf.language_id and st.stem=sf.stem ".(strlen($this->tf_hwm_site_id) > 0? "and sf.SITE_ID = '".$DB->ForSQL($this->tf_hwm_site_id, 2)."'": "and sf.SITE_ID IS NULL" ): "" )." WHERE ".CSearch::CheckPermissions("sc.ID")." AND st.STEM in (".$strStemList.") ".(count($this->Query->m_stemmed_words)>1? "AND sf.STEM in (".$strStemList.")": "")." AND st.language_id='".$this->Query->m_lang."' ".$strSqlWhere." GROUP BY stags.NAME ".(count($this->Query->m_stemmed_words)>1?" ,sc.ID HAVING (".$query.") ": "")." ".$strSort." "; else //if(!$bIncSites && !$bStem) $strSql = " SELECT stags2.NAME ,COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT ,MAX(sc.DATE_CHANGE) DC_TMP ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)")." as FULL_DATE_CHANGE ,".$DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT")." as DATE_CHANGE FROM b_search_tags stags2 INNER JOIN b_search_tags stags ON (stags.SEARCH_CONTENT_ID=stags2.SEARCH_CONTENT_ID and stags.SITE_ID=stags2.SITE_ID) INNER JOIN b_search_content sc ON (stags.SEARCH_CONTENT_ID=sc.ID) ".($this->Query->bText? "INNER JOIN b_search_content_text sct ON sct.SEARCH_CONTENT_ID = sc.ID": "")." WHERE ".CSearch::CheckPermissions("sc.ID")." AND ".($this->Query->bTagsSearch? ( //Index range scan optimization (make it for other queries ???) is_array($this->Query->m_tags_words) && count($this->Query->m_tags_words)? "stags.name in ('".implode("', '", $this->Query->m_tags_words)."')": "(1=1)" ) : "(".$query.")")." ".$strSqlWhere." GROUP BY stags2.NAME ".($this->Query->bTagsSearch? " HAVING (".$query.")": "")." ".$strSort." "; if($limit < 1) $limit = 150; return $strSql."LIMIT ".$limit; }
function GetFilterMD5() { $perm = CSearch::CheckPermissions("sc.ID"); $sql = preg_replace("/(DATE_FROM|DATE_TO|DATE_CHANGE)(\\s+IS\\s+NOT\\s+NULL|\\s+IS\\s+NULL|\\s*[<>!=]+\\s*'.*?')/im", "", $this->strSqlWhere); return md5($perm.$sql.$this->strTags); }
public function Search($phrase = "", $nTopCount = 5, $arParams = array(), $bNotFilter = false, $order = "") { $DB = CDatabase::GetModuleConnection('search'); $this->_arPhrase = stemming_split($phrase, LANGUAGE_ID); $bOrderByRank = ($order == "rank"); if(!empty($this->_arPhrase)) { $nTopCount = intval($nTopCount); if($nTopCount <= 0) $nTopCount = 5; $sqlHaving = array(); $sqlWords = array(); foreach(array_reverse($this->_arPhrase, true) as $word => $pos) { if(empty($sqlWords) && !preg_match("/[\\n\\r \\t]$/", $phrase)) $s = $sqlWords[] = "ct.WORD like '".$DB->ForSQL($word)."%'"; else $s = $sqlWords[] = "ct.WORD = '".$DB->ForSQL($word)."'"; $sqlHaving[] = "(sum(".$s.") > 0)"; } $bIncSites = false; $strSqlWhere = CSearch::__PrepareFilter($arParams, $bIncSites); if($bNotFilter) { if(!empty($strSqlWhere)) $strSqlWhere = "NOT (".$strSqlWhere.")"; else $strSqlWhere = "1=0"; } $strSql = " SELECT sc.ID ,sc.MODULE_ID ,sc.ITEM_ID ,sc.TITLE ,sc.PARAM1 ,sc.PARAM2 ,sc.DATE_CHANGE ,L.DIR ,L.SERVER_NAME ,sc.URL as URL ,scsite.URL as SITE_URL ,scsite.SITE_ID ,if(locate('".$DB->ForSQL(ToUpper($phrase))."', upper(sc.TITLE)) > 0, 1, 0) RANK1 ,count(1) RANK2 ,min(ct.POS) RANK3 FROM b_search_content_title ct INNER JOIN b_lang L ON ct.SITE_ID = L.LID inner join b_search_content sc on sc.ID = ct.SEARCH_CONTENT_ID INNER JOIN b_search_content_site scsite ON sc.ID = scsite.SEARCH_CONTENT_ID and ct.SITE_ID = scsite.SITE_ID WHERE ".CSearch::CheckPermissions("sc.ID")." AND ct.SITE_ID = '".SITE_ID."' AND (".implode(" OR ", $sqlWords).") ".(!empty($strSqlWhere)? "AND ".$strSqlWhere: "")." GROUP BY ID, MODULE_ID, ITEM_ID, TITLE, PARAM1, PARAM2, DATE_CHANGE, DIR, SERVER_NAME, URL, SITE_URL, SITE_ID ".(count($sqlHaving) > 1? "HAVING ".implode(" AND ", $sqlHaving): "")." ORDER BY ".( $bOrderByRank? "RANK1 DESC, RANK2 DESC, RANK3 ASC, TITLE": "DATE_CHANGE DESC, RANK1 DESC, RANK2 DESC, RANK3 ASC, TITLE" )." LIMIT 0, ".($nTopCount+1)." "; $r = $DB->Query($strSql); parent::CDBResult($r); return true; } else { return false; } }
/** * <p>Получение списка тегов элементов поискового индекса. Метод динамичный.</p> <p>Данный метод использует технологию управляемого кеширования в случае соответствующей настройки <a href="http://dev.1c-bitrix.ru/api_help/search/constants.php">констант модуля поиска</a>: CACHED_b_search_tags и CACHED_b_search_tags_len.</p> * * * @param array $arSelect = array() Массив, содержащий поля для выборки. <br><br> Название поля может * принимать значение: <ul> <li> <b>NAME</b> - тег;</li> <li> <b>CNT</b> - частота тега, * количество элементов поискового индекса содержащих этот тег;</li> * <li> <b>DATE_CHANGE</b> - максимальная дата модификации (в полном формате) * элементов поискового индекса содержащих этот тег;</li> </ul> Не * обязательный параметр. По умолчанию равен: <pre class="syntax"> array(<br> * "NAME",<br> "CNT",<br> )<br></pre> * * @param array $arFilter = array() Массив, содержащий фильтр в виде наборов "название * поля"=>"значение фильтра". <br><br> Название поля может принимать * значение: <ul> <li> <b>SITE_ID</b> - массив идентификаторов сайтов;</li> <li> * <b>TAG</b> - начало тега, будут возвращены все теги начинающиеся с * этого значения;</li> <li> <b>MODULE_ID</b> - идентификатор модуля;</li> <li> * <b>PARAM1</b> - первый параметр элемента;</li> <li> <b>PARAM2</b> - второй параметр * элемента;</li> </ul> Пример: <pre class="syntax"> array(<br> "SITE_ID"=>array("s1"),<br> * "TAG"=>"We",<br> "MODULE_ID"=>"iblock",<br> )<br></pre> * * @param array $arOrder = array() Массив, содержащий признак сортировки в виде наборов "название * поля"=>"направление". <br><br> Название поля может принимать * значение: <ul> <li> <b>NAME</b> - тег;</li> <li> <b>CNT</b> - частота тега, количество * элементов поискового индекса содержащих этот тег;</li> <li> * <b>DATE_CHANGE</b> - максимальная дата модификации (в полном формате) * элементов поискового индекса содержащих этот тег;</li> </ul> * Направление сортировки может принимать значение: <ul> <li> <b>ASC</b> - по * возрастанию;</li> <li> <b>DESC</b> - по убыванию.</li> </ul> Не обязательный * параметр. По умолчанию равен: <pre class="syntax"> array(<br> "NAME"=>"ASC",<br> )<br></pre> * * @param int $limit = 100 Ограничение количества тегов в результатах. * * @return CDBResult <p>Возвращается результат запроса типа <a * href="http://dev.1c-bitrix.ru/api_help/main/reference/cdbresult/index.php">CDBResult</a>. При выборке из * результата методами класса CDBResult становятся доступны поля * перечисленные в параметре arSelect.</p> * * <h4>Example</h4> * <pre> * <?<br>//подключение модуля поиска<br>if(CModule::IncludeModule('search'))<br>{<br> $rsTags = CSearchTags::GetList(<br> array(),<br> array(<br> "MODULE_ID" => "iblock",<br> ),<br> array(<br> "CNT" => "DESC",<br> ),<br> 10<br> );<br> while($arTag = $rsTags->Fetch())<br> print_r($arTag);<br>}<br>?><br> * </pre> * * * <h4>See Also</h4> * <ul> <li><a href="http://dev.1c-bitrix.ru/api_help/search/constants.php">Константы модуля * поиска</a></li> </ul> <a name="examples"></a> * * * @static * @link http://dev.1c-bitrix.ru/api_help/search/classes/csearchtags/getlist.php * @author Bitrix */ public static function GetList($arSelect = array(), $arFilter = array(), $arOrder = array(), $limit = 100) { global $USER; $DB = CDatabase::GetModuleConnection('search'); static $arFilterEvents = false; $arQuerySelect = array(); if (!is_array($arSelect)) { $arSelect = array(); } if (count($arSelect) < 1) { $arSelect = array("NAME", "CNT"); } $bJoinSearchContent = false; foreach ($arSelect as $key => $value) { $value = strtoupper($value); switch ($value) { case "NAME": $arQuerySelect["NAME"] = "stags.NAME"; break; case "CNT": $arQuerySelect["CNT"] = "COUNT(DISTINCT stags.SEARCH_CONTENT_ID) as CNT"; break; case "DATE_CHANGE": $arQuerySelect["DC_TMP"] = "MAX(sc.DATE_CHANGE) as DC_TMP"; $arQuerySelect["FULL_DATE_CHANGE"] = $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "FULL") . " as FULL_DATE_CHANGE"; $arQuerySelect["DATE_CHANGE"] = $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT") . " as DATE_CHANGE"; $bJoinSearchContent = true; break; } } $arQueryWhere = array(); if (!is_array($arFilter)) { $arFilter = array("TAG" => $arFilter, "SITE_ID" => array(SITE_ID)); } if (empty($arFilter["SITE_ID"]) && array_key_exists("TAG", $arFilter)) { $arFilter["SITE_ID"] = array(SITE_ID); } if (array_key_exists("SITE_ID", $arFilter) && !is_array($arFilter["SITE_ID"])) { $arFilter["SITE_ID"] = array($arFilter["SITE_ID"]); } $strTag = ""; foreach ($arFilter as $key => $value) { $key = strtoupper($key); switch ($key) { case "SITE_ID": $arSites = array(); foreach ($value as $site_id) { $arSites[$DB->ForSql($site_id, 2)] = true; } $arSites = array_keys($arSites); if (count($arSites) == 1) { $arQueryWhere[] = "stags.SITE_ID = '" . $arSites[0] . "'"; } elseif (count($arSites) > 1) { $arQueryWhere[] = "stags.SITE_ID in ('" . implode("', '", $arSites) . "')"; } break; case "TAG": $arTags = tags_prepare($value, $arFilter["SITE_ID"][0]); if (count($arTags) > 0) { $strTag = array_pop($arTags); $arQueryWhere[] = "UPPER(stags.NAME) LIKE '" . $DB->ForSql(ToUpper($strTag)) . "%'"; } break; case "MODULE_ID": case "PARAM1": case "PARAM2": $arQueryWhere[] = "sc." . $key . " ='" . $DB->ForSql($value) . "'"; $bJoinSearchContent = true; break; case "PARAMS": if (is_array($value)) { foreach ($value as $p_key => $p_val) { if (is_array($p_val)) { foreach ($p_val as $i => $val2) { $p_val[$i] = $DB->ForSQL($val2); } $p_where = " in ('" . implode("', '", $p_val) . "')"; } else { $p_where = " = '" . $DB->ForSQL($p_val) . "'"; } $arQueryWhere[] = "EXISTS (SELECT * FROM b_search_content_param WHERE SEARCH_CONTENT_ID = stags.SEARCH_CONTENT_ID AND PARAM_NAME = '" . $DB->ForSQL($p_key) . "' AND PARAM_VALUE " . $p_where . ")"; } } break; default: if (!is_array($arFilterEvents)) { $arFilterEvents = GetModuleEvents("search", "OnSearchPrepareFilter", true); } //Try to get someone to make the filter sql foreach ($arFilterEvents as $arEvent) { $sql = ExecuteModuleEventEx($arEvent, array("sc.", $key, $value)); if (strlen($sql)) { $arQueryWhere[] = "(" . $sql . ")"; $bJoinSearchContent = true; break; } } } } $arQueryOrder = array(); if (!is_array($arOrder)) { $arOrder = array(); } if (count($arOrder) < 1) { $arOrder = array("NAME" => "ASC"); } foreach ($arOrder as $key => $value) { $key = strtoupper($key); $value = strtoupper($value) == "DESC" ? "DESC" : "ASC"; switch ($key) { case "NAME": case "CNT": $arQueryOrder[$key] = $key . " " . $value; break; case "DATE_CHANGE": $arQueryOrder[$key] = "DC_TMP " . $value; $arQuerySelect["DC_TMP"] = "MAX(sc.DATE_CHANGE) as DC_TMP"; $arQuerySelect["FULL_DATE_CHANGE"] = $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "FULL") . " as FULL_DATE_CHANGE"; $arQuerySelect["DATE_CHANGE"] = $DB->DateToCharFunction("MAX(sc.DATE_CHANGE)", "SHORT") . " as DATE_CHANGE"; $bJoinSearchContent = true; break; } } if (count($arQueryOrder) < 1) { $arQueryOrder = array("NAME" => "NAME ASC"); } $strSql = "\n\t\t\tSELECT /*TOP*/\n\t\t\t\t" . implode("\n,", $arQuerySelect) . "\n\t\t\tFROM b_search_tags stags\n\t\t\t\t" . ($bJoinSearchContent ? "INNER JOIN b_search_content sc ON sc.ID = stags.SEARCH_CONTENT_ID" : "") . "\n\t\t\tWHERE\n\t\t\t\t" . CSearch::CheckPermissions("stags.SEARCH_CONTENT_ID") . "\n\t\t\t\t" . (count($arQueryWhere) > 0 ? "AND " . implode("\nAND ", $arQueryWhere) : "") . "\n\t\t\tGROUP BY stags.NAME\n\t\t\tORDER BY " . implode(", ", $arQueryOrder) . "\n\t\t"; if ($limit !== false) { $limit = intVal($limit); if ($limit <= 0 || $limit > COption::GetOptionInt("search", "max_result_size")) { $limit = COption::GetOptionInt("search", "max_result_size"); } if ($limit < 1) { $limit = 100; } $strSql = CSearch::FormatLimit($strSql, $limit); } else { $strSql = str_replace("/*TOP*/", "", $strSql); } if (CACHED_b_search_tags !== false && $limit !== false && strlen($strTag) <= CACHED_b_search_tags_len) { global $CACHE_MANAGER; $path = "b_search_tags"; while (strlen($strTag) > 0) { $path .= "/_" . ord(substr($strTag, 0, 1)); $strTag = substr($strTag, 1); } $cache_id = "search_tags:" . md5($strSql); if ($CACHE_MANAGER->Read(CACHED_b_search_tags, $cache_id, $path)) { $arTags = $CACHE_MANAGER->Get($cache_id); } else { $arTags = array(); $res = $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); while ($ar = $res->Fetch()) { $arTags[] = $ar; } $CACHE_MANAGER->Set($cache_id, $arTags); } $res = new CDBResult(); $res->InitFromArray($arTags); return $res; } else { return $DB->Query($strSql, false, "File: " . __FILE__ . "<br>Line: " . __LINE__); } }