Adds a table to the FROM part of the query.
public addTableFrom ( string $p_table ) : void | ||
$p_table | string | The name of the table |
return | void |
/** * Performs a search against the given article field using the given * keywords. Returns the list of articles matching the given criteria. * * @param array $p_keywords * @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 SearchByField(array $p_keywords, $p_fieldName, $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; static $searchFields = array( 'title'=>array('table_fields'=>array('Name'), 'table'=>'Articles'), 'author'=>array('table_fields'=>array('first_name', 'last_name'), 'table'=>'ArticleAuthors', 'join_fields'=>array('Number'=>'fk_article_number'))); $fieldName = strtolower($p_fieldName); if (!array_key_exists($fieldName, $searchFields)) { return false; } $selectClauseObj = new SQLSelectClause(); // set tables and joins between tables $selectClauseObj->setTable('Articles'); $joinTable = $searchFields[$fieldName]['table']; if ($joinTable != 'Articles') { $selectClauseObj->addTableFrom($joinTable); foreach ($searchFields[$fieldName]['join_fields'] as $leftJoinField=>$rightJoinField) { $selectClauseObj->addWhere("`Articles`.`$leftJoinField` = " . "`$joinTable`.`$rightJoinField`"); } if ($fieldName == 'author') { $joinTable = 'Authors'; $selectClauseObj->addTableFrom($joinTable); $selectClauseObj->addWhere("`ArticleAuthors`.`fk_author_id` = " . "`$joinTable`.`id`"); } } foreach ($searchFields[$fieldName]['table_fields'] as $matchField) { $matchFields[] = "`$joinTable`.`$matchField`"; } $matchCond = 'MATCH (' . implode(', ', $matchFields) . ") AGAINST ('"; foreach ($p_keywords as $keyword) { $matchCond .= ($p_matchAll ? '+' : '') . $g_ado_db->escape($keyword) . ' '; } $matchCond .= "' IN BOOLEAN MODE)"; $selectClauseObj->addWhere($matchCond); $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'); $selectClauseObj->addColumn($matchCond . ' AS score'); // set the order for the select clause $p_order = count($p_order) > 0 ? $p_order : Article::$s_defaultOrder; $order = Article::ProcessListOrder($p_order); $selectClauseObj->addOrderBy('score DESC'); 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; }
/** * Returns an article attachments 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 $articleAttachmentsList * An array of Attachment 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__); $articleAttachmentsList = $cacheListObj->fetchFromCache(); if ($articleAttachmentsList !== false && is_array($articleAttachmentsList)) { return $articleAttachmentsList; } } $hasArticleNr = false; $selectClauseObj = new SQLSelectClause(); $countClauseObj = new SQLSelectClause(); // sets the where conditions foreach ($p_parameters as $param) { $comparisonOperation = self::ProcessParameters($param); if (sizeof($comparisonOperation) < 1) { break; } if (strpos($comparisonOperation['left'], 'fk_article_number')) { $whereCondition = $g_ado_db->escapeOperation($comparisonOperation); $hasArticleNr = true; } elseif (strpos($comparisonOperation['left'], 'fk_language_id')) { $whereCondition = '(' . $comparisonOperation['left'] . ' IS NULL OR ' . $comparisonOperation['left'] . " = " . $g_ado_db->escape($comparisonOperation['right']) . ")"; } else { $whereCondition = $g_ado_db->escapeOperation($comparisonOperation); } $selectClauseObj->addWhere($whereCondition); $countClauseObj->addWhere($whereCondition); } // validates whether article number was given if ($hasArticleNr === false) { CampTemplate::singleton()->trigger_error('missed parameter Article ' . 'Number in statement list_article_attachments'); return; } // sets the columns to be fetched $tmpAttachment = new Attachment(); $columnNames = $tmpAttachment->getColumnNames(true); foreach ($columnNames as $columnName) { $selectClauseObj->addColumn($columnName); } $countClauseObj->addColumn('COUNT(*)'); // sets the main table for the query $selectClauseObj->setTable($tmpAttachment->getDbTableName()); $countClauseObj->setTable($tmpAttachment->getDbTableName()); unset($tmpAttachment); // adds the ArticleAttachments join and condition to the query $selectClauseObj->addTableFrom('ArticleAttachments'); $selectClauseObj->addWhere('ArticleAttachments.fk_attachment_id = Attachments.id'); $countClauseObj->addTableFrom('ArticleAttachments'); $countClauseObj->addWhere('ArticleAttachments.fk_attachment_id = Attachments.id'); if (!is_array($p_order)) { $p_order = array(); } // sets the order condition if any foreach ($p_order as $orderColumn => $orderDirection) { $selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection); } // sets the limit $selectClauseObj->setLimit($p_start, $p_limit); // builds the query and executes it $selectQuery = $selectClauseObj->buildQuery(); $attachments = $g_ado_db->GetAll($selectQuery); if (is_array($attachments)) { $countQuery = $countClauseObj->buildQuery(); $p_count = $g_ado_db->GetOne($countQuery); // builds the array of attachment objects $articleAttachmentsList = array(); foreach ($attachments as $attachment) { $attchObj = new Attachment($attachment['id']); if ($attchObj->exists()) { $articleAttachmentsList[] = $attchObj; } } } else { $articleAttachmentsList = array(); $p_count = 0; } if (!$p_skipCache && CampCache::IsEnabled()) { $cacheListObj->storeInCache($articleAttachmentsList); } return $articleAttachmentsList; }
/** * Returns an article images 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 $articleImagesList * An array of Image objects */ public static function GetList(array $p_parameters, array $p_order = array(), $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__); $articleImagesList = $cacheListObj->fetchFromCache(); if ($articleImagesList !== false && is_array($articleImagesList)) { return $articleImagesList; } } $hasArticleNr = false; $selectClauseObj = new SQLSelectClause(); $countClauseObj = new SQLSelectClause(); // sets the where conditions foreach ($p_parameters as $param) { $comparisonOperation = self::ProcessListParameters($param); if (sizeof($comparisonOperation) < 3) { break; } if (strpos($comparisonOperation['left'], 'NrArticle')) { $hasArticleNr = true; } $whereCondition = $g_ado_db->escapeOperation($comparisonOperation); $selectClauseObj->addWhere($whereCondition); $countClauseObj->addWhere($whereCondition); } // validates whether article number was given if ($hasArticleNr === false) { CampTemplate::singleton()->trigger_error('Missing parameter Article ' . 'Number in statement list_article_images'); return; } // sets the columns to be fetched $tmpImage = new Image(); $columnNames = $tmpImage->getColumnNames(true); foreach ($columnNames as $columnName) { $selectClauseObj->addColumn($columnName); } $countClauseObj->addColumn('COUNT(*)'); // sets the base table Attachment $selectClauseObj->setTable($tmpImage->getDbTableName()); $countClauseObj->setTable($tmpImage->getDbTableName()); unset($tmpImage); // adds the ArticleImages join and condition to the query $selectClauseObj->addTableFrom('ArticleImages'); $selectClauseObj->addWhere('ArticleImages.IdImage = Images.Id'); $countClauseObj->addTableFrom('ArticleImages'); $countClauseObj->addWhere('ArticleImages.IdImage = Images.Id'); // sets the ORDER BY condition $p_order = array_merge($p_order, self::$s_defaultOrder); $order = self::ProcessListOrder($p_order); foreach ($order as $orderDesc) { $orderColumn = $orderDesc['field']; $orderDirection = $orderDesc['dir']; $selectClauseObj->addOrderBy($orderColumn . ' ' . $orderDirection); } // sets the limit $selectClauseObj->setLimit($p_start, $p_limit); // builds the query executes it $selectQuery = $selectClauseObj->buildQuery(); $images = $g_ado_db->GetAll($selectQuery); if (is_array($images)) { $countQuery = $countClauseObj->buildQuery(); $p_count = $g_ado_db->GetOne($countQuery); // builds the array of image objects $articleImagesList = array(); foreach ($images as $image) { $imgObj = new Image($image['Id']); if ($imgObj->exists()) { $articleImagesList[] = $imgObj; } } } else { $articleImagesList = array(); $p_count = 0; } if (!$p_skipCache && CampCache::IsEnabled()) { $cacheListObj->storeInCache($articleImagesList); } return $articleImagesList; }
/** * Get all the topics in an array, where each element contains the entire * path for each topic. Each topic will be indexed by its ID. * For example, if we have the following topic structure (IDs are * in brackets): * * sports (1) * - baseball (2) * - soccer (3) * - player stats (4) * - matches (5) * politics (6) * - world (7) * - local (8) * * ...then the returned array would look like: * array(array(1 => "sports"), * array(1 => "sports", 2 => "baseball"), * array(1 => "sports", 3 => "soccer"), * array(1 => "sports", 3 => "soccer", 4 => "player stats"), * array(1 => "sports", 3 => "soccer", 5 => "matches"), * array(6 => "politics"), * array(6 => "politics", 7 => "world"), * array(6 => "politics", 8 => "local") * ); * * @param int $p_startingTopicId * @return array */ public static function GetTree($p_startingTopicId = 0) { global $g_ado_db; $topicObj = new Topic(); $query = new SQLSelectClause(); $query->addColumn('node.id'); $query->addColumn('(COUNT(parent.id) - 1) AS depth'); $query->setTable($topicObj->m_dbTableName . ' AS node'); $query->addTableFrom($topicObj->m_dbTableName . ' AS parent'); $query->addWhere('node.node_left BETWEEN parent.node_left AND parent.node_right'); if ($p_startingTopicId > 0) { $query->addTableFrom($topicObj->m_dbTableName . ' AS sub_parent'); $query->addWhere('node.node_left > sub_parent.node_left'); $query->addWhere('node.node_left < sub_parent.node_right'); $query->addWhere('sub_parent.id = ' . (int) $p_startingTopicId); } $query->addGroupField('node.id'); $query->addOrderBy('node.node_left'); $rows = $g_ado_db->GetAll($query->buildQuery()); if (empty($rows)) { // empty tree return array(); } $p_tree = array(); $startDepth = null; $currentPath = array(); foreach ($rows as $row) { $topicId = $row['id']; $depth = $row['depth'] - (int) $startDepth; $topic = new Topic($topicId); if (is_null($startDepth)) { $startDepth = $depth; $depth = 0; $currentPath[$topicId] = $topic; } elseif ($depth > count($currentPath)) { $currentPath[$topicId] = $topic; } elseif ($depth == 0) { $currentPath = array($topicId => $topic); } else { while ($depth < count($currentPath)) { array_pop($currentPath); } $currentPath[$topicId] = $topic; } $p_tree[] = $currentPath; } return $p_tree; }