public static SearchQuery ( $p_searchPhrase, $p_symbol = null ) |
public static function SearchQuery($p_searchPhrase, $p_symbol = null)
{
global $g_ado_db;
$p_searchPhrase = trim($p_searchPhrase);
if (empty($p_searchPhrase)) {
return;
}
$matchAll = false;
$keywords = preg_split('/[\\s,.-]/', $p_searchPhrase);
if (isset($keywords[0]) && strtolower($keywords[0]) == '__match_all') {
$matchAll = true;
array_shift($keywords);
}
$keywords = array_diff($keywords, array("", ""));
$sKeys = array();
foreach ($keywords as $keyword) {
if (strlen($keyword) > 2) {
$sKeys[] = $keyword;
}
}
$keywords = $sKeys;
if (count($keywords) < 1) {
return;
}
// specifically match webcode (first one)
$webcodeMatches = preg_grep("`^\\s*[\\+@]`", $keywords);
if (count($webcodeMatches)) {
$wcode = ltrim(current($webcodeMatches), '@+');
$za = Zend_Registry::get('container')->getService('webcode')->findArticleByWebcode($wcode);
$article_no = $za->getId();
if (is_numeric($article_no)) {
$selectKeywordClauseObj = new SQLSelectClause();
$selectKeywordClauseObj->addColumn('DISTINCT AI1.NrArticle');
$selectKeywordClauseObj->addColumn('AI1.IdLanguage');
$selectKeywordClauseObj->setTable('ArticleIndex AS AI1');
$selectKeywordClauseObj->addConditionalWhere("AI1.NrArticle = '{$article_no}'");
}
} elseif ($matchAll && count($keywords) > 1) {
$selectKeywordClauseObj = new SQLSelectClause();
$selectKeywordClauseObj->addColumn('DISTINCT AI1.NrArticle');
$selectKeywordClauseObj->addColumn('AI1.IdLanguage');
$selectKeywordClauseObj->setTable('ArticleIndex AS AI1');
$selectKeywordClauseObj->addJoin('LEFT JOIN KeywordIndex AS KI1 ON AI1.IdKeyword = KI1.Id');
for ($tableIndex = 2; $tableIndex <= count($keywords); $tableIndex++) {
$selectKeywordClauseObj->addJoin("LEFT JOIN ArticleIndex AS AI{$tableIndex} " . "ON AI1.NrArticle = AI{$tableIndex}.NrArticle " . "AND AI1.IdLanguage = AI{$tableIndex}.IdLanguage");
$selectKeywordClauseObj->addJoin("LEFT JOIN KeywordIndex AS KI{$tableIndex} " . "ON AI{$tableIndex}.IdKeyword = KI{$tableIndex}.Id");
}
$tableIndex = 1;
foreach ($keywords as $keyword) {
$keywordConstraint = 'KI' . $tableIndex . '.Keyword = ' . $g_ado_db->escape($keyword);
$selectKeywordClauseObj->addWhere($keywordConstraint);
$tableIndex++;
}
} else {
$selectKeywordClauseObj = new SQLSelectClause();
$selectKeywordClauseObj->addColumn('DISTINCT AI1.NrArticle');
$selectKeywordClauseObj->addColumn('AI1.IdLanguage');
$selectKeywordClauseObj->setTable('ArticleIndex AS AI1');
$selectKeywordClauseObj->addJoin('LEFT JOIN KeywordIndex AS KI1 ON AI1.IdKeyword = KI1.Id');
foreach ($keywords as $keyword) {
if (strtolower($p_symbol) == 'like') {
$keywordConstraint = 'KI1.Keyword LIKE ' . $g_ado_db->escape($keyword . '%');
} else {
$keywordConstraint = 'KI1.Keyword = ' . $g_ado_db->escape($keyword);
}
$selectKeywordClauseObj->addConditionalWhere($keywordConstraint);
}
}
return $selectKeywordClauseObj->buildQuery();
}
/** * Performs a search against the article content using the given * keywords. Returns the list of articles matching the given criteria. * * @param string $p_searchPhrase * @param string $p_fieldName - may be 'title' or 'author' * @param bool $p_matchAll - true if all keyword have to match * @param array $p_constraints * @param array $p_order * @param int $p_start - return results starting from the given order number * @param int $p_limit - return at most $p_limit rows * @param int $p_count - sets $p_count to the total number of rows in the search * @param bool $p_countOnly - if true returns only the total number of rows * @return array */ public static function SearchByKeyword($p_searchPhrase, $p_matchAll = false, array $p_constraints = array(), array $p_order = array(), $p_start = 0, $p_limit = 0, &$p_count, $p_countOnly = false) { global $g_ado_db; $selectClauseObj = new SQLSelectClause(); // set tables and joins between tables $selectClauseObj->setTable('Articles'); if ($p_matchAll) { $p_searchPhrase = '__match_all ' . $p_searchPhrase; } $mainClauseConstraint = "(Articles.Number, Articles.IdLanguage) IN (" . ArticleIndex::SearchQuery($p_searchPhrase) . ")"; $selectClauseObj->addWhere($mainClauseConstraint); $joinTables = array(); // set other constraints foreach ($p_constraints as $constraint) { $leftOperand = $constraint->getLeftOperand(); $operandAttributes = explode('.', $leftOperand); if (count($operandAttributes) == 2) { $table = trim($operandAttributes[0]); if (strtolower($table) != 'articles') { $joinTables[] = $table; } } $symbol = $constraint->getOperator()->getSymbol('sql'); $rightOperand = "'" . $g_ado_db->escape($constraint->getRightOperand()) . "'"; $selectClauseObj->addWhere("$leftOperand $symbol $rightOperand"); } foreach ($joinTables as $table) { $selectClauseObj->addJoin("LEFT JOIN $table ON Articles.Number = $table.NrArticle"); } // create the count clause object $countClauseObj = clone $selectClauseObj; // set the columns for the select clause $selectClauseObj->addColumn('Articles.Number'); $selectClauseObj->addColumn('Articles.IdLanguage'); // set the order for the select clause $p_order = count($p_order) > 0 ? $p_order : Article::$s_defaultOrder; $order = Article::ProcessListOrder($p_order); foreach ($order as $orderDesc) { $orderField = $orderDesc['field']; $orderDirection = $orderDesc['dir']; $selectClauseObj->addOrderBy($orderField . ' ' . $orderDirection); } // sets the LIMIT start and offset values $selectClauseObj->setLimit($p_start, $p_limit); // set the column for the count clause $countClauseObj->addColumn('COUNT(*)'); $articlesList = array(); if (!$p_countOnly) { $selectQuery = $selectClauseObj->buildQuery(); $articles = $g_ado_db->GetAll($selectQuery); foreach ($articles as $article) { $articlesList[] = new Article($article['IdLanguage'], $article['Number']); } } $countQuery = $countClauseObj->buildQuery(); $p_count = $g_ado_db->GetOne($countQuery); return $articlesList; }