PHP JDatabaseQuery::innerJoin方法代码示例

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

 /**
  * Prepare some main filters.
  *
  * @param JDatabaseQuery $query
  */
 protected function prepareFilters(&$query)
 {
     $db = JFactory::getDbo();
     // Filter by featured state.
     $value = $this->getState($this->context . '.filter_featured');
     if (null !== $value) {
         if (!$value) {
             $query->where('a.featured = 0');
         } else {
             $query->where('a.featured = 1');
         }
     }
     // Filter by category ID
     $value = (int) $this->getState($this->context . '.category_id', 0);
     if ($value > 0) {
         $query->where('a.catid = ' . $value);
     }
     // Filter by project type
     $value = (int) $this->getState($this->context . '.filter_projecttype', 0);
     if ($value > 0) {
         $query->where('a.type_id = ' . $value);
     }
     // Filter by user.
     $value = (int) $this->getState($this->context . '.filter_user', 0);
     if ($value > 0) {
         $query->where('a.user_id = ' . $value);
     }
     // Filter by country
     $value = $this->getState($this->context . '.filter_country');
     if (JString::strlen($value) > 0) {
         $query->innerJoin($db->quoteName('#__crowdf_locations', 'l') . ' ON a.location_id = l.id');
         $query->where('l.country_code = ' . $db->quote($value));
     }
     // Filter by location
     $value = (int) $this->getState($this->context . '.filter_location');
     if ($value > 0) {
         $query->where('a.location_id = ' . (int) $value);
     }
     // Filter by funding type
     $value = JString::strtoupper(JString::trim($this->getState($this->context . '.filter_fundingtype')));
     if (JString::strlen($value) > 0) {
         $allowedFundingTypes = array('FIXED', 'FLEXIBLE');
         if (in_array($value, $allowedFundingTypes, true)) {
             $query->where('a.funding_type = ' . $db->quote($value));
         }
     }
     // Filter by phrase
     $value = $this->getState($this->context . '.filter_phrase');
     if (JString::strlen($value) > 0) {
         $escaped = $db->escape($value, true);
         $quoted = $db->quote('%' . $escaped . '%', false);
         $query->where('a.title LIKE ' . $quoted);
     }
 }

 /**
  * Prepare some main filters.
  *
  * @param JDatabaseQuery $query
  */
 protected function prepareFilters(&$query)
 {
     $db = JFactory::getDbo();
     // Filter by featured state.
     $featured = $this->getState($this->context . ".filter_featured");
     if (!is_null($featured)) {
         if (!$featured) {
             $query->where('a.featured = 0');
         } else {
             $query->where('a.featured = 1');
         }
     }
     // Filter by category ID
     $categoryId = $this->getState($this->context . ".category_id", 0);
     if (!empty($categoryId)) {
         $query->where('a.catid = ' . (int) $categoryId);
     }
     // Filter by project type
     $projectTypeId = $this->getState($this->context . ".filter_projecttype", 0);
     if (!empty($projectTypeId)) {
         $query->where('a.type_id = ' . (int) $projectTypeId);
     }
     // Filter by country
     $countryCode = $this->getState($this->context . ".filter_country");
     if (!empty($countryCode)) {
         $query->innerJoin($db->quoteName("#__crowdf_locations", "l") . " ON a.location_id = l.id");
         $query->where('l.country_code = ' . $db->quote($countryCode));
     }
     // Filter by location
     $locationId = $this->getState($this->context . ".filter_location");
     if (!empty($locationId)) {
         $query->where('a.location_id = ' . (int) $locationId);
     }
     // Filter by funding type
     $filterFundingType = Joomla\String\String::strtoupper(Joomla\String\String::trim($this->getState($this->context . ".filter_fundingtype")));
     if (!empty($filterFundingType)) {
         $allowedFundingTypes = array("FIXED", "FLEXIBLE");
         if (in_array($filterFundingType, $allowedFundingTypes)) {
             $query->where('a.funding_type = ' . $db->quote($filterFundingType));
         }
     }
     // Filter by phrase
     $phrase = $this->getState($this->context . ".filter_phrase");
     if (!empty($phrase)) {
         $escaped = $db->escape($phrase, true);
         $quoted = $db->quote("%" . $escaped . "%", false);
         $query->where('a.title LIKE ' . $quoted);
     }
 }