function _buildQuery() { global $mainframe; // Get the page/component configuration $params =& $mainframe->getParams(); // Voting is turned on, get voting data as well for the content items $voting = ContentHelperQuery::buildVotingQuery($params); // Get the WHERE and ORDER BY clauses for the query $where = $this->_buildContentWhere(); $orderby = $this->_buildContentOrderBy(); $query = ' SELECT a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by,' . ' a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.images, a.attribs, a.urls, a.metakey, a.metadesc, a.access,' . ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug,' . ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug,' . ' CHAR_LENGTH( a.`fulltext` ) AS readmore,' . ' u.name AS author, u.usertype, g.name AS groups, u.email as author_email, cc.title AS category, s.title AS section, s.ordering AS s_ordering, cc.ordering AS cc_ordering, a.ordering AS a_ordering, f.ordering AS f_ordering' . $voting['select'] . ' FROM #__content AS a' . ' INNER JOIN #__content_frontpage AS f ON f.content_id = a.id' . ' LEFT JOIN #__categories AS cc ON cc.id = a.catid' . ' LEFT JOIN #__sections AS s ON s.id = a.sectionid' . ' LEFT JOIN #__users AS u ON u.id = a.created_by' . ' LEFT JOIN #__groups AS g ON a.access = g.id' . $voting['join'] . $where . $orderby; return $query; }
/** * Method to load content article data * * @access private * @return boolean True on success * @since 1.5 */ function _loadArticle() { global $mainframe; if ($this->_id == '0') { return false; } // Load the content if it doesn't already exist if (empty($this->_article)) { // Get the page/component configuration $params =& $mainframe->getParams(); // If voting is turned on, get voting data as well for the article $voting = ContentHelperQuery::buildVotingQuery($params); // Get the WHERE clause $where = $this->_buildContentWhere(); $query = 'SELECT a.*, u.name AS author, u.usertype, cc.title AS category, s.title AS section,' . ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,' . ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug,' . ' g.name AS groups, s.published AS sec_pub, cc.published AS cat_pub, s.access AS sec_access, cc.access AS cat_access ' . $voting['select'] . ' FROM #__content AS a' . ' LEFT JOIN #__categories AS cc ON cc.id = a.catid' . ' LEFT JOIN #__sections AS s ON s.id = cc.section AND s.scope = "content"' . ' LEFT JOIN #__users AS u ON u.id = a.created_by' . ' LEFT JOIN #__groups AS g ON a.access = g.id' . $voting['join'] . $where; $this->_db->setQuery($query); $this->_article = $this->_db->loadObject(); if (!$this->_article) { return false; } if ($this->_article->publish_down == $this->_db->getNullDate()) { $this->_article->publish_down = JText::_('Never'); } // These attributes need to be defined in order for the voting plugin to work if (count($voting) && !isset($this->_article->rating_count)) { $this->_article->rating_count = 0; $this->_article->rating = 0; } return true; } return true; }
function _buildQuery($state = 1) { $app = JFactory::getApplication(); $params = $app->getParams(); $user = JFactory::getUser(); $userGroups = implode(',', $user->getAuthorisedViewLevels()); // If voting is turned on, get voting data as well for the content items $voting = ContentHelperQuery::buildVotingQuery($params); $metakey = trim($this->_article->metakey); $thisAlias = trim($this->_article->created_by_alias); $thisAuthor = $this->_article->created_by; $matchAuthor = trim($params->get('matchAuthor', 0)); $matchAuthorAlias = trim($params->get('matchAuthorAlias', 0)); $noauth = !$params->get('show_noauth'); $anyOrAll = $params->get('anyOrAll', 'any'); $publishedState = $params->get('fjArticleState', 1); if ($metakey || $matchAuthor || $matchAuthorAlias && $thisAlias) { $db = $this->getDBO(); $user = JFactory::getUser(); $date = JFactory::getDate(); $now = $date->toMySQL(); $nullDate = $db->getNullDate(); // explode the meta keys on a comma $keys = explode(',', $metakey); $likes = array(); // assemble any non-blank word(s) foreach ($keys as $key) { $key = trim($key); if ($key) { // surround with commas so first and last items have surrounding commas $likes[] = ',' . $this->_db->getEscaped($key) . ','; } } $ordering = $params->get('ordering', 'alpha'); $sqlSort = $this->_buildContentOrderBy($ordering); // set connector to OR or AND based on parameter $sqlConnector = $anyOrAll == 'any' ? ' OR ' : ' AND '; if ($likes && $anyOrAll != 'exact') { $keywordSelection = ' CONCAT(",", REPLACE(a.metakey,", ",","),",") LIKE "%' . implode('%"' . $sqlConnector . 'CONCAT(",", REPLACE(a.metakey,", ",","),",") LIKE "%', $likes) . '%"'; } else { if ($likes && $anyOrAll == 'exact') { $keywordSelection = ' UPPER(a.metakey) = "' . strtoupper($metakey) . '" '; } else { // in this case we are only going to match on author or alias, so we put a harmless false selection here $keywordSelection = ' 1 = 2 '; // just as a placeholder (so our AND's and OR's still work) } } // get published state select if (is_array($publishedState)) { $publishedStateCondition = implode(',', $publishedState); } else { $publishedStateCondition = $publishedState; } // get category selections // process either as comma-delimited list or as array (for backward compatibility) $catid = is_array($params->get('catid')) ? implode(',', $params->get('catid')) : trim($params->get('catid')); $catCondition = ''; if ($catid || $catid == '0') { $ids = str_replace('C', $this->_article->catid, strtoupper($catid)); $ids = explode(',', $ids); JArrayHelper::toInteger($ids); $catCondition = ' AND a.catid IN (' . implode(',', $ids) . ')'; } if ($matchAuthor) { $matchAuthorCondition = $sqlConnector . 'a.created_by = ' . $db->Quote($thisAuthor) . ' '; } if ($matchAuthorAlias && $thisAlias) { $matchAuthorAliasCondition = $sqlConnector . 'UPPER(a.created_by_alias) = ' . $db->Quote(strtoupper($thisAlias)) . ' '; } else { $matchAuthorAliasCondition = ' '; } if ($noauth) { $noauthCondition = ' AND a.access IN (' . $userGroups . ')' . ' AND cc.access IN (' . $userGroups . ')' . ' AND cc.published = 1 '; } if ($params->get('filter_type') != 'none') { $filterWhere = $this->_getFilterWhere($params->get('filter_type')); } else { $filterWhere = ''; } // select other items based on the metakey field 'like' the keys found $query = 'SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, DATE_FORMAT(a.created, "%Y-%m-%d") AS created, a.state, a.catid, a.hits,' . ' a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by,' . ' a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.attribs, a.hits, a.images, a.urls, a.ordering, a.metakey, a.metadesc, a.access,' . ' cc.access AS cat_access, cc.published AS cat_state, ' . ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,' . ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug,' . ' CHAR_LENGTH( a.`fulltext` ) AS readmore, u.name AS author ' . $voting['select'] . ', ' . ' a.metakey, "0" as match_count, "" as match_list, "" as main_article_keywords, ' . ' cc.title as category, "article" as link_type, cc.alias as category_alias, parent.id as parent_id, parent.alias as parent_alias' . ' FROM #__content AS a' . ' LEFT JOIN #__content_frontpage AS f ON f.content_id = a.id' . ' LEFT JOIN #__categories AS cc ON cc.id = a.catid' . ' LEFT JOIN #__users AS u ON u.id = a.created_by' . ' LEFT JOIN #__categories AS parent on parent.id = cc.parent_id' . $voting['join'] . ' WHERE a.id != ' . (int) $this->_id . ' AND a.state IN (' . $publishedStateCondition . ') ' . ($noauth ? $noauthCondition : '') . ' AND ( ' . $keywordSelection . ($matchAuthor ? $matchAuthorCondition : '') . ($matchAuthorAlias ? $matchAuthorAliasCondition : '') . ' )' . ' AND ( a.publish_up = ' . $db->Quote($nullDate) . ' OR a.publish_up <= ' . $db->Quote($now) . ' )' . ' AND ( a.publish_down = ' . $db->Quote($nullDate) . ' OR a.publish_down >= ' . $db->Quote($now) . ' ) ' . ($catCondition ? $catCondition : '') . $filterWhere . $sqlSort; // sort the query } else { $query = ''; } return $query; }
protected function _buildQuery($countOnly = false) { $app = JFactory::getApplication(); // Get the page/component configuration $params =& $app->getParams(); // If voting is turned on, get voting data as well for the content items $voting = ContentHelperQuery::buildVotingQuery($params); // Get the WHERE and ORDER BY clauses for the query $where = $this->_buildContentWhere(); $orderby = $this->_buildContentOrderBy(); if (!$countOnly) { $query = 'SELECT a.id, a.title, a.title_alias, a.introtext, a.sectionid, a.state, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by,' . ' a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.attribs, a.hits, a.images, a.urls, a.ordering, a.metakey, a.metadesc, a.access, cc.title AS category,' . ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug,' . ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug,' . ' CHAR_LENGTH(a.`fulltext`) AS readmore, u.name AS author, u.usertype' . $voting['select']; } else { $query = 'SELECT count(*) '; } $query .= ' FROM #__content AS a' . ' INNER JOIN #__categories AS cc ON cc.id = a.catid' . ' LEFT JOIN #__users AS u ON u.id = a.created_by' . $voting['join'] . $where . $orderby; return $query; }
function _buildQuery($state = 1) { global $mainframe; // Get the page/component configuration $params =& $mainframe->getParams(); // If voting is turned on, get voting data as well for the content items $voting = ContentHelperQuery::buildVotingQuery($params); // Get the WHERE and ORDER BY clauses for the query $where = $this->_buildContentWhere($state); $orderby = $this->_buildContentOrderBy($state); // Custom Properties join $helper = new comZonalesHelper(); $cpjoin = $helper->_buildCustomPropertiesJoin(); $query = 'SELECT cc.title AS category, a.id, a.title, a.alias, a.title_alias, a.introtext, a.fulltext, a.sectionid, a.state, a.catid, a.created, a.created_by, a.created_by_alias, a.modified, a.modified_by,' . ' a.checked_out, a.checked_out_time, a.publish_up, a.publish_down, a.attribs, a.hits, a.images, a.urls, a.ordering, a.metakey, a.metadesc, a.access,' . ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(":", a.id, a.alias) ELSE a.id END as slug,' . ' CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(":", cc.id, cc.alias) ELSE cc.id END as catslug,' . ' CHAR_LENGTH( a.`fulltext` ) AS readmore, u.name AS author, u.usertype, g.name AS groups, u.email as author_email' . $voting['select'] . ' FROM #__content AS a' . ' LEFT JOIN #__categories AS cc ON a.catid = cc.id' . ' LEFT JOIN #__users AS u ON u.id = a.created_by' . ' LEFT JOIN #__groups AS g ON a.access = g.id' . $cpjoin . $voting['join'] . $where . ' GROUP BY a.id' . $orderby; return $query; }