/** * Constructor. */ public function __construct() { if (!$this->table) { throw new DomainException('Table name missing from ' . get_class($this)); } $this->db = JFactory::getDbo(); $this->query = $this->db->getQuery(true); $this->query->from($this->table . ' AS a'); }
/** * Get a list of logged users. * * @param JObject The module parameters. * @return mixed An array of articles, or false on error. */ public static function getList($params) { // Initialise variables $db = JFactory::getDbo(); $user = JFactory::getUser(); $query = new JDatabaseQuery(); $query->select('s.time, s.client_id, u.id, u.name, u.username'); $query->from('#__session AS s'); $query->leftJoin('#__users AS u ON s.userid = u.id'); $query->where('s.guest = 0'); $db->setQuery($query, 0, $params->get('count', 5)); $results = $db->loadObjectList(); // Check for database errors if ($error = $db->getErrorMsg()) { JError::raiseError(500, $error); return false; } foreach ($results as $k => $result) { $results[$k]->logoutLink = ''; if ($user->authorise('core.manage', 'com_users')) { $results[$k]->editLink = JRoute::_('index.php?option=com_users&task=user.edit&id=' . $result->id); $results[$k]->logoutLink = JRoute::_('index.php?option=com_login&task=logout&uid=' . $result->id . '&' . JUtility::getToken() . '=1'); } if ($params->get('name', 1) == 0) { $results[$k]->name = $results[$k]->username; } } return $results; }
/** * Tests the JDatabaseQuery::from method. * * @return void * * @since 11.3 */ public function testFrom() { $this->assertThat($this->_instance->from('#__foo'), $this->identicalTo($this->_instance), 'Tests chaining.'); $this->assertThat(trim($this->_instance->from), $this->equalTo('FROM #__foo'), 'Tests rendered value.'); // Add another column. $this->_instance->from('#__bar'); $this->assertThat(trim($this->_instance->from), $this->equalTo('FROM #__foo,#__bar'), 'Tests rendered value after second use.'); }
static function &getModules() { static $modules; if (!is_null($modules)) return $modules; $mainframe = JFactory::getApplication(); $user =& JFactory::getUser(); $db =& JFactory::getDBO(); $aid = $user->get('aid', 0); $groups = implode(',', $user->getAuthorisedViewLevels()); $query = new JDatabaseQuery; $query->select('id, title, module, position, content, showtitle, params, mm.menuid'); $query->from('#__modules AS m'); $query->join('LEFT','#__modules_menu AS mm ON mm.moduleid = m.id'); $query->where('m.published = 1'); if (!$user->authorise('core.admin',1)) { $query->where('m.access IN (' . $groups . ')'); } $query->where('m.client_id = ' . (int)$mainframe->getClientId()); $query->order('position, ordering'); $db->setQuery($query); $modules = $db->loadObjectList('id'); if ($db->getErrorNum()) { $modules = array(); } foreach ($modules as $key => $mod) { $module =& $modules[$key]; $file = $module->module; $custom = substr($file, 0, 4) == 'mod_' ? 0 : 1; $module->user = $custom; $module->name = $custom ? $module->title : substr( $file, 4 ); $module->style = null; $module->position = strtolower($module->position); } return $modules; }
/** * 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 JDatabaseQuery(); $query->select('#__helloworld.id as id,greeting,#__categories.title as category,catid'); $query->from('#__helloworld'); $query->leftJoin('#__categories on catid=#__categories.id'); $db->setQuery((string) $query); $messages = $db->loadObjectList(); $options = array(); if ($messages) { foreach ($messages as $message) { $options[] = JHtml::_('select.option', $message->id, $message->greeting . ($message->catid ? ' (' . $message->category . ')' : '')); } } $options = array_merge(parent::getOptions(), $options); return $options; }
/** * 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 JDatabaseQuery(); $query->select('#__jpaudiotracks.id as id, pathatweb, pathatlocal, file, title, alias, tracknumber, mediatype, bit_rate, sample_rate, channels, channelmode, filesize, length, catid, add_datetime, artist, album, year, description, lyrics, frontcover, backcover, encoder, metakey, metadesc, #__categories.title as category,catid'); $query->from('#__jpaudiotracks'); $query->leftJoin('#__categories on catid=#__categories.id'); $db->setQuery((string) $query); $tracks = $db->loadObjectList(); $options = array(); if ($tracks) { foreach ($tracks as $track) { $options[] = JHtml::_('select.option', $track->id, $track->name . ($track->catid ? ' (' . $track->category . ')' : '')); } } $options = array_merge(parent::getOptions(), $options); return $options; }
/** * 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 JDatabaseQuery(); $query->select('#__quipforum_boards.id as id,topic'); $query->from('#__quipforum_boards'); $db->setQuery((string) $query); $messages = $db->loadObjectList(); $options = array(); if ($messages) { //if($this->allow_all) // $options[] = JHtml::_('select.option', 0, 'Any'); foreach ($messages as $message) { $options[] = JHtml::_('select.option', $message->id, $message->topic); } } $options = array_merge(parent::getOptions(), $options); return $options; }
/** * Method to get the database query * * @return JDatabaseQuery The database query * @since 1.6 */ protected function getListQuery() { $enabled = $this->getState('filter.enabled'); $type = $this->getState('filter.type'); $client = $this->getState('filter.client_id'); $group = $this->getState('filter.group'); $hideprotected = $this->getState('filter.hideprotected'); $query = new JDatabaseQuery(); $query->select('*'); $query->from('#__extensions'); $query->where('state=0'); if ($hideprotected) { $query->where('protected!=1'); } if ($enabled != '') { $query->where('enabled=' . intval($enabled)); } if ($type) { $query->where('type=' . $this->_db->Quote($type)); } if ($client != '') { $query->where('client_id=' . intval($client)); } if ($group != '' && in_array($type, array('plugin', 'library', ''))) { $query->where('folder=' . $this->_db->Quote($group == '*' ? '' : $group)); } // Filter by search in id $search = $this->getState('filter.search'); if (!empty($search) && stripos($search, 'id:') === 0) { $query->where('extension_id = ' . (int) substr($search, 3)); } return $query; }
/** * Load published modules * * @return array */ protected static function &_load() { static $clean; if (isset($clean)) { return $clean; } $Itemid = JRequest::getInt('Itemid'); $app = JFactory::getApplication(); $user = JFactory::getUser(); $groups = implode(',', $user->getAuthorisedViewLevels()); $lang = JFactory::getLanguage()->getTag(); $clientId = (int) $app->getClientId(); $cache = JFactory::getCache('com_modules', ''); $cacheid = md5(serialize(array($Itemid, $groups, $clientId, $lang))); if (!($clean = $cache->get($cacheid))) { $db = JFactory::getDbo(); $query = new JDatabaseQuery(); $query->select('id, title, module, position, content, showtitle, params, mm.menuid'); $query->from('#__modules AS m'); $query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = m.id'); $query->where('m.published = 1'); $date = JFactory::getDate(); $now = $date->toMySQL(); $nullDate = $db->getNullDate(); $query->where('(m.publish_up = ' . $db->Quote($nullDate) . ' OR m.publish_up <= ' . $db->Quote($now) . ')'); $query->where('(m.publish_down = ' . $db->Quote($nullDate) . ' OR m.publish_down >= ' . $db->Quote($now) . ')'); $query->where('m.access IN (' . $groups . ')'); $query->where('m.client_id = ' . $clientId); $query->where('(mm.menuid = ' . (int) $Itemid . ' OR mm.menuid <= 0)'); // Filter by language if ($app->isSite() && $app->getLanguageFilter()) { $query->where('m.language IN (' . $db->Quote($lang) . ',' . $db->Quote('*') . ')'); } $query->order('position, ordering'); // Set the query $db->setQuery($query); $modules = $db->loadObjectList(); $clean = array(); if ($db->getErrorNum()) { JError::raiseWarning(500, JText::sprintf('JLIB_APPLICATION_ERROR_MODULE_LOAD', $db->getErrorMsg())); return $clean; } // Apply negative selections and eliminate duplicates $negId = $Itemid ? -(int) $Itemid : false; $dupes = array(); for ($i = 0, $n = count($modules); $i < $n; $i++) { $module =& $modules[$i]; // The module is excluded if there is an explicit prohibition, or if // the Itemid is missing or zero and the module is in exclude mode. $negHit = $negId === (int) $module->menuid || !$negId && (int) $module->menuid < 0; if (isset($dupes[$module->id])) { // If this item has been excluded, keep the duplicate flag set, // but remove any item from the cleaned array. if ($negHit) { unset($clean[$module->id]); } continue; } $dupes[$module->id] = true; // Only accept modules without explicit exclusions. if (!$negHit) { //determine if this is a custom module $file = $module->module; $custom = substr($file, 0, 4) == 'mod_' ? 0 : 1; $module->user = $custom; // Custom module name is given by the title field, otherwise strip off "com_" $module->name = $custom ? $module->title : substr($file, 4); $module->style = null; $module->position = strtolower($module->position); $clean[$module->id] = $module; } } unset($dupes); // Return to simple indexing that matches the query order. $clean = array_values($clean); $cache->store($clean, $cacheid); } return $clean; }
private function _getExtensionID($type, $id, $client, $group) { $db = $this->parent->getDbo(); $result = $id; $query = new JDatabaseQuery(); $query->select('extension_id'); $query->from('#__extensions'); $query->where('type = ' . $db->Quote($type)); $query->where('element = ' . $db->Quote($id)); switch ($type) { case 'plugin': // plugins have a folder but not a client $query->where('folder = ' . $db->Quote($group)); break; case 'library': case 'package': case 'component': // components, packages and libraries don't have a folder or client // included for completeness break; case 'language': case 'module': case 'template': // languages, modules and templates have a client but not a folder $client = JApplicationHelper::getClientInfo($client, true); $query->where('client_id = ' . (int) $client->id); break; } $db->setQuery($query); $result = $db->loadResult(); // note: for templates, libraries and packages their unique name is their key // this means they come out the same way they came in return $result; }
function getModule($id = 0, $name = '') { $Itemid = $this->Itemid; $app = JFactory::getApplication(); $user = JFactory::getUser(); $groups = implode(',', $user->authorisedLevels()); $db = JFactory::getDbo(); $query = new JDatabaseQuery(); $query->select('id, title, module, position, content, showtitle, params, mm.menuid'); $query->from('#__modules AS m'); $query->join('LEFT', '#__modules_menu AS mm ON mm.moduleid = m.id'); $query->where('m.published = 1'); $query->where('m.id = ' . $id); $date = JFactory::getDate(); $now = $date->toSql(); $nullDate = $db->getNullDate(); $query->where('(m.publish_up = ' . $db->Quote($nullDate) . ' OR m.publish_up <= ' . $db->Quote($now) . ')'); $query->where('(m.publish_down = ' . $db->Quote($nullDate) . ' OR m.publish_down >= ' . $db->Quote($now) . ')'); $clientid = (int) $app->getClientId(); if (!$user->authorise('core.admin', 1)) { $query->where('m.access IN (' . $groups . ')'); } $query->where('m.client_id = ' . $clientid); if (isset($Itemid)) { $query->where('(mm.menuid = ' . (int) $Itemid . ' OR mm.menuid <= 0)'); } $query->order('position, ordering'); // Filter by language if ($app->isSite() && $app->getLanguageFilter()) { $query->where('m.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . ')'); } // Set the query $db->setQuery($query); $cache = JFactory::getCache('com_modules', 'callback'); $cacheid = md5(serialize(array($Itemid, $groups, $clientid, JFactory::getLanguage()->getTag(), $id))); $module = $cache->get(array($db, 'loadObject'), null, $cacheid, false); if (!$module) { return null; } $negId = $Itemid ? -(int) $Itemid : false; // The module is excluded if there is an explicit prohibition, or if // the Itemid is missing or zero and the module is in exclude mode. $negHit = $negId === (int) $module->menuid || !$negId && (int) $module->menuid < 0; // Only accept modules without explicit exclusions. if (!$negHit) { //determine if this is a custom module $file = $module->module; $custom = substr($file, 0, 4) == 'mod_' ? 0 : 1; $module->user = $custom; // Custom module name is given by the title field, otherwise strip off "com_" $module->name = $custom ? $module->title : substr($file, 4); $module->style = null; $module->position = strtolower($module->position); $clean[$module->id] = $module; } return $module; }
private function add_product_rows_query_from(JDatabaseQuery $query) { $db = JFactory::getDbo(); $j_user = JFactory::getUser(); $query->from('#__ecommercewd_products AS T_PRODUCTS'); $query->leftJoin('(SELECT * FROM #__ecommercewd_categories WHERE published = 1) AS T_CATEGORIES ON T_CATEGORIES.id = T_PRODUCTS.category_id'); $query->leftJoin('(SELECT * FROM #__ecommercewd_manufacturers WHERE published = 1) AS T_MANUFACTURERS ON T_MANUFACTURERS.id = T_PRODUCTS.manufacturer_id'); $query->leftJoin('(SELECT * FROM #__ecommercewd_labels WHERE published = 1) AS T_LABELS ON T_LABELS.id = T_PRODUCTS.label_id'); $query->leftJoin('(SELECT * FROM #__ecommercewd_taxes WHERE published = 1) AS T_TAXES ON T_TAXES.id = T_PRODUCTS.tax_id'); $query->leftJoin('(SELECT * FROM #__ecommercewd_discounts WHERE published = 1) AS T_DISCOUNTS ON T_PRODUCTS.discount_id = T_DISCOUNTS.id'); $query->leftJoin('(SELECT product_id, COUNT(id) AS reviews_count FROM #__ecommercewd_feedback WHERE published = 1 GROUP BY product_id) AS T_FEEDBACK ON T_FEEDBACK.product_id = T_PRODUCTS.id'); $query->leftJoin('(SELECT product_id, AVG(rating) AS rating FROM #__ecommercewd_ratings GROUP BY product_id) AS T_RATINGS ON T_RATINGS.product_id = T_PRODUCTS.id'); // tags $query->leftJoin(' ( SELECT T_PRODUCT_TAGS.product_id, CONCAT(",", GROUP_CONCAT(T_TAGS.name SEPARATOR ","), ",") as tag_names FROM #__ecommercewd_tags AS T_TAGS LEFT JOIN #__ecommercewd_producttags AS T_PRODUCT_TAGS ON T_PRODUCT_TAGS.tag_id = T_TAGS.id GROUP BY product_id) AS T_PRODUCT_TAGS ON T_PRODUCT_TAGS.product_id = T_PRODUCTS.id'); // rating if (WDFHelper::is_user_logged_in() == true) { $user_identification = 'j_user_id = ' . $j_user->id; } else { $user_ip_address = WDFUtils::get_client_ip_address(); $user_identification = 'user_ip_address = ' . $db->quote($user_ip_address); } $query->leftJoin('( SELECT product_id, COUNT(rating) AS ratings_count FROM #__ecommercewd_ratings WHERE ' . $user_identification . ' GROUP BY product_id ) AS T_USER_RATINGS ON T_USER_RATINGS.product_id = T_PRODUCTS.id'); if (WDFHelper::is_user_logged_in()) { $j_user = JFactory::getUser(); $user_identification = 'j_user_id = ' . $j_user->id; } else { $order_product_rand_ids = WDFInput::cookie_get_array('order_product_rand_ids'); if (empty($order_product_rand_ids) == false) { $user_identification = 'j_user_id = 0 AND rand_id IN (' . implode(',', $order_product_rand_ids) . ')'; } else { $user_identification = '0'; } } $query->leftJoin('(SELECT product_id, COUNT(id) AS products_in_cart FROM #__ecommercewd_orderproducts WHERE ' . $user_identification . ' AND order_id = 0 GROUP BY product_id) AS T_ORDERPRODUCTS ON T_ORDERPRODUCTS.product_id = T_PRODUCTS.id'); return $query; }
/** * registerQueryTables * * @param DatabaseQuery $query * * @return DatabaseQuery */ public function registerQueryTables(DatabaseQuery $query) { foreach ($this->tables as $alias => $table) { if ($table['join'] == 'FROM') { $query->from($query->quoteName($table['name']) . ' AS ' . $query->quoteName($alias)); } else { $query->join($table['join'], $query->quoteName($table['name']) . ' AS ' . $query->quoteName($alias) . ' ' . $table['condition']); } } return $query; }
protected function _load($id) { $db = JFactory::getDbo(); $app = JFactory::getApplication(); $user = JFactory::getUser(); $extension = $this->_extension; // Record that this $id has been checked $this->_checkedCategories[$id] = true; $query = new JDatabaseQuery(); // right join with c for category $query->select('c.*'); $query->select('CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(":", c.id, c.alias) ELSE c.id END as slug'); $query->from('#__categories as c'); $query->where('(c.extension=' . $db->Quote($extension) . ' OR c.extension=' . $db->Quote('system') . ')'); if ($this->_options['access']) { $query->where('c.access IN (' . implode(',', $user->getAuthorisedViewLevels()) . ')'); } if ($this->_options['published'] == 1) { $query->where('c.published = 1'); } $query->order('c.lft'); // s for selected id if ($id != 'root') { // Get the selected category $query->leftJoin('#__categories AS s ON (s.lft <= c.lft AND s.rgt >= c.rgt) OR (s.lft > c.lft AND s.rgt < c.rgt)'); $query->where('s.id=' . (int) $id); } $subQuery = ' (SELECT cat.id as id FROM #__categories AS cat JOIN #__categories AS parent ' . 'ON cat.lft BETWEEN parent.lft AND parent.rgt WHERE parent.extension = ' . $db->quote($extension) . ' AND parent.published != 1 GROUP BY cat.id) '; $query->leftJoin($subQuery . 'AS badcats ON badcats.id = c.id'); $query->where('badcats.id is null'); // i for item if (isset($this->_options['countItems']) && $this->_options['countItems'] == 1) { if ($this->_options['published'] == 1) { $query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id AND i.' . $this->_statefield . ' = 1'); } else { $query->leftJoin($db->nameQuote($this->_table) . ' AS i ON i.' . $db->nameQuote($this->_field) . ' = c.id'); } $query->select('COUNT(i.' . $db->nameQuote($this->_key) . ') AS numitems'); } // Group by $query->group('c.id'); // Filter by language if ($app->isSite() && $app->getLanguageFilter()) { $query->where('(' . ($id != 'root' ? 'c.id=s.id OR ' : '') . 'c.language in (' . $db->Quote(JFactory::getLanguage()->getTag()) . ',' . $db->Quote('*') . '))'); } // Get the results $db->setQuery($query); $results = $db->loadObjectList('id'); $childrenLoaded = false; if (count($results)) { // foreach categories foreach ($results as $result) { // Deal with root category if ($result->id == 1) { $result->id = 'root'; } // Deal with parent_id if ($result->parent_id == 1) { $result->parent_id = 'root'; } // Create the node if (!isset($this->_nodes[$result->id])) { // Create the JCategoryNode and add to _nodes $this->_nodes[$result->id] = new JCategoryNode($result, $this); // If this is not root, and if the current nodes parent is in the list or the current node parent is 0 if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) { // Compute relationship between node and its parent - set the parent in the _nodes field $this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]); } // if the node's parent id is not in the _nodes list and the node is not root (doesn't have parent_id == 0), // then remove this nodes from the list if (!(isset($this->_nodes[$result->parent_id]) || $result->parent_id == 0)) { unset($this->_nodes[$result->id]); continue; } if ($result->id == $id || $childrenLoaded) { $this->_nodes[$result->id]->setAllLoaded(); $childrenLoaded = true; } } else { if ($result->id == $id || $childrenLoaded) { // Create the JCategoryNode $this->_nodes[$result->id] = new JCategoryNode($result, $this); if ($result->id != 'root' && (isset($this->_nodes[$result->parent_id]) || $result->parent_id)) { // Compute relationship between node and its parent $this->_nodes[$result->id]->setParent($this->_nodes[$result->parent_id]); } if (!isset($this->_nodes[$result->parent_id])) { unset($this->_nodes[$result->id]); continue; } if ($result->id == $id || $childrenLoaded) { $this->_nodes[$result->id]->setAllLoaded(); $childrenLoaded = true; } } } } } else { $this->_nodes[$id] = null; } }
/** * Apply currently set filters to the database query * @param JDatabaseQuery $query */ protected function applyFilters(JDatabaseQuery &$query) { if ($this->useEventsTable) { $query->from("events"); $query->leftJoin($this->table . " USING (" . $this->idField . ")"); } else { $query->from($this->table); } $query->where(array($this->startDateField . " >= '" . Event::timeToDate($this->startDate) . "'", $this->startDateField . " <= '" . Event::timeToDate($this->endDate) . "'")); if (!$this->showUnpublished) { $query->where($this->readyToPublishField); } // Filter by attendees if (!empty($this->filterAttendedBy)) { $query->join("INNER", "eventattendance ON eventattendance.eventtype=" . $this->eventTypeConst . " AND eventattendance.eventid=" . $this->table . "." . $this->idField); $query->where("eventattendance.user IN (" . implode(",", $this->filterAttendedBy) . ")"); } // This allows subclasses to modify the query. Normally they'll add extra WHERE clauses. $this->modifyQuery($query); }