/** * Method to build the query for the tags * * @access private * @return string */ public function _buildQuery($browsingMode = false) { // Get the WHERE and ORDER BY clauses for the query $where = $this->_buildQueryWhere($browsingMode); $orderby = $this->_buildQueryOrderBy(); $db = EB::db(); if ($browsingMode) { $query = array(); $query[] = 'SELECT a.*, b.`content_id` AS `featured` FROM ' . $db->qn('#__users') . ' AS a '; $query[] = 'LEFT JOIN ' . $db->qn('#__easyblog_featured') . ' AS b '; $query[] = 'ON a.`id` = b.`content_id` AND b.`type`=' . $db->Quote('blogger'); $query[] = $where; $query[] = $orderby; $query = implode(' ', $query); } else { $aclQuery = EB::AclHelper()->genIsbloggerSQL(); $query = 'select count( p.id ) as `totalPost`, COUNT( DISTINCT(g.content_id) ) as `featured`,'; $query .= ' a.*'; $query .= ' from `#__users` as a'; $query .= ' left join `#__easyblog_post` as p on a.`id` = p.`created_by`'; $query .= ' and `p`.`published` = ' . $db->Quote(EASYBLOG_POST_PUBLISHED); $query .= ' and p.`state` = ' . $db->Quote(EASYBLOG_POST_NORMAL); $query .= ' left join `#__easyblog_featured` AS `g` ON a.`id`= g.`content_id` AND g.`type`= ' . $db->Quote('blogger'); $query .= ' where (' . $aclQuery . ')'; $query .= $where; $query .= ' group by a.`id`'; $query .= $orderby; } return $query; }
/** * Method to build the query for the tags * * @access private * @return string */ public function _buildQuery($bloggerOnly = false) { // Get the WHERE and ORDER BY clauses for the query $where = $this->_buildQueryWhere(); $orderby = $this->_buildQueryOrderBy(); $db = EB::db(); if ($bloggerOnly) { $aclQuery = EB::AclHelper()->genIsbloggerSQL(); $query = 'select a.* FROM `#__users` AS `a`'; $query .= $where; $query .= $where ? ' and (' : ' where ('; $query .= $aclQuery . ')'; } else { $query = 'SELECT * FROM ' . $db->nameQuote('#__users'); $query .= $where; } $query .= $orderby; return $query; }
/** * Retrieves a list of bloggers from the site * * @since 5.0 * @access public * @param string * @return */ public function getBloggers($sort = 'latest', $limit = 0, $filter = 'showallblogger', $search = '', $inclusion = array(), $exclusion = array(), $featuredOnly = '') { $db = EB::db(); $config = EB::config(); $nameDisplayFormat = $config->get('layout_nameformat'); $limit = $limit == 0 ? $this->getState('limit') : $limit; $limitstart = $this->input->get('limitstart', $this->getState('limitstart'), 'int'); $limitSQL = ' LIMIT ' . $limitstart . ',' . $limit; $excludedQuery = ''; $excluded = $config->get('layout_exclude_bloggers'); // check if there is exclusion from the backend settings OR from the parameter if (!empty($excluded) || !empty($exclusion)) { $tmp = explode(',', $excluded); if (!empty($excluded) && !empty($exclusion)) { $tmp = array_merge($tmp, $exclusion); } $values = array(); foreach ($tmp as $id) { $values[] = $db->Quote($id); } $excludedQuery = ' AND a.`id` NOT IN (' . implode(',', $values) . ')'; } //inclusion blogger $includedQuery = ''; if (!empty($inclusion)) { $values = array(); foreach ($inclusion as $id) { $values[] = $db->Quote($id); } $includedQuery = ' AND a.id IN (' . implode(',', $values) . ')'; } $searchQuery = ''; if (!empty($search)) { $searchQuery .= ' AND '; switch ($nameDisplayFormat) { case 'name': $searchQuery .= '`name` LIKE ' . $db->Quote('%' . $search . '%'); break; case 'username': $searchQuery .= '`username` LIKE ' . $db->Quote('%' . $search . '%'); break; default: $searchQuery .= '`nickname` LIKE ' . $db->Quote('%' . $search . '%'); break; } } $aclQuery = EB::AclHelper()->genIsbloggerSQL(); // $query = 'select count( p.id ) as `totalPost`, MAX(p.`created`) as `latestPostDate`, COUNT( DISTINCT(g.content_id) ) as `featured`,'; $query = 'select SQL_CALC_FOUND_ROWS count( p.id ) as `totalPost`, MAX(p.`created`) as `latestPostDate`, COUNT( DISTINCT(g.content_id) ) as `featured`,'; $query .= ' a.`id`, b.`nickname`, a.`name`, a.`username`, a.`registerDate`, a.`lastvisitDate`, b.`permalink`'; $query .= ' from `#__users` as a'; $query .= ' left join `#__easyblog_post` as p on a.`id` = p.`created_by`'; $query .= ' inner JOIN `#__easyblog_users` AS `b` ON p.`created_by` = b.`id`'; $query .= ' left join `#__easyblog_featured` AS `g` ON a.`id`= g.`content_id` AND g.`type`= ' . $db->Quote('blogger'); $query .= ' where (' . $aclQuery . ')'; $query .= ' and `p`.`published` = ' . $db->Quote(EASYBLOG_POST_PUBLISHED); $query .= ' and p.`state` = ' . $db->Quote(EASYBLOG_POST_NORMAL); if ($featuredOnly) { $query .= ' and a.`id` = g.`content_id`'; } $query .= $excludedQuery; $query .= $includedQuery; $query .= $searchQuery; $query .= ' group by a.`id`'; if ($filter == 'showbloggerwithpost') { $query .= ' having (count(p.id) > 0)'; } switch ($sort) { case 'featured': $query .= ' ORDER BY `featured` DESC'; break; case 'latestpost': $query .= ' ORDER BY `latestPostDate` DESC'; break; case 'latest': $query .= ' ORDER BY a.`registerDate` DESC'; break; case 'postcount': $query .= ' ORDER BY `totalPost` DESC'; break; case 'active': $query .= ' ORDER BY a.`lastvisitDate` DESC'; break; case 'alphabet': if ($nameDisplayFormat == 'name') { $query .= ' ORDER BY a.`name` ASC'; } else { if ($nameDisplayFormat == 'username') { $query .= ' ORDER BY a.`username` ASC'; } else { $query .= ' ORDER BY b.`nickname` ASC'; } } break; default: break; } $query .= $limitSQL; $db->setQuery($query); $results = $db->loadObjectList(); // now execute found_row() to get the number of records found. $cntQuery = 'select FOUND_ROWS()'; $db->setQuery($cntQuery); $this->_total = $db->loadResult(); if (empty($this->_pagination)) { jimport('joomla.html.pagination'); $this->_pagination = EB::pagination($this->_total, $limitstart, $limit); } return $results; }
/** * Retrieves the total number of authors * * @since 4.0 * @access public * @param string * @return */ public function getTotalAuthors() { $db = EB::db(); $aclQuery = EB::AclHelper()->genIsbloggerSQL(); $query = 'select count(1)'; $query .= ' from `#__users` as a'; $query .= ' where a.`block` = ' . $db->Quote('0'); $query .= ' and (' . $aclQuery . ')'; $db->setQuery($query); $result = $db->loadResult(); return $result; }