| 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;
}
