/** * Adds Count Items for Category Manager. * * @param JDatabaseQuery $query The query object of com_categories * * @return JDatabaseQuery * * @since 3.4 */ public static function countItems($query) { // Join articles to categories and count published items $query->select('COUNT(DISTINCT cp.id) AS count_published'); $query->join('LEFT', '#__newsfeeds AS cp ON cp.catid = a.id AND cp.published = 1'); // Count unpublished items $query->select('COUNT(DISTINCT cu.id) AS count_unpublished'); $query->join('LEFT', '#__newsfeeds AS cu ON cu.catid = a.id AND cu.published = 0'); // Count archived items $query->select('COUNT(DISTINCT ca.id) AS count_archived'); $query->join('LEFT', '#__newsfeeds AS ca ON ca.catid = a.id AND ca.published = 2'); // Count trashed items $query->select('COUNT(DISTINCT ct.id) AS count_trashed'); $query->join('LEFT', '#__newsfeeds AS ct ON ct.catid = a.id AND ct.published = -2'); return $query; }
/** * Tests the JDatabaseQuery::join method. * * @return void * * @covers JDatabaseQuery::join * @since 11.3 */ public function testJoin() { $this->assertThat($this->_instance->join('INNER', 'foo ON foo.id = bar.id'), $this->identicalTo($this->_instance), 'Tests chaining.'); $this->assertThat(trim($this->_instance->join[0]), $this->equalTo('INNER JOIN foo ON foo.id = bar.id'), 'Tests that first join renders correctly.'); $this->_instance->join('OUTER', 'goo ON goo.id = car.id'); $this->assertThat(trim($this->_instance->join[1]), $this->equalTo('OUTER JOIN goo ON goo.id = car.id'), 'Tests that second join renders correctly.'); }
/** * @param array $filters * @param array $sort_filters */ public function process(array $filters, array $sort_filters = array(), $showUnpublished = false) { $this->showUnpublished = $showUnpublished; $this->setAccessWhere(); $this->setDisplayedWhere(); foreach ($filters as $filtertype => $fitlerdata) { if (!method_exists($this, $filtertype)) { //throw new RokSprocket_Exception(rc__('Unknown Filter %s', $filtertype)); } else { $this->{$filtertype}($fitlerdata); } } foreach ($sort_filters as $sort_filtertype => $sort_fitlerdata) { $sort_function = 'sort_' . $sort_filtertype; if (!method_exists($this, 'sort_' . $sort_filtertype)) { //throw new RokSprocket_Exception(rc__('Unknown Filter %s', $filtertype)); } else { $this->{$sort_function}($sort_fitlerdata); } } if (!empty($this->access_where)) { $access_where = sprintf('(%s)', implode(' AND ', $this->access_where)); $article_where_parts[] = $access_where; $filter_where_parts[] = $access_where; } if (!empty($this->displayed_where)) { $displayed_where = sprintf('(%s)', implode(' AND ', $this->displayed_where)); $article_where_parts[] = $displayed_where; $filter_where_parts[] = $displayed_where; } if (!empty($this->article_where)) { $article_where_parts[] = implode(' AND ', $this->article_where); $this->query->where(sprintf('(%s)', implode(' AND ', $article_where_parts)), 'OR'); } if (!empty($this->filter_where)) { $filter_where_parts[] = implode(' AND ', $this->filter_where); $this->query->where(sprintf('(%s)', implode(' AND ', $filter_where_parts)), 'OR'); } if (empty($this->article_where) && empty($this->filter_where)) { $this->query->where('0=1'); } if ($this->manualSort) { $this->query->join('LEFT OUTER', sprintf('(select rsi.provider_id, rsi.order from #__roksprocket_items as rsi where module_id = %d) rsi on a.id = rsi.provider_id', $this->moduleId)); array_unshift($this->sort_order, 'rsi.order'); if ($this->manualAppend == 'after') { array_unshift($this->sort_order, 'IF(ISNULL(rsi.order),1,0)'); } } foreach ($this->sort_order as $sort) { $this->query->order($sort); } }
/** * add filter to query * * @param JDatabaseQuery $query query object * * @return JDatabaseQuery */ public function addFilter(JDatabaseQuery $query) { if (!$this->filterField) { return $query; } $db = JFactory::getDbo(); //since joomla 3.0 filter_value can be '' too not only filterNullValue if (isset($this->filter_value) && strlen($this->filter_value) > 0 && $this->filter_value != $this->filterNullValue) { $query->join('INNER', '#__rwf_fields AS rwff ON rwff.id = c.field_id'); $query->where("rwff.form_id = " . $db->escape($this->filter_value, true)); } return $query; }
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; }
/** * 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; }
protected function modifyQuery(JDatabaseQuery &$query) { $query->where("NOT deleted"); if (isset($this->leader)) { if ($this->leader instanceof Leader) { $query->where("leaderid = " . $this->leader->id); } else { if (is_int($this->leader)) { $query->where("leaderid = " . $this->leader); } else { if (ctype_digit($this->leader)) { $query->where("leaderid = " . (int) $this->leader); } } } } if (isset($this->walkProgramme)) { if ($this->walkProgramme instanceof WalkProgramme) { $wpID = $this->walkProgramme->id; } else { $wpID = (int) $this->walkProgramme; } $query->join('INNER', 'walkprogrammewalklinks ON WalkProgrammeWalkID = walkprogrammewalks.SequenceID'); $query->where("walkprogrammewalklinks.ProgrammeID = " . $wpID); } if (isset($this->startTimeMax)) { $query->where("meettime <= '" . strftime("%H:%M", $this->startTimeMax) . "'"); } }
/** * Get the part of the sql query that creates the joins * used when building the table's data * * @param bool|JDatabaseQuery $query JQuery object or false * * @return JDatabaseQuery|string string or join query - join sql */ public function buildQueryJoin($query = false) { $db = FabrikWorker::getDbo(); $ref = $query ? '1' : '0'; if (isset($this->joinsSQL[$ref])) { return $this->joinsSQL[$ref]; } $statements = array(); $table = $this->getTable(); $selectedTables[] = $table->db_table_name; $return = array(); $joins = $this->get('includeCddInJoin', true) === false ? $this->getJoinsNoCdd() : $this->getJoins(); foreach ($joins as $join) { // Used to bypass user joins if the table connect isnt the Joomla connection if ((int) $join->canUse === 0) { continue; } if ($join->join_type == '') { $join->join_type = 'LEFT'; } $sql = JString::strtoupper($join->join_type) . ' JOIN ' . $db->qn($join->table_join); $k = FabrikString::safeColName($join->keytable . '.' . $join->table_key); // Check we only get the field name $join->table_join_key = explode('.', $join->table_join_key); $join->table_join_key = array_pop($join->table_join_key); if ($join->table_join_alias == '') { $on = FabrikString::safeColName($join->table_join . '.' . $join->table_join_key); $sql .= ' ON ' . $on . ' = ' . $k; } else { $on = FabrikString::safeColName($join->table_join_alias . '.' . $join->table_join_key); $sql .= ' AS ' . FabrikString::safeColName($join->table_join_alias) . ' ON ' . $on . ' = ' . $k . "\n"; } /* * @FIXME - need to work out where the COLLATE needs to go. Was at the end of builQuery, but that creates * an invalid query if we had any grouping. As it only applies to joined tables, tried putting it here, * but still get a query error. Needs to be fixed, but I have to commit to get some other changes done. */ /* $params = $this->getParams(); if ($params->get('force_collate', '') !== '') { $sql .= ' COLLATE ' . $params->get('force_collate', '') . ' '; } */ /* Try to order join statements to ensure that you are selecting from tables that have * already been included (either via a previous join statement or the table select statement) */ if (in_array($join->keytable, $selectedTables)) { $return[] = $sql; $selectedTables[] = $join->table_join; } else { // Didn't find anything so defer it till later /* $statements[$join->keytable] = $sql; * $$$rob - sometimes the keytable is the same for 2 deferred joins * in this case the first join is incorrectly overwritten in the $statements array * keying on join->id should solve this */ $statements[$join->id] = array($join->keytable, $sql); } // Go through the deferred join statements and see if their table has now been selected foreach ($statements as $joinId => $ar) { $t = $ar[0]; $s = $ar[1]; if (in_array($t, $selectedTables)) { if (!in_array($s, $return)) { // $$$rob test to avoid duplicate join queries $return[] = $s; unset($statements[$t]); } } } } // $$$rob test for bug #376 foreach ($statements as $joinId => $ar) { $s = $ar[1]; if (!in_array($s, $return)) { $return[] = $s; } } if ($query !== false) { foreach ($return as $r) { $words = explode(' ', trim($r)); $type = array_shift($words); $statement = str_replace('JOIN', '', implode(' ', $words)); $query->join($type, $statement); } return $query; } else { $return = implode(' ', $return); $this->joinsSQL[$ref] = $return; } return $query == false ? $return : $query; }
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; }
/** * 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; }
/** * 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); }