/**
* Returns an articles list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articlesList
* An array of Article objects
*/
public static function GetList(array $p_parameters, $p_order = null,
$p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = (is_null($p_order)) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articlesList = $cacheListObj->fetchFromCache();
if ($articlesList !== false && is_array($articlesList)) {
return $articlesList;
}
}
$matchAllTopics = false;
$hasTopics = array();
$hasNotTopics = array();
$selectClauseObj = new SQLSelectClause();
$otherTables = array();
// sets the name of the table for the this database object
$tmpArticle = new Article();
$articleTable = $tmpArticle->getDbTableName();
$selectClauseObj->setTable($articleTable);
unset($tmpArticle);
$languageId = null;
// parses the given parameters in order to build the WHERE part of
// the SQL SELECT sentence
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param, $otherTables);
$leftOperand = strtolower($comparisonOperation['left']);
if ($leftOperand == 'idlanguage' && $comparisonOperation['symbol'] == '=') {
$languageId = $comparisonOperation['right'];
}
if (array_key_exists($leftOperand, Article::$s_regularParameters)) {
// regular article field, having a direct correspondent in the
// Article table fields
$whereCondition = Article::$s_regularParameters[$leftOperand]
. ' ' . $comparisonOperation['symbol']
. " '" . $g_ado_db->escape($comparisonOperation['right']) . "' ";
if ($leftOperand == 'reads'
&& strstr($comparisonOperation['symbol'], '=') !== false
&& $comparisonOperation['right'] == 0) {
$selectClauseObj->addConditionalWhere($whereCondition);
$isNullCond = Article::$s_regularParameters[$leftOperand]
. ' IS NULL';
$selectClauseObj->addConditionalWhere($isNullCond);
} elseif ($leftOperand == 'type' && $comparisonOperation['symbol'] == '=' ) {
$selectClauseObj->addConditionalWhere($whereCondition);
} else {
$selectClauseObj->addWhere($whereCondition);
}
} elseif ($leftOperand == 'matchalltopics') {
// set the matchAllTopics flag
$matchAllTopics = true;
} elseif ($leftOperand == 'topic') {
// add the topic to the list of match/do not match topics depending
// on the operator
$topic = new Topic($comparisonOperation['right']);
if ($topic->exists()) {
$topicIds = $topic->getSubtopics(true, 0);
$topicIds[] = $comparisonOperation['right'];
if ($comparisonOperation['symbol'] == '=') {
$hasTopics[] = $topicIds;
} else {
$hasNotTopics[] = $topicIds;
}
}
} elseif ($leftOperand == 'author') {
$otherTables['ArticleAuthors'] = array('__JOIN' => ',');
$author = Author::ReadName($comparisonOperation['right']);
$symbol = $comparisonOperation['symbol'];
$valModifier = strtolower($symbol) == 'like' ? '%' : '';
$firstName = $g_ado_db->escape($author['first_name']);
$lastName = $g_ado_db->escape($author['last_name']);
$whereCondition = "ArticleAuthors.fk_author_id IN
(SELECT Authors.id
FROM Authors
WHERE CONCAT(Authors.first_name, ' ', Authors.last_name) $symbol
'$valModifier$firstName $lastName$valModifier')";
$selectClauseObj->addWhere($whereCondition);
$selectClauseObj->addWhere('Articles.Number = ArticleAuthors.fk_article_number');
$selectClauseObj->addWhere('Articles.IdLanguage = ArticleAuthors.fk_language_id');
} elseif ($leftOperand == 'search_phrase') {
$searchQuery = ArticleIndex::SearchQuery($comparisonOperation['right']);
$mainClauseConstraint = "(Articles.Number, Articles.IdLanguage) IN ( $searchQuery )";
$selectClauseObj->addWhere($mainClauseConstraint);
} elseif ($leftOperand == 'location') {
$num = '[-+]?[0-9]+(?:\.[0-9]+)?';
if (preg_match("/($num) ($num), ($num) ($num)/",
trim($comparisonOperation['right']), $matches)) {
$queryLocation = Geo_Map::GetGeoSearchSQLQuery(array(
array(
'latitude' => $matches[1],
'longitude' => $matches[2],
), array(
'latitude' => $matches[3],
'longitude' => $matches[4],
),
));
$selectClauseObj->addWhere("Articles.Number IN ($queryLocation)");
}
} else {
// custom article field; has a correspondence in the X[type]
// table fields
$sqlQuery = self::ProcessCustomField($comparisonOperation, $languageId);
if (!is_null($sqlQuery)) {
$whereCondition = "Articles.Number IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
}
}
if (count($hasTopics) > 0 || count($hasNotTopics) > 0) {
$typeAttributes = ArticleTypeField::FetchFields(null, null, 'topic', false,
false, false, true, $p_skipCache);
}
if (count($hasTopics) > 0) {
if ($matchAllTopics) {
foreach ($hasTopics as $topicId) {
$sqlQuery = Article::BuildTopicSelectClause($topicId, $typeAttributes);
$whereCondition = "Articles.Number IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
} else {
$sqlQuery = Article::BuildTopicSelectClause($hasTopics, $typeAttributes);
$whereCondition = "Articles.Number IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
}
if (count($hasNotTopics) > 0) {
$sqlQuery = Article::BuildTopicSelectClause($hasNotTopics, $typeAttributes);
$whereCondition = "Articles.Number NOT IN (\n$sqlQuery )";
$selectClauseObj->addWhere($whereCondition);
}
// create the count clause object
$countClauseObj = clone $selectClauseObj;
if (!is_array($p_order)) {
$p_order = array();
}
// sets the ORDER BY condition
$p_order = array_merge($p_order, Article::$s_defaultOrder);
$order = Article::ProcessListOrder($p_order, $otherTables, $otherWhereConditions);
foreach ($order as $orderDesc) {
$orderColumn = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
if (count($otherTables) > 0) {
foreach ($otherTables as $table=>$fields) {
$joinType = 'LEFT JOIN';
if (isset($fields['__JOIN'])) {
$joinType = $fields['__JOIN'];
}
if (isset($fields['__TABLE_ALIAS'])) {
$tableAlias = $fields['__TABLE_ALIAS'];
$tableJoin = "\n $joinType $table AS $tableAlias \n";
} else {
$tableAlias = $table;
$tableJoin = "\n $joinType $tableAlias \n";
}
$firstCondition = true;
foreach ($fields as $parent=>$child) {
if ($parent == '__TABLE_ALIAS' || $parent == '__JOIN') {
continue;
}
$condOperator = $firstCondition ? ' ON ' : 'AND ';
if ($parent == '__CONST') {
$constTable = $child['table'];
$constField = $child['field'];
$value = $child['value'];
$negate = isset($child['negate']) ? $child['negate'] : false;
if (is_null($value)) {
$operator = $negate ? 'IS NOT' : 'IS';
$value = 'NULL';
} else {
$operator = $negate ? '!=' : '=';
$value = "'" . $g_ado_db->escape($value) . "'";
}
$tableJoin .= " $condOperator`$constTable`.`$constField` $operator $value\n";
} else {
$tableJoin .= " $condOperator`$articleTable`.`$parent` = `$tableAlias`.`$child`\n";
}
$firstCondition = false;
}
$selectClauseObj->addJoin($tableJoin);
$countClauseObj->addJoin($tableJoin);
}
}
// other where conditions needed for certain order options
foreach ($otherWhereConditions as $whereCondition) {
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// gets the column list to be retrieved for the database table
$selectClauseObj->addColumn('Articles.Number');
$selectClauseObj->addColumn('Articles.IdLanguage');
$countClauseObj->addColumn('COUNT(*)');
// sets the LIMIT start and offset values
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the SQL query
$selectQuery = $selectClauseObj->buildQuery();
$countQuery = $countClauseObj->buildQuery();
// runs the SQL query
$articles = $g_ado_db->GetAll($selectQuery);
if (is_array($articles)) {
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of Article objects
$articlesList = array();
foreach ($articles as $article) {
$articlesList[] = new Article($article['IdLanguage'], $article['Number']);
}
} else {
$articlesList = array();
$p_count = 0;
}
// stores articles list in cache
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articlesList);
}
return $articlesList;
} // fn GetList
/**
* Returns an articles list based on the given parameters.
*
* @param array $p_parameters
* An array of ComparisonOperation objects
* @param string $p_order
* An array of columns and directions to order by
* @param integer $p_start
* The record number to start the list
* @param integer $p_limit
* The offset. How many records from $p_start will be retrieved.
* @param integer $p_count
* The total count of the elements; this count is computed without
* applying the start ($p_start) and limit parameters ($p_limit)
*
* @return array $articlesList
* An array of Article objects
*/
public static function GetList(array $p_parameters, $p_order = null, $p_start = 0, $p_limit = 0, &$p_count, $p_skipCache = false, $returnObjs = true)
{
global $g_ado_db;
if (!$p_skipCache && CampCache::IsEnabled()) {
$paramsArray['parameters'] = serialize($p_parameters);
$paramsArray['order'] = is_null($p_order) ? 'null' : $p_order;
$paramsArray['start'] = $p_start;
$paramsArray['limit'] = $p_limit;
$cacheListObj = new CampCacheList($paramsArray, __METHOD__);
$articlesList = $cacheListObj->fetchFromCache();
if ($articlesList !== false && is_array($articlesList)) {
return $articlesList;
}
}
$matchAllTopics = false;
$hasTopics = array();
$hasNotTopics = array();
$selectClauseObj = new SQLSelectClause();
$otherTables = array();
// sets the name of the table for the this database object
$tmpArticle = new Article();
$articleTable = $tmpArticle->getDbTableName();
$selectClauseObj->setTable($articleTable);
unset($tmpArticle);
$languageId = null;
$em = Zend_Registry::get('container')->getService('em');
$request = Zend_Registry::get('container')->getService('request');
$repository = $em->getRepository('Newscoop\\NewscoopBundle\\Entity\\Topic');
// parses the given parameters in order to build the WHERE part of
// the SQL SELECT sentence
foreach ($p_parameters as $param) {
$comparisonOperation = self::ProcessListParameters($param, $otherTables);
$leftOperand = strtolower($comparisonOperation['left']);
if ($leftOperand == 'idlanguage' && $comparisonOperation['symbol'] == '=') {
$languageId = $comparisonOperation['right'];
}
if (array_key_exists($leftOperand, Article::$s_regularParameters)) {
// regular article field, having a direct correspondent in the
// Article table fields
$whereCondition = Article::$s_regularParameters[$leftOperand] . ' ' . $comparisonOperation['symbol'] . " " . $g_ado_db->escape($comparisonOperation['right']) . " ";
if ($leftOperand == 'reads' && strstr($comparisonOperation['symbol'], '=') !== false && $comparisonOperation['right'] == 0) {
$selectClauseObj->addConditionalWhere($whereCondition);
$isNullCond = Article::$s_regularParameters[$leftOperand] . ' IS NULL';
$selectClauseObj->addConditionalWhere($isNullCond);
} elseif ($leftOperand == 'type' && $comparisonOperation['symbol'] == '=') {
$selectClauseObj->addConditionalWhere($whereCondition);
} elseif ($leftOperand == 'workflow_status' && isset($comparisonOperation['pending'])) {
$selectClauseObj->addConditionalWhere('Articles.NrIssue = 0');
$selectClauseObj->addConditionalWhere('Articles.NrSection = 0');
$selectClauseObj->addWhere($whereCondition);
} else {
$selectClauseObj->addWhere($whereCondition);
}
} elseif ($leftOperand == 'matchalltopics') {
// set the matchAllTopics flag
$matchAllTopics = true;
} elseif ($leftOperand == 'topic') {
// add the topic to the list of match/do not match topics depending
// on the operator
$topic = $repository->getTopicByIdOrName($comparisonOperation['right'], $request->getLocale())->getOneOrNullResult();
if ($topic) {
$topicIds = array();
foreach ($topic->getChildren() as $child) {
$topicIds[] = $child->getId();
}
$topicIds[] = $comparisonOperation['right'];
if ($comparisonOperation['symbol'] == '=') {
$hasTopics[] = $topicIds;
} else {
$hasNotTopics[] = $topicIds;
}
}
} elseif ($leftOperand == 'topic_strict') {
$topic = $repository->getTopicByIdOrName($comparisonOperation['right'], $request->getLocale())->getOneOrNullResult();
if ($topic) {
$topicIds[] = $comparisonOperation['right'];
if ($comparisonOperation['symbol'] == '=') {
$hasTopics[] = $topicIds;
} else {
$hasNotTopics[] = $topicIds;
}
}
} elseif ($leftOperand == 'author') {
$otherTables['ArticleAuthors'] = array('__JOIN' => ',');
$author = Author::ReadName($comparisonOperation['right']);
$symbol = $comparisonOperation['symbol'];
$valModifier = strtolower($symbol) == 'like' ? '%' : '';
$firstName = trim(trim($g_ado_db->escape($author['first_name']), "'"));
$lastName = trim(trim($g_ado_db->escape($author['last_name']), "'"));
$authors = $g_ado_db->GetAll("\n SELECT Authors.id\n FROM Authors\n WHERE CONCAT(Authors.first_name, ' ', Authors.last_name) {$symbol}\n '{$valModifier}{$firstName} {$lastName}{$valModifier}'\n ");
$authorsIds = array();
foreach ($authors as $author) {
$authorsIds[] = $author['id'];
}
$whereCondition = "ArticleAuthors.fk_author_id IN (" . implode(',', $authorsIds) . ")";
$selectClauseObj->addWhere($whereCondition);
$selectClauseObj->addWhere('Articles.Number = ArticleAuthors.fk_article_number');
$selectClauseObj->addWhere('Articles.IdLanguage = ArticleAuthors.fk_language_id');
} elseif ($leftOperand == 'search_phrase') {
$searchQuery = ArticleIndex::SearchQuery($comparisonOperation['right'], $comparisonOperation['symbol']);
if (!empty($searchQuery)) {
$otherTables["({$searchQuery})"] = array('__TABLE_ALIAS' => 'search', '__JOIN' => 'INNER JOIN', 'Number' => 'NrArticle', 'IdLanguage' => 'IdLanguage');
}
} elseif ($leftOperand == 'location') {
$num = '[-+]?[0-9]+(?:\\.[0-9]+)?';
if (preg_match("/({$num}) ({$num}), ({$num}) ({$num})/", trim($comparisonOperation['right']), $matches)) {
$queryLocation = Geo_Map::GetGeoSearchSQLQuery(array(array('latitude' => $matches[1], 'longitude' => $matches[2]), array('latitude' => $matches[3], 'longitude' => $matches[4])));
$selectClauseObj->addWhere("Articles.Number IN ({$queryLocation})");
}
} elseif ($leftOperand == 'insection') {
$selectClauseObj->addWhere("Articles.NrSection IN " . $comparisonOperation['right']);
} elseif ($leftOperand == 'complex_date') {
/* @var $param ComparisonOperation */
$fieldName = key($roper = $param->getRightOperand());
$searchValues = array();
foreach (explode(",", current($roper)) as $values) {
list($key, $value) = explode(":", $values, 2);
$searchValues[preg_replace("`(?<=[a-z])(_([a-z]))`e", "strtoupper('\\2')", trim($key))] = trim($value);
}
$repo = Zend_Registry::get('container')->getService('em')->getRepository('Newscoop\\Entity\\ArticleDatetime');
/* @var $repo \Newscoop\Entity\Repository\ArticleRepository */
$searchValues['fieldName'] = $fieldName;
$sqlQuery = $repo->findDates((object) $searchValues, true)->getFindDatesSQL('dt.articleId');
if (!is_null($sqlQuery)) {
$whereCondition = "Articles.Number IN (\n{$sqlQuery})";
$selectClauseObj->addWhere($whereCondition);
}
} else {
// custom article field; has a correspondence in the X[type]
// table fields
$sqlQuery = self::ProcessCustomField($comparisonOperation, $languageId);
if (!is_null($sqlQuery)) {
$whereCondition = "Articles.Number IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
}
}
if (count($hasTopics) > 0 || count($hasNotTopics) > 0) {
$typeAttributes = ArticleTypeField::FetchFields(null, null, 'topic', false, false, false, true, $p_skipCache);
}
if (count($hasTopics) > 0) {
if ($matchAllTopics) {
foreach ($hasTopics as $topicId) {
$sqlQuery = Article::BuildTopicSelectClause($topicId, $typeAttributes);
$whereCondition = "Articles.Number IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
} else {
$sqlQuery = Article::BuildTopicSelectClause($hasTopics, $typeAttributes);
$whereCondition = "Articles.Number IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
}
if (count($hasNotTopics) > 0) {
$sqlQuery = Article::BuildTopicSelectClause($hasNotTopics, $typeAttributes);
$whereCondition = "Articles.Number NOT IN (\n{$sqlQuery} )";
$selectClauseObj->addWhere($whereCondition);
}
// create the count clause object
$countClauseObj = clone $selectClauseObj;
if (!is_array($p_order)) {
$p_order = array();
}
// sets the ORDER BY condition
$p_order = array_merge($p_order, Article::$s_defaultOrder);
$order = Article::ProcessListOrder($p_order, $otherTables, $otherWhereConditions);
foreach ($order as $orderDesc) {
$orderColumn = $orderDesc['field'];
$orderDirection = $orderDesc['dir'];
$selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection);
}
if (count($otherTables) > 0) {
foreach ($otherTables as $table => $fields) {
$joinType = 'LEFT JOIN';
if (isset($fields['__JOIN'])) {
$joinType = $fields['__JOIN'];
}
if (isset($fields['__TABLE_ALIAS'])) {
$tableAlias = $fields['__TABLE_ALIAS'];
$tableJoin = "\n {$joinType} {$table} AS {$tableAlias} \n";
} else {
$tableAlias = $table;
$tableJoin = "\n {$joinType} {$tableAlias} \n";
}
$firstCondition = true;
foreach ($fields as $parent => $child) {
if ($parent == '__TABLE_ALIAS' || $parent == '__JOIN') {
continue;
}
$condOperator = $firstCondition ? ' ON ' : 'AND ';
if ($parent == '__CONST') {
$constTable = $child['table'];
$constField = $child['field'];
$value = $child['value'];
$negate = isset($child['negate']) ? $child['negate'] : false;
if (is_null($value)) {
$operator = $negate ? 'IS NOT' : 'IS';
$value = 'NULL';
} else {
$operator = $negate ? '!=' : '=';
$value = $g_ado_db->escape($value);
}
$tableJoin .= " {$condOperator}`{$constTable}`.`{$constField}` {$operator} {$value}\n";
} else {
$tableJoin .= " {$condOperator}`{$articleTable}`.`{$parent}` = `{$tableAlias}`.`{$child}`\n";
}
$firstCondition = false;
}
$selectClauseObj->addJoin($tableJoin);
$countClauseObj->addJoin($tableJoin);
}
}
// other where conditions needed for certain order options
foreach ($otherWhereConditions as $whereCondition) {
$selectClauseObj->addWhere($whereCondition);
$countClauseObj->addWhere($whereCondition);
}
// gets the column list to be retrieved for the database table
$selectClauseObj->addColumn('Articles.Number');
$selectClauseObj->addColumn('Articles.IdLanguage');
$countClauseObj->addColumn('COUNT(*)');
// sets the LIMIT start and offset values
$selectClauseObj->setLimit($p_start, $p_limit);
// builds the SQL query
$selectQuery = $selectClauseObj->buildQuery();
$countQuery = $countClauseObj->buildQuery();
// runs the SQL query
$articles = $g_ado_db->GetAll($selectQuery);
if (is_array($articles)) {
$p_count = $g_ado_db->GetOne($countQuery);
// builds the array of Article objects
$articlesList = array();
foreach ($articles as $article) {
if ($returnObjs) {
$articlesList[] = new Article($article['IdLanguage'], $article['Number']);
} else {
$articlesList[] = array('language_id' => $article['IdLanguage'], 'number' => $article['Number']);
}
}
} else {
$articlesList = array();
$p_count = 0;
}
// stores articles list in cache
if (!$p_skipCache && CampCache::IsEnabled()) {
$cacheListObj->storeInCache($articlesList);
}
return $articlesList;
}
