function tagsMakeSQL($query, $strSqlWhere, $strSort, $bIncSites, $bStem, $limit = 100)
{
$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("/(ANDs+([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 = "
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 && mb_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
".($this->tf_hwm_site_id <> ''?
"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)
{
//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))
{
$strSqlJoin2 = "INNER JOIN b_search_content_param scp ON scp.$match[1]";
}
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)
".$strSqlJoin2."
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)
".$strSqlJoin2."
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 && mb_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
".($this->tf_hwm_site_id <> ''?
"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;
}