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) . "'"); } }
/** * Implements custom filtering * * @param \JDatabaseQuery $query The model query we're operating on * @param bool $overrideLimits Are we told to override limits? * * @return void */ protected function onBeforeBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false) { $db = $this->getDbo(); $fltItemText = $this->getState('itemtext', null, 'string'); $fltUserText = $this->getState('usertext', null, 'string'); $fltCategory = $this->getState('category', null, 'int'); $fltVersion = $this->getState('version', null, 'int'); if ($fltItemText) { // This extra query approach is required for performance on very large log tables (multiple millions of rows) $itemIDs = $this->getItems($fltItemText); if (empty($itemIDs)) { $query->where('FALSE'); } else { $itemIDs = array_map(array($db, 'quote'), $itemIDs); $ids = implode(',', $itemIDs); $query->where($db->qn('item_id') . ' IN(' . $ids . ')'); } } if ($fltUserText) { // This extra query approach is required for performance on very large log tables (multiple millions of rows) $userIDs = $this->getUsers($fltUserText); if (empty($userIDs)) { $query->where('FALSE'); } else { $userIDs = array_map(array($db, 'quote'), $userIDs); $ids = implode(',', $userIDs); $query->where($db->qn('user_id') . ' IN(' . $ids . ')'); } } if ($fltCategory) { // We use the double nested subquery instead of whereHas() for memory conservation reasons at the expense of // performance. $query_inner = $db->getQuery(true)->select($db->qn('id'))->from($db->qn('#__ars_releases'))->where($db->qn('category_id') . ' = ' . $db->q($fltCategory)); $query_outer = $db->getQuery(true)->select($db->qn('id'))->from($db->qn('#__ars_items'))->where($db->qn('release_id') . ' IN (' . $query_inner . ')'); $db->setQuery($query_outer); $ids = $db->loadColumn(); $clause = '(' . implode(", ", $ids) . ')'; $query->where($db->qn('item_id') . ' IN ' . $clause); } if ($fltVersion) { // We use the nested subquery instead of whereHas() for memory conservation reasons at the expense of // performance. $query_outer = $db->getQuery(true)->select($db->qn('id'))->from($db->qn('#__ars_items'))->where($db->qn('release_id') . ' = ' . $db->q($fltVersion)); $db->setQuery($query_outer); $ids = $db->loadColumn(); $clause = '(' . implode(", ", $ids) . ')'; $query->where($db->qn('item_id') . ' IN ' . $clause); } }
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; }
/** * 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; }
/** * Filter by field. * * @param string $field Field name. * @param string $operation Operation (>|>=|<|<=|=|IN|NOT IN) * @param string|array $value Value. * * @return $this */ public function where($field, $operation, $value) { $operation = strtoupper($operation); switch ($operation) { case '>': case '>=': case '<': case '<=': case '=': $this->query->where("{$this->db->quoteName($field)} {$operation} {$this->db->quote($value)}"); break; case 'BETWEEN': list($a, $b) = (array) $value; $this->query->where("{$this->db->quoteName($field)} BETWEEN {$this->db->quote($a)} AND {$this->db->quote($b)}"); break; case 'IN': case 'NOT IN': $value = (array) $value; if (empty($value)) { // WHERE field IN (nothing). $this->query->where('0'); } else { $list = implode(',', $value); $this->query->where("{$this->db->quoteName($field)} {$operation} ({$list})"); } break; } return $this; }
/** * Implements custom filtering * * @param \JDatabaseQuery $query The model query we're operating on * @param bool $overrideLimits Are we told to override limits? * * @return void */ protected function onBeforeBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false) { $db = $this->getDbo(); $fltUsername = $this->getState('username', null, 'string'); if ($fltUsername) { $fltUsername = '******' . $fltUsername . '%'; $q = $db->getQuery(true)->select(array($db->qn('id')))->from($db->qn('#__users'))->where($db->qn('username') . ' LIKE ' . $db->q($fltUsername), 'OR')->where($db->qn('name') . ' LIKE ' . $db->q($fltUsername))->where($db->qn('email') . ' LIKE ' . $db->q($fltUsername)); $db->setQuery($q); $ids = $db->loadColumn(); if (!empty($ids)) { $ids = array_map(array($db, 'quote'), $ids); $query->where($db->qn('user_id') . 'IN (' . implode(',', $ids) . ')'); } else { $query->where($db->qn('user_id') . '=' . $db->q(0)); } } }
/** * Implements custom filtering * * @param \JDatabaseQuery $query The model query we're operating on * @param bool $overrideLimits Are we told to override limits? * * @return void */ protected function onBeforeBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false) { $db = $this->getDbo(); $fltSearch = $this->getState('search', null, 'string'); if ($fltSearch) { $fltSearch = "%{$fltSearch}%"; $query->where($db->qn('title') . ' LIKE ' . $db->q($fltSearch)); } }
/** * Tests the JDatabaseQuery::andWhere method. * * @return void * * @since 3.6 */ public function testAndWhere() { $this->assertThat($this->_instance->where('foo = 1')->andWhere('bar = 2'), $this->identicalTo($this->_instance), 'Tests chaining.'); $this->assertThat(trim(TestReflection::getValue($this->_instance, 'where')), $this->equalTo('WHERE ' . PHP_EOL . '(foo = 1) AND ' . PHP_EOL . '(bar = 2)'), 'Tests rendered value.'); // Add another set of where conditions. $this->_instance->andWhere(array('baz = 3', 'goo = 4')); $this->assertThat(trim(TestReflection::getValue($this->_instance, 'where')), $this->equalTo('WHERE ' . PHP_EOL . '(' . PHP_EOL . '(foo = 1) AND ' . PHP_EOL . '(bar = 2)) AND ' . PHP_EOL . '(baz = 3 OR goo = 4)'), 'Tests rendered value after second use and array input.'); // Add another set of where conditions with some different glue. $this->_instance->andWhere(array('faz = 5', 'gaz = 6'), 'XOR'); $this->assertThat(trim(TestReflection::getValue($this->_instance, 'where')), $this->equalTo('WHERE ' . PHP_EOL . '(' . PHP_EOL . '(' . PHP_EOL . '(foo = 1) AND ' . PHP_EOL . '(bar = 2)) AND ' . PHP_EOL . '(baz = 3 OR goo = 4)) AND ' . PHP_EOL . '(faz = 5 XOR gaz = 6)'), 'Tests rendered value after third use, array input and different glue.'); }
protected function modifyQuery(JDatabaseQuery &$query) { $showWhat = array(); if ($this->getNormal) { $showWhat[] = "shownormal"; } if ($this->getNewMember) { $showWhat[] = "shownewmember"; } $query->where("(" . implode(" OR ", $showWhat) . ")"); }
/** * 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->where(" c." . $this->filterField . " = " . $db->escape($this->filter_value, true)); } return $query; }
/** * Prepare some main filters. * * @param \JDatabaseQuery $query * @param array $options */ protected function prepareFilters(&$query, $options) { // Filter by state. if (isset($options["state"])) { $published = (int) $options["state"]; if (!$published) { $query->where("a.published = 0"); } else { $query->where("a.published = 1"); } } // Filter by approval state. if (isset($options["approved"])) { $approved = (int) $options["approved"]; if (!$approved) { $query->where("a.approved = 0"); } else { $query->where("a.approved = 1"); } } }
/** * Prepare some main filters. * * @param \JDatabaseQuery $query * @param array $options */ protected function prepareFilters(&$query, $options) { // Filter by state. if (array_key_exists('state', $options)) { $published = (int) $options['state']; if (!$published) { $query->where('a.published = 0'); } else { $query->where('a.published = 1'); } } // Filter by approval state. if (array_key_exists('approved', $options)) { $approved = (int) $options['approved']; if (!$approved) { $query->where('a.approved = 0'); } else { $query->where('a.approved = 1'); } } }
/** * Tests the JDatabaseQuery::where method. * * @return void * * @since 11.3 */ public function testWhere() { $this->assertThat($this->_instance->where('foo = 1'), $this->identicalTo($this->_instance), 'Tests chaining.'); $this->assertThat(trim(TestReflection::getValue($this->_instance, 'where')), $this->equalTo('WHERE foo = 1'), 'Tests rendered value.'); // Add another column. $this->_instance->where(array('bar = 2', 'goo = 3')); $this->assertThat(trim(TestReflection::getValue($this->_instance, 'where')), $this->equalTo('WHERE foo = 1 AND bar = 2 AND goo = 3'), 'Tests rendered value after second use and array input.'); // Clear the where TestReflection::setValue($this->_instance, 'where', null); $this->_instance->where(array('bar = 2', 'goo = 3'), 'OR'); $this->assertThat(trim(TestReflection::getValue($this->_instance, 'where')), $this->equalTo('WHERE bar = 2 OR goo = 3'), 'Tests rendered value with glue.'); }
/** * @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); } }
/** * @param array $filters * @param array $sort_filters */ public function process(array $filters, array $sort_filters = array(), $showUnpublished = false) { $this->showUnpublished = $showUnpublished; $this->setAccessWhere(); 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->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'); } 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; }
protected function modifyQuery(JDatabaseQuery &$query) { $showWhat = array(); if ($this->getWithWalks) { $showWhat[] = "showWithWalks"; } if ($this->getWithSocials) { $showWhat[] = "showWithSocials"; } if ($this->getWithWeekends) { $showWhat[] = "showWithWeekends"; } $query->where("(" . implode(" OR ", $showWhat) . ")"); }
/** * Reschedule the plugin for next run, and republish * * * @return void */ protected function reschedule() { $now = JFactory::getDate(); $now = $now->toUnix(); $new = JFactory::getDate($this->row->nextrun); $tmp = $new->toUnix(); switch ($this->row->unit) { case 'second': $inc = 1; break; case 'minute': $inc = 60; break; case 'hour': $inc = 60 * 60; break; default: case 'day': $inc = 60 * 60 * 24; break; } while ($tmp + $inc * $this->row->frequency < $now) { $tmp = $tmp + $inc * $this->row->frequency; } // Mark them as being run $nextRun = JFactory::getDate($tmp); $this->query->clear(); $this->query->update('#__{package}_cron'); $this->query->set('lastrun = ' . $this->db->quote($nextRun->toSql())); if ($this->pluginModel->shouldReschedule() && $this->pluginModel->doRunGating()) { $this->query->set("published = '1'"); } if (!$this->pluginModel->shouldReschedule()) { $this->query->set("published = '0'"); $this->log->message .= "\nPlugin has unpublished itself"; } $this->query->where('id = ' . $this->row->id); $this->db->setQuery($this->query); $this->db->execute(); }
/** * Modify the query to filter list objects by n:n relation. * * @param F0FModel $model The model on which operate. * @param JDatabaseQuery $query The query to alter. */ public function onAfterBuildQuery(&$model, &$query) { $input = new F0FInput(); $db = $model->getDbo(); // Retrieve the relations configuration for this table $table = $model->getTable(); $key = $table->getConfigProviderKey() . '.relations'; $relations = $table->getConfigProvider()->get($key, array()); // For each multiple type relation add the filter query foreach ($relations as $relation) { if ($relation['type'] == 'multiple') { // Get complete relation fields $relation = array_merge(array('itemName' => $relation['itemName']), $table->getRelations()->getRelation($relation['itemName'], $relation['type'])); // Model only save $table->getKnownFields as state, so we look into the input $filter_name = $relation['itemName']; $model_filter_value = $input->getInt($filter_name); // Build the conditions based on relation configuration if (!empty($model_filter_value)) { $query->innerJoin(sprintf('%1$s ON %1$s.%2$s = %3$s.%4$s', $db->qn($relation['pivotTable']), $db->qn($relation['ourPivotKey']), $db->qn($table->getTableName()), $db->qn($relation['localKey']))); $query->where(sprintf('%s.%s = %s', $db->qn($relation['pivotTable']), $db->qn($relation['theirPivotKey']), $model_filter_value)); } } } }
/** * Process the query filters. * * @param JDatabaseQuery $query The query object. * @param array $filters The filters values. * * @return JDatabaseQuery The db query object. */ protected function processFilters(\JDatabaseQuery $query, $filters = array()) { $user = $this->container->get('user'); $db = $this->container->get('db'); $date = $this->container->get('date'); // If no state filter, set published >= 0 if (!isset($filters['user.state']) && property_exists($this->getTable(), 'state')) { $query->where($query->quoteName('user.state') . ' >= 0'); } // Category // ===================================================================================== $category = $this->getCategory(); if ($category->id != 1 && in_array('category.lft', $this->filterFields) && in_array('category.rgt', $this->filterFields)) { $query->where($query->format('(%n >= %a AND %n <= %a)', 'category.lft', $category->lft, 'category.rgt', $category->rgt)); } // Max Level // ===================================================================================== $maxLevel = $this->state->get('filter.max_category_levels', -1); if ($maxLevel > 0) { $query->where($query->quoteName('category.level') . " <= " . $maxLevel); } // Edit Access // ===================================================================================== if ($this->state->get('filter.unpublished')) { $query->where('user.state >= 0'); } else { $query->where('user.state > 0'); $nullDate = $query->Quote($db->getNullDate()); $nowDate = $query->Quote($date->toSQL(true)); if (in_array('user.publish_up', $this->filterFields) && in_array('user.publish_down', $this->filterFields)) { $query->where('(user.publish_up = ' . $nullDate . ' OR user.publish_up <= ' . $nowDate . ')'); $query->where('(user.publish_down = ' . $nullDate . ' OR user.publish_down >= ' . $nowDate . ')'); } } // View Level // ===================================================================================== if ($access = $this->state->get('filter.access') && in_array('user.access', $this->filterFields)) { $query->where(new InCompare('user.access', $user->getAuthorisedViewLevels())); } // Language // ===================================================================================== if ($this->state->get('filter.language') && in_array('a.language', $this->filterFields)) { $lang_code = $db->quote(JFactory::getLanguage()->getTag()); $query->where("a.language IN ('{$lang_code}', '*')"); } return parent::processFilters($query, $filters); }
/** * 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; }
/** * Method to append the primary keys for this table to a query. * * @param JDatabaseQuery $query A query object to append. * @param mixed $pk Optional primary key parameter. * * @return void * * @since 12.3 */ public function appendPrimaryKeys($query, $pk = null) { if (is_null($pk)) { foreach ($this->_tbl_keys as $k) { $query->where($this->_db->quoteName($k) . ' = ' . $this->_db->quote($this->$k)); } } else { if (is_string($pk)) { $pk = array($this->_tbl_key => $pk); } $pk = (object) $pk; foreach ($this->_tbl_keys AS $k) { $query->where($this->_db->quoteName($k) . ' = ' . $this->_db->quote($pk->$k)); } } }
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; }
/** * Create the where part for the query that selects the list options * * @param array $data Current row data to use in placeholder replacements * @param bool $incWhere Should the additional user defined WHERE statement be included * @param string $thisTableAlias Db table alias * @param array $opts Options * @param JDatabaseQuery|bool $query Append where to JDatabaseQuery object or return string (false) * * @return string|JDatabaseQuery */ protected function buildQueryWhere($data = array(), $incWhere = true, $thisTableAlias = null, $opts = array(), $query = false) { $params = $this->getParams(); $db = $this->getDb(); $field = $params->get('notes_where_element'); $value = $params->get('notes_where_value'); $fk = $params->get('join_fk_column', ''); $rowId = $this->getFormModel()->getRowId(); $primaryKey = $this->getGroupModel()->isJoin() ? $this->getJoinedGroupPkVal($repeatCounter) : $rowId; $where = array(); // Jaanus: here we can choose whether WHERE has to have single or (if field is the same as FK then only) custom (single or multiple) criteria, if ($value != '') { if ($field != '' && $field !== $fk) { $where[] = $db->qn($field) . ' = ' . $db->q($value); } else { $where[] = $value; } } // Jaanus: when we choose WHERE field to be the same as FK then WHERE criteria is automatically FK = rowid, custom criteria(s) above may be added if ($fk !== '' && $field === $fk && $primaryKey != '') { $where[] = $db->qn($fk) . ' = ' . $primaryKey; } if ($this->loadRow != '') { $pk = $db->qn($this->getJoin()->table_join_alias . '.' . $params->get('join_key_column')); $where[] = $pk . ' = ' . $this->loadRow; } /** * $$$ hugh if where is still empty (most likely if new form) set it to "1 = -1", otherwise * we'll wind up selecting everything in the table. */ if ($query) { if (!empty($where)) { $query->where(implode(' OR ', $where)); } else { $query->where('1 = -1'); } return $query; } else { return empty($where) ? '1 = -1' : 'WHERE ' . implode(' OR ', $where); } }
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; }
/** * Implements custom filtering * * @param \JDatabaseQuery $query The model query we're operating on * @param bool $overrideLimits Are we told to override limits? * * @return void */ protected function onBeforeBuildQuery(\JDatabaseQuery &$query, $overrideLimits = false) { $db = $this->getDbo(); // Visual Groups filter $fltVgroup = $this->getState('vgroup', null, 'int'); if ($fltVgroup) { $query->where($db->qn('vgroup_id') . ' = ' . $db->q($fltVgroup)); } // Access by user filter (unless we are asked to display unauthorized links for this category) $fltAccessUser = $this->getState('access_user', null, 'int'); if (!is_null($fltAccessUser)) { $access_levels = $this->container->platform->getUser($fltAccessUser)->getAuthorisedViewLevels(); $access_levels = array_map(array($db, 'quote'), $access_levels); $query->where('(' . '(' . $db->qn('access') . ' IN (' . implode(',', $access_levels) . ')) OR (' . $db->qn('show_unauth_links') . ' = ' . $db->q(1) . '))'); } // No unpublished Bleeding Edge categories filter $fltNoBEUnpub = $this->getState('nobeunpub', null, 'int'); if ($fltNoBEUnpub) { $query->where('NOT(' . $db->qn('published') . ' = ' . $db->q('0') . ' AND ' . $db->qn('type') . '=' . $db->q('bleedingedge') . ')'); } // Language filter $fltLanguage = $this->getState('language', null, 'cmd'); $fltLanguage2 = $this->getState('language2', null, 'string'); if ($fltLanguage && $fltLanguage != '*') { $query->where($db->qn('language') . ' IN(' . $db->q('*') . ',' . $db->q($fltLanguage) . ')'); } elseif ($fltLanguage2) { $query->where($db->qn('language') . ' = ' . $db->q($fltLanguage2)); } // Generic search (matching title or description) filter $search = $this->getState('search', null); if ($search) { $search = '%' . $search . '%'; $query->where('(' . '(' . $db->qn('title') . ' LIKE ' . $db->quote($search) . ') OR' . '(' . $db->qn('description') . ' LIKE ' . $db->quote($search) . ')' . ')'); } // Order filtering $fltOrderBy = $this->getState('orderby_filter', null, 'cmd'); switch ($fltOrderBy) { case 'alpha': $this->setState('filter_order', 'title'); $this->setState('filter_order_Dir', 'ASC'); break; case 'ralpha': $this->setState('filter_order', 'title'); $this->setState('filter_order_Dir', 'DESC'); break; case 'created': $this->setState('filter_order', 'created'); $this->setState('filter_order_Dir', 'ASC'); break; case 'rcreated': $this->setState('filter_order', 'created'); $this->setState('filter_order_Dir', 'DESC'); break; case 'order': $this->setState('filter_order', 'ordering'); $this->setState('filter_order_Dir', 'ASC'); break; } }
/** * 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; }
/** * Get the part of the sql query that relates to the where statement * * @param bool $incFilters if true the SQL contains any filters * if false only contains prefilter sql * @param bool|JDatabaseQuery $query if false return the where as a string * if a db query object, set the where clause * Paul 2013-07-20 Add join parameter to limit where clause to main table if needed * @param bool $doJoins include where clauses for joins? * * @return mixed string if $query false, else JDatabaseQuery */ public function buildQueryWhere($incFilters = true, $query = false, $doJoins = true) { $pluginManager = FabrikWorker::getPluginManager(); $pluginManager->runPlugins('onBuildQueryWhere', $this, 'list'); $sig = !$query ? 'string' : 'query'; $sig .= (int) $incFilters; /** * $$ hugh - caching is borked when using query builder object, as we're always returning a query * string, never an object. So code like this: * * $query = $db->getQuery(true); * // some code that does query building stuff here, then ... * $query = $listModel->buildQueryWhere(true, $query); * $query = $listModel->buildQueryJoin($query); * * ... blows up in buildQueryJoin() if this function returns a cached query string, overwriting * the $query builder object. * * "Real" fix would be to sort it out so we return the object, although I'm not convinced that's * the even the right fix. But for now only use the cache if we're using string not object. */ if ($query === false && isset($this->_whereSQL[$sig])) { return $this->_whereSQL[$sig][$incFilters]; } $filters = $this->getFilterArray(); /* $$$ hugh - added option to 'require filtering', so if no filters specified * we return an empty table. Only do this where $inFilters is set, so we're only doing this * on the main row count and data fetch, and things like * filter dropdowns still get built. */ if ($incFilters && !$this->gotAllRequiredFilters()) { if (!$query) { return 'WHERE 1 = -1 '; } else { $query->where('1 = -1'); return $query; } } $groups = $this->getFormModel()->getGroupsHiarachy(); foreach ($groups as $groupModel) { if ($doJoins || !$doJoins && $groupModel->isJoin()) { $elementModels = $groupModel->getPublishedElements(); foreach ($elementModels as $elementModel) { $elementModel->appendTableWhere($this->pluginQueryWhere); } } } if (empty($filters)) { // $$$ hugh - testing hack for plugins to add WHERE clauses if (!empty($this->pluginQueryWhere)) { if (!$query) { return 'WHERE ' . implode(' AND ', $this->pluginQueryWhere); } else { $query->where(implode(' AND ', $this->pluginQueryWhere)); return $query; } } else { return $query ? $query : ''; } } $addWhere = $query == false ? true : false; list($sqlNoFilter, $sql) = $this->_filtersToSQL($filters, $addWhere); $this->_whereSQL[$sig] = array('0' => $sqlNoFilter, '1' => $sql); if (!$query) { return $this->_whereSQL[$sig][$incFilters]; } else { if (!empty($this->_whereSQL[$sig][$incFilters])) { $query->where($this->_whereSQL[$sig][$incFilters]); } return $query; } }
/** * Prepare filter by funding state. * * @param JDatabaseQuery $query */ protected function prepareFilterFundingState(&$query) { $db = JFactory::getDbo(); // Filter by funding state. $filter = (int) $this->getState($this->context . '.filter_funding_state'); switch ($filter) { case 1: // Successfully funded. jimport('joomla.date.date'); $date = new JDate(); $today = $date->toSql(); $query->where('a.funding_end < ' . $db->quote($today) . ' AND a.funded >= a.goal'); break; } }
/** * Prepare the state of the project in where clause of the query. * * @throws \InvalidArgumentException * * @param \JDatabaseQuery $query * @param array $options */ protected function prepareQueryStates(&$query, array $options = array()) { // Filter by state published. $value = ArrayHelper::getValue($options, 'published'); if ($value !== null and is_numeric($value)) { $query->where('a.published = ' . (int) $value); } // Filter by state approved. $value = ArrayHelper::getValue($options, 'approved'); if ($value !== null and is_numeric($value)) { $query->where('a.approved = ' . (int) $value); } }