/** * Build an SQL query to load the list data. * * @return JQuery */ protected function _getListQuery() { // Create a new query object. $query = new JQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.*')); $query->from('`#__viewlevels` AS a'); // Add the level in the tree. $query->group('a.id'); // Filter the items over the search string if set. $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('a.id = ' . (int) substr($search, 3)); } else { $search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%'); $query->where('a.title LIKE ' . $search); } } $query->group('a.id'); // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.lft')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC'))); //echo nl2br(str_replace('#__','jos_',$query)); return $query; }
/** * Build an SQL query to load the list data. * * @return JQuery */ protected function _getListQuery() { // Create a new query object. $query = new JQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.*')); $query->from('`#__usergroups` AS a'); // Add the level in the tree. $query->select('COUNT(DISTINCT c2.id) AS level'); $query->join('LEFT OUTER', '`#__usergroups` AS c2 ON a.lft > c2.lft AND a.rgt < c2.rgt'); $query->group('a.id'); // Count the objects in the user group. $query->select('COUNT(DISTINCT map.user_id) AS user_count'); $query->join('LEFT', '`#__user_usergroup_map` AS map ON map.group_id = a.id'); $query->group('a.id'); // Filter the comments over the search string if set. $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('a.id = ' . (int) substr($search, 3)); } else { $search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%'); $query->where('a.title LIKE ' . $search); } } // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.lft')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC'))); //echo nl2br(str_replace('#__','jos_',$query)); return $query; }
/** * Method to build an SQL query to load the list data. * * @access protected * @return string An SQL query * @since 1.0 */ function _getListQuery() { $query = new JQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.*')); $query->from('`#__social_threads` AS a'); // Join on the comments table. $query->select('COUNT(c.id) AS comment_count'); $query->leftJoin('#__social_comments AS c ON c.thread_id = a.id'); // Join on the ratings table. $query->select('pscore_count'); $query->leftJoin('#__social_ratings AS r ON r.thread_id = a.id'); $query->group('a.id'); // Filter the items over the context if set. if ($context = $this->getState('filter.context')) { $query->where('a.context = ' . $this->_db->Quote($context)); } // Filter by search string. if ($search = $this->getState('filter.search')) { if (stripos($search, 'id:') === 0) { $query->where('a.id = ' . (int) substr($search, 3)); } else { $search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%'); $query->where('a.page_title LIKE ' . $search); } } // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.id') . ' ' . $this->getState('list.direction', 'asc'))); //echo nl2br(str_replace('#__','jos_',$query)); return $query; }
/** * Method to get a list of options for a list input. * * @return array An array of JHtml options. */ protected function _getOptions() { $db =& JFactory::getDbo(); $query = new JQuery(); $query->select('a.id AS value, a.title AS text, a.level'); $query->from('#__categories AS a'); $query->join('LEFT', '`#__categories` AS b ON a.lft > b.lft AND a.rgt < b.rgt'); // Filter by the type if ($extension = $this->_form->getValue('extension')) { $query->where('(a.extension = ' . $db->quote($extension) . ' OR a.parent_id = 0)'); } // Prevent parenting to children of this item. if ($id = $this->_form->getValue('id')) { $query->join('LEFT', '`#__categories` AS p ON p.id = ' . (int) $id); $query->where('NOT(a.lft >= p.lft AND a.rgt <= p.rgt)'); } $query->group('a.id'); $query->order('a.lft ASC'); // Get the options. $db->setQuery($query); $options = $db->loadObjectList(); // Check for a database error. if ($db->getErrorNum()) { JError::raiseWarning(500, $db->getErrorMsg()); } // Pad the option text with spaces using depth level as a multiplier. for ($i = 0, $n = count($options); $i < $n; $i++) { $options[$i]->text = str_repeat('- ', $options[$i]->level) . $options[$i]->text; } $options = array_merge(parent::_getOptions(), $options); return $options; }
/** * Build an SQL query to load the list data. * * @return JQuery */ protected function _getListQuery() { // Create a new query object. $query = new JQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.id, a.template, a.title, a.home, a.client_id')); $query->from('`#__template_styles` AS a'); // Join on menus. $query->select('COUNT(m.template_style_id) AS assigned'); $query->leftjoin('#__menu AS m ON m.template_style_id = a.id'); $query->group('a.id'); // Filter by template. if ($template = $this->getState('filter.template')) { $query->where('a.template = ' . $this->_db->quote($template)); } // Filter by client. $clientId = $this->getState('filter.client_id'); if (is_numeric($clientId)) { $query->where('a.client_id = ' . (int) $clientId); } // Filter by search in title $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('a.id = ' . (int) substr($search, 3)); } else { $search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%'); $query->where('a.template LIKE ' . $search . ' OR a.title LIKE ' . $search); } } // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.name')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC'))); //echo nl2br(str_replace('#__','jos_',$query)); return $query; }
function getList(&$params) { //get database $db =& JFactory::getDbo(); $query = new JQuery(); $query->select('MONTH(created) AS created_month, created, id, title, YEAR(created) AS created_year'); $query->from('#__content'); $query->where('state = -1 AND checked_out = 0'); $query->group('created_year DESC, created_month DESC'); // $query = 'SELECT MONTH(created) AS created_month, created, id, title, YEAR(created) AS created_year' . // ' FROM #__content' . // ' WHERE (state = -1 AND checked_out = 0)' . // ' GROUP BY created_year DESC, created_month DESC'; $db->setQuery($query, 0, intval($params->get('count'))); $rows = $db->loadObjectList(); $menu =& JSite::getMenu(); $item = $menu->getItems('link', 'index.php?option=com_content&view=archive', true); $itemid = isset($item) ? '&Itemid=' . $item->id : ''; $i = 0; $lists = array(); foreach ($rows as $row) { $date =& JFactory::getDate($row->created); $created_month = $date->toFormat("%m"); $month_name = $date->toFormat("%B"); $created_year = $date->toFormat("%Y"); $lists[$i]->link = JRoute::_('index.php?option=com_content&view=archive&year=' . $created_year . '&month=' . $created_month . $itemid); $lists[$i]->text = $month_name . ', ' . $created_year; $i++; } return $lists; }
/** * Get a list of the available menus. * * @return array An array of the available menus (from the menu types table). */ public static function getMenus() { $db =& JFactory::getDbo(); $query = new JQuery(); $query->select('a.*, SUM(b.home) AS home'); $query->from('#__menu_types AS a'); $query->leftJoin('#__menu AS b ON b.menutype = a.menutype'); $query->group('a.id'); $db->setQuery($query); $result = $db->loadObjectList(); return $result; }
/** * Build an SQL query to load the list data. * * @return JQuery */ protected function _getListQuery() { // Create a new query object. $query = new JQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.*')); $query->from('`#__users` AS a'); // Join over the group mapping table. $query->select('COUNT(map.group_id) AS group_count'); $query->join('LEFT', '#__user_usergroup_map AS map ON map.user_id = a.id'); $query->group('a.id'); // Join over the user groups table. $query->select('GROUP_CONCAT(g2.title SEPARATOR ' . $this->_db->Quote("\n") . ') AS group_names'); $query->join('LEFT', '#__usergroups AS g2 ON g2.id = map.group_id'); // If the model is set to check item state, add to the query. $state = $this->getState('filter.state'); if (is_numeric($state)) { $query->where('a.block = ' . (int) $state); } // If the model is set to check the activated state, add to the query. $active = $this->getState('filter.active'); if (is_numeric($active)) { if ($active == '0') { $query->where('a.activation = ""'); } else { if ($active == '1') { $query->where('LENGTH(a.activation) = 32'); } } } // Filter the items over the group id if set. if ($groupId = $this->getState('filter.group_id')) { $query->join('LEFT', '#__user_usergroup_map AS map2 ON map2.user_id = a.id'); $query->where('map2.group_id = ' . (int) $groupId); } // Filter the items over the search string if set. if ($this->getState('filter.search') !== '') { // Escape the search token. $token = $this->_db->Quote('%' . $this->_db->getEscaped($this->getState('filter.search')) . '%'); // Compile the different search clauses. $searches = array(); $searches[] = 'a.name LIKE ' . $token; $searches[] = 'a.username LIKE ' . $token; $searches[] = 'a.email LIKE ' . $token; // Add the clauses to the query. $query->where('(' . implode(' OR ', $searches) . ')'); } // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.name')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC'))); //echo nl2br(str_replace('#__','jos_',$query)); return $query; }
function getOnlineUserNames() { $db =& JFactory::getDbo(); $result = null; $query = new JQuery(); $query->select('a.username, a.time, a.userid, a.usertype, a.client_id'); $query->from('#__session AS a'); $query->where('a.userid != 0'); $query->group('a.userid'); $db->setQuery($query); $result = $db->loadObjectList(); if ($db->getErrorNum()) { JError::raiseWarning(500, $db->stderr()); } return $result; }
/** * Method to build an SQL query to load the list data. * * @return string An SQL query */ protected function _getListQuery() { // Create a new query object. $query = new JQuery(); // Select all fields from the table. $query->select($this->getState('list.select', 'a.*')); $query->from('`#__menu_types` AS a'); // Self join to find the number of published menu items in the menu. $query->select('COUNT(DISTINCT m1.id) AS count_published'); $query->join('LEFT', '`#__menu` AS m1 ON m1.menutype = a.menutype AND m1.published = 1'); // Self join to find the number of unpublished menu items in the menu. $query->select('COUNT(DISTINCT m2.id) AS count_unpublished'); $query->join('LEFT', '`#__menu` AS m2 ON m2.menutype = a.menutype AND m2.published = 0'); // Self join to find the number of trashed menu items in the menu. $query->select('COUNT(DISTINCT m3.id) AS count_trashed'); $query->join('LEFT', '`#__menu` AS m3 ON m3.menutype = a.menutype AND m3.published = -2'); $query->group('a.id'); // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.id')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC'))); //echo nl2br(str_replace('#__','jos_',$query->toString())).'<hr/>'; return $query; }
/** * method to give information about categories */ function &getCategories() { if (!isset($this->_categories)) { $query = new JQuery(); $query->select('MAX(ordering) as `max`'); $query->select('catid'); $query->from('#__banners'); $query->where('state>=0'); $query->group('catid'); $this->_db->setQuery((string) $query); $this->_categories = $this->_db->loadObjectList('catid'); } return $this->_categories; }
/** * Get a list of menu links for one or all menus. * * @param string An option menu to filter the list on, otherwise all menu links are returned as a grouped array. * @param int An optional parent ID to pivot results around. * @param int An optional mode. If parent ID is set and mode=2, the parent and children are excluded from the list. * @param array An optional array of states */ public static function getMenuLinks($menuType = null, $parentId = 0, $mode = 0, $published = array()) { $db = JFactory::getDbo(); $query = new JQuery(); $query->select('a.id AS value, a.title AS text, a.level, a.menutype, a.type'); $query->from('#__menu AS a'); $query->join('LEFT', '`#__menu` AS b ON a.lft > b.lft AND a.rgt < b.rgt'); // Filter by the type if ($menuType) { $query->where('(a.menutype = ' . $db->quote($menuType) . ' OR a.parent_id = 0)'); } if ($parentId) { if ($mode == 2) { // Prevent the parent and children from showing. $query->join('LEFT', '`#__menu` AS p ON p.id = ' . (int) $parentId); $query->where('(a.lft <= p.lft OR a.rgt >= p.rgt)'); } } if (!empty($published)) { if (is_array($published)) { $published = '(' . implode(',', $published) . ')'; } $query->where('a.published IN ' . $published); } $query->group('a.id'); $query->order('a.lft ASC'); // Get the options. $db->setQuery($query); $links = $db->loadObjectList(); // Check for a database error. if ($error = $db->getErrorMsg()) { JError::raiseWarning(500, $error); return false; } // Pad the option text with spaces using depth level as a multiplier. foreach ($links as &$link) { $link->text = str_repeat('- ', $link->level) . $link->text; } if (empty($menuType)) { // If the menutype is empty, group the items by menutype. $query = new JQuery(); $query->select('*'); $query->from('#__menu_types'); $query->where('menutype <> ' . $db->quote('')); $query->order('title, menutype'); $db->setQuery($query); $menuTypes = $db->loadObjectList(); // Check for a database error. if ($error = $db->getErrorMsg()) { JError::raiseWarning(500, $error); return false; } // Create a reverse lookup and aggregate the links. $rlu = array(); foreach ($menuTypes as &$type) { $rlu[$type->menutype] =& $type; $type->links = array(); } // Loop through the list of menu links. foreach ($links as &$link) { if (isset($rlu[$link->menutype])) { $rlu[$link->menutype]->links[] =& $link; // Cleanup garbage. unset($link->menutype); } } return $menuTypes; } else { return $links; } }
/** * Gets a list of the asset groups as an array of JHtml compatible options. * * @param array $config An array of options for the options * * @return mixed An array or false if an error occurs */ public static function assetgroups($config = array()) { if (empty(JHtmlAccess::$asset_groups)) { $db =& JFactory::getDbo(); $query = new JQuery(); $query->select('a.id AS value, a.title AS text'); $query->from('#__viewlevels AS a'); $query->group('a.id'); $query->order('a.ordering ASC'); $db->setQuery($query); JHtmlAccess::$asset_groups = $db->loadObjectList(); // Check for a database error. if ($db->getErrorNum()) { JError::raiseNotice(500, $db->getErrorMsg()); return false; } } return JHtmlAccess::$asset_groups; }
/** * Build an SQL query to load the list data. * * @return JQuery */ protected function _getListQuery() { // Create a new query object. $query = new JQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.id AS id,' . 'a.name AS name,' . 'a.contact AS contact,' . 'a.checked_out AS checked_out,' . 'a.checked_out_time AS checked_out_time, ' . 'a.state AS state,' . 'a.metakey AS metakey,' . 'a.purchase_type as purchase_type')); $query->from('`#__banner_clients` AS a'); // Join over the banners for counting $query->select('COUNT(b.id) as nbanners'); $query->join('LEFT', '#__banners AS b ON a.id = b.cid'); // Join over the users for the checked out user. $query->select('uc.name AS editor'); $query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out'); // Filter by published state $published = $this->getState('filter.state'); if (is_numeric($published)) { $query->where('a.state = ' . (int) $published); } else { if ($published === '') { $query->where('(a.state IN (0, 1))'); } } $query->group('a.id'); // Filter by search in title $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('a.id = ' . (int) substr($search, 3)); } else { $search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%'); $query->where('a.name LIKE ' . $search); } } // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'ordering')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC'))); //echo nl2br(str_replace('#__','jos_',$query)); return $query; }
/** * Generic check for whether dependancies exist for this object in the database schema * * Can be overloaded/supplemented by the child class * * @deprecated * @param mixed An optional primary key value check the row for. If not * set the instance property value is used. * @param array An optional array to compiles standard joins formatted like: * [label => 'Label', name => 'table name' , idfield => 'field', joinfield => 'field'] * @return boolean True on success. * @since 1.0 * @link http://docs.joomla.org/JTable/canDelete */ public function canDelete($pk = null, $joins = null) { // Initialise variables. $k = $this->_tbl_key; $pk = is_null($pk) ? $this->{$k} : $pk; // If no primary key is given, return false. if ($pk === null) { return false; } if (is_array($joins)) { // Get a query object. jimport('joomla.database.query'); $query = new JQuery(); // Setup the basic query. $query->select('`' . $this->_tbl_key . '`'); $query->from('`' . $this->_tbl . '`'); $query->where('`' . $this->_tbl_key . '` = ' . $this->_db->quote($this->{$k})); $query->group('`' . $this->_tbl_key . '`'); // For each join add the select and join clauses to the query object. foreach ($joins as $table) { $query->select('COUNT(DISTINCT ' . $table['idfield'] . ') AS ' . $table['idfield']); $query->join('LEFT', $table['name'] . ' ON ' . $table['joinfield'] . ' = ' . $k); } // Get the row object from the query. $this->_db->setQuery((string) $query, 0, 1); $row = $this->_db->loadObject(); // Check for a database error. if ($this->_db->getErrorNum()) { $this->setError($this->_db->getErrorMsg()); return false; } $msg = array(); $i = 0; foreach ($joins as $table) { $k = $table['idfield'] . $i; if ($obj->{$k}) { $msg[] = JText::_($table['label']); } $i++; } if (count($msg)) { $this->setError("noDeleteRecord" . ": " . implode(', ', $msg)); return false; } else { return true; } } return true; }
/** * Build an SQL query to load the list data. * * @return JQuery */ protected function _getListQuery() { // Create a new query object. $query = new JQuery(); // Select the required fields from the table. $query->select($this->getState('list.select', 'a.id, a.title, a.position, a.module, ' . 'a.checked_out, a.checked_out_time, a.published, a.access, a.ordering, a.language')); $query->from('`#__modules` AS a'); // Join over the users for the checked out user. $query->select('uc.name AS editor'); $query->join('LEFT', '#__users AS uc ON uc.id=a.checked_out'); // Join over the asset groups. $query->select('ag.title AS access_level'); $query->join('LEFT', '#__viewlevels AS ag ON ag.id = a.access'); // Join over the module menus $query->select('MIN(mm.menuid) AS pages'); $query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = a.id'); $query->group('a.id'); // Filter by access level. if ($access = $this->getState('filter.access')) { $query->where('a.access = ' . (int) $access); } // Filter by published state $state = $this->getState('filter.state'); if (is_numeric($state)) { $query->where('a.published = ' . (int) $state); } else { if ($state === '') { $query->where('(a.published IN (0, 1))'); } } // Filter by position $position = $this->getState('filter.position'); if ($position) { $query->where('a.position = ' . $this->_db->Quote($position)); } // Filter by module $module = $this->getState('filter.module'); if ($module) { $query->where('a.module = ' . $this->_db->Quote($module)); } // Filter by client. $clientId = $this->getState('filter.client_id'); if (is_numeric($clientId)) { $query->where('a.client_id = ' . (int) $clientId); } // Filter by search in title $search = $this->getState('filter.search'); if (!empty($search)) { if (stripos($search, 'id:') === 0) { $query->where('a.id = ' . (int) substr($search, 3)); } else { $search = $this->_db->Quote('%' . $this->_db->getEscaped($search, true) . '%'); $query->where('a.title LIKE ' . $search); } } // Add the list ordering clause. $query->order($this->_db->getEscaped($this->getState('list.ordering', 'a.ordering')) . ' ' . $this->_db->getEscaped($this->getState('list.direction', 'ASC'))); //echo nl2br(str_replace('#__','jos_',$query)); return $query; }