PHP JDatabaseQuery::where方法代码示例

 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) . "'");
     }
 }

	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;
 }

 /**
  * 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();
 }