PHP DbCommand::join方法代码示例

 public function modifyElementsQuery(DbCommand $query, $params = array())
 {
     // Join with plugin table
     $query->join(AmMaps_GeoMapperRecord::TableName, 'elements.id=' . craft()->db->tablePrefix . AmMaps_GeoMapperRecord::TableName . '.elementId');
     // Prepare where statement
     $this->_searchParams($query, $params);
 }

 /**
  * Modify Query
  *
  * @param DbCommand $query
  * @param array $params
  */
 public function modifyQuery(DbCommand &$query, $params = array())
 {
     $query->join(SimpleMap_MapRecord::TABLE_NAME, 'elements.id=' . craft()->db->tablePrefix . SimpleMap_MapRecord::TABLE_NAME . '.ownerId');
     if (array_key_exists('location', $params)) {
         $this->_searchLocation($query, $params);
     }
 }

    public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
    {
        $query->addSelect('
				neoblocks.fieldId,
				neoblocks.ownerId,
				neoblocks.ownerLocale,
				neoblocks.typeId,
				neoblocks.collapsed
			')->join('neoblocks neoblocks', 'neoblocks.id = elements.id')->leftJoin('neoblockstructures neoblockstructures', ['and', 'neoblockstructures.ownerId = neoblocks.ownerId', 'neoblockstructures.fieldId = neoblocks.fieldId', ['or', 'neoblockstructures.ownerLocale = neoblocks.ownerLocale', ['and', 'neoblockstructures.ownerLocale is null', 'neoblocks.ownerLocale is null']]])->leftJoin('structureelements structureelements', ['and', 'structureelements.structureId = neoblockstructures.structureId', 'structureelements.elementId = neoblocks.id']);
        if ($criteria->fieldId) {
            $query->andWhere(DbHelper::parseParam('neoblocks.fieldId', $criteria->fieldId, $query->params));
        }
        if ($criteria->ownerId) {
            $query->andWhere(DbHelper::parseParam('neoblocks.ownerId', $criteria->ownerId, $query->params));
        }
        if ($criteria->ownerLocale) {
            $query->andWhere(DbHelper::parseParam('neoblocks.ownerLocale', $criteria->ownerLocale, $query->params));
        }
        if ($criteria->typeId) {
            $query->andWhere(DbHelper::parseParam('neoblocks.typeId', $criteria->typeId, $query->params));
        } else {
            if ($criteria->type) {
                $query->join('neoblocktypes neoblocktypes', 'neoblocktypes.id = neoblocks.typeId');
                $query->andWhere(DbHelper::parseParam('neoblocktypes.handle', $criteria->type, $query->params));
            }
        }
    }

 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('submissions.formId')->join('formerly_submissions submissions', 'submissions.id = elements.id');
     if ($criteria->formId) {
         $query->andWhere(DbHelper::parseParam('submissions.formId', $criteria->formId, $query->params));
     }
     if ($criteria->form) {
         $query->join('formerly_forms forms', 'forms.id = submissions.formId');
         $query->andWhere(DbHelper::parseParam('formerly_forms.handle', $criteria->form, $query->params));
     }
 }

 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('supertableblocks.fieldId, supertableblocks.ownerId, supertableblocks.ownerLocale, supertableblocks.typeId, supertableblocks.sortOrder')->join('supertableblocks supertableblocks', 'supertableblocks.id = elements.id');
     if ($criteria->fieldId) {
         $query->andWhere(DbHelper::parseParam('supertableblocks.fieldId', $criteria->fieldId, $query->params));
     }
     if ($criteria->ownerId) {
         $query->andWhere(DbHelper::parseParam('supertableblocks.ownerId', $criteria->ownerId, $query->params));
     }
     if ($criteria->ownerLocale) {
         $query->andWhere(DbHelper::parseParam('supertableblocks.ownerLocale', $criteria->ownerLocale, $query->params));
     }
     if ($criteria->type) {
         $query->join('supertableblocktypes supertableblocktypes', 'supertableblocktypes.id = supertableblocks.typeId');
         $query->andWhere(DbHelper::parseParam('supertableblocktypes.handle', $criteria->type, $query->params));
     }
 }

 /**
  * @inheritDoc IElementType::modifyElementsQuery()
  *
  * @param DbCommand            $query
  * @param ElementCriteriaModel $criteria
  *
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('assetfiles.sourceId, assetfiles.folderId, assetfiles.filename, assetfiles.kind, assetfiles.width, assetfiles.height, assetfiles.size, assetfiles.dateModified')->join('assetfiles assetfiles', 'assetfiles.id = elements.id');
     if (!empty($criteria->source)) {
         $query->join('assetsources assetsources', 'assetfiles.sourceId = assetsources.id');
     }
     if ($criteria->sourceId) {
         $query->andWhere(DbHelper::parseParam('assetfiles.sourceId', $criteria->sourceId, $query->params));
     }
     if ($criteria->source) {
         $query->andWhere(DbHelper::parseParam('assetsources.handle', $criteria->source, $query->params));
     }
     if ($criteria->folderId) {
         if ($criteria->includeSubfolders) {
             $folders = craft()->assets->getAllDescendantFolders(craft()->assets->getFolderById($criteria->folderId));
             $query->andWhere(DbHelper::parseParam('assetfiles.folderId', array_keys($folders), $query->params));
         } else {
             $query->andWhere(DbHelper::parseParam('assetfiles.folderId', $criteria->folderId, $query->params));
         }
     }
     if ($criteria->filename) {
         $query->andWhere(DbHelper::parseParam('assetfiles.filename', $criteria->filename, $query->params));
     }
     if ($criteria->kind) {
         if (is_array($criteria->kind)) {
             $query->andWhere(DbHelper::parseParam('assetfiles.kind', array_merge(array('or'), $criteria->kind), $query->params));
         } else {
             $query->andWhere(DbHelper::parseParam('assetfiles.kind', $criteria->kind, $query->params));
         }
     }
     if ($criteria->width) {
         $query->andWhere(DbHelper::parseParam('assetfiles.width', $criteria->width, $query->params));
     }
     if ($criteria->height) {
         $query->andWhere(DbHelper::parseParam('assetfiles.height', $criteria->height, $query->params));
     }
     if ($criteria->size) {
         $query->andWhere(DbHelper::parseParam('assetfiles.size', $criteria->size, $query->params));
     }
 }

 public function modifyElementsQuery(DbCommand $query, $params = array())
 {
     if (is_null($params)) {
         return null;
     }
     $defaultParams = array('distanceColumn' => 'distance', 'distanceOperator' => '<=', 'unit' => 'miles');
     $this->queryParams = array_merge($defaultParams, $params);
     $handle = $this->model->handle;
     if (isset($this->queryParams['address'])) {
         $response = craft()->googleMaps_geocoder->geocode($this->queryParams['address']);
         if ($response->status != 'OK') {
             return null;
         }
         $this->queryParams['lat'] = $lat = $response->results[0]->geometry->location->lat;
         $this->queryParams['lng'] = $lng = $response->results[0]->geometry->location->lng;
     } elseif (isset($this->queryParams['lat']) && isset($this->queryParams['lng'])) {
         $lat = $this->queryParams['lat'];
         $lng = $this->queryParams['lng'];
     }
     if (isset($lat) && isset($lng)) {
         $query->addSelect($this->queryParams['distanceColumn']);
         $query->join('(SELECT *, ROUND((((ACOS(SIN(' . $lat . ' * PI() / 180) * SIN(' . craft()->db->tablePrefix . 'googlemaps_locations.lat * PI() / 180) + COS(' . $lat . ' * PI() / 180) * COS(' . craft()->db->tablePrefix . 'googlemaps_locations.lat * PI() / 180) * COS((' . $lng . ' - ' . craft()->db->tablePrefix . 'googlemaps_locations.lng) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) * ' . craft()->googleMaps->getUnitMultiplier($this->queryParams['unit']) . '), 1) AS ' . $this->queryParams['distanceColumn'] . ' FROM ' . craft()->db->tablePrefix . 'googlemaps_locations ' . (isset($this->queryParams['distance']) ? 'HAVING ' . $this->queryParams['distanceColumn'] . ' ' . $this->queryParams['distanceOperator'] . ' ' . $this->queryParams['distance'] . ' OR ' . $this->queryParams['distanceColumn'] . ' IS NULL' : '') . ' ORDER BY ' . $this->queryParams['distanceColumn'] . ' ASC) googlemaps_locations', 'elements.id=googlemaps_locations.elementId');
     }
 }

 /**
  * Modifies an element query targeting elements of this type.
  *
  * @param DbCommand $query
  * @param ElementCriteriaModel $criteria
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('events.calendarId, events.startDate, events.endDate')->join('events events', 'events.id = elements.id');
     if ($criteria->calendarId) {
         $query->andWhere(DbHelper::parseParam('events.calendarId', $criteria->calendarId, $query->params));
     }
     if ($criteria->calendar) {
         $query->join('events_calendars events_calendars', 'events_calendars.id = events.calendarId');
         $query->andWhere(DbHelper::parseParam('events_calendars.handle', $criteria->calendar, $query->params));
     }
     if ($criteria->startDate) {
         $query->andWhere(DbHelper::parseDateParam('events.startDate', $criteria->startDate, $query->params));
     }
     if ($criteria->endDate) {
         $query->andWhere(DbHelper::parseDateParam('events.endDate', $criteria->endDate, $query->params));
     }
 }

 /**
  * Modifies an element query targeting elements of this type.
  *
  * @param DbCommand $query
  * @param ElementCriteriaModel $criteria
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('forms.id,
                        forms.fieldLayoutId,
                        forms.redirectEntryId,
                        forms.name,
                        forms.handle,
                        forms.titleFormat,
                        forms.submitAction,
                        forms.submitButton,
                        forms.afterSubmit,
                        forms.afterSubmitText,
                        forms.submissionEnabled,
                        forms.displayTabTitles,
                        forms.redirectUrl,
                        forms.sendCopy,
                        forms.sendCopyTo,
                        forms.notificationEnabled,
                        forms.notificationFilesEnabled,
                        forms.notificationRecipients,
                        forms.notificationSubject,
                        forms.confirmationSubject,
                        forms.notificationSenderName,
                        forms.confirmationSenderName,
                        forms.notificationSenderEmail,
                        forms.confirmationSenderEmail,
                        forms.notificationReplyToEmail,
                        forms.formTemplate,
                        forms.tabTemplate,
                        forms.fieldTemplate,
                        forms.notificationTemplate,
                        forms.confirmationTemplate');
     $query->join('amforms_forms forms', 'forms.id = elements.id');
     if ($criteria->handle) {
         $query->andWhere(DbHelper::parseParam('forms.handle', $criteria->handle, $query->params));
     }
 }

 /**
  * Adds conditions to related elements query.
  *
  * @access private
  * @param DbCommand $query
  * @param string    $source
  * @param string    $target
  * @param int       $sourceId
  * @param int|null  $fieldId
  * @param bool      $ordered
  */
 private function _addConditionsToRelationsQuery(DbCommand $query, $source, $target, $sourceId, $fieldId)
 {
     $query->join('relations relations', "relations.{$target}Id = elements.id")->andWhere(array("relations.{$source}Id" => $sourceId));
     if ($fieldId) {
         $query->andWhere(array('relations.fieldId' => $fieldId));
     }
 }

 /**
  * Modifies an entries query targeting entries of this type.
  *
  * @param DbCommand $query
  * @param ElementCriteriaModel $criteria
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('users.username, users.photo, users.firstName, users.lastName, users.email, users.admin, users.status, users.lastLoginDate, users.lockoutDate, users.preferredLocale')->join('users users', 'users.id = elements.id');
     if ($criteria->groupId) {
         $query->join('usergroups_users usergroups_users', 'users.id = usergroups_users.userId');
         $query->andWhere(DbHelper::parseParam('usergroups_users.groupId', $criteria->groupId, $query->params));
     }
     if ($criteria->group) {
         $query->join('usergroups_users usergroups_users', 'users.id = usergroups_users.userId');
         $query->join('usergroups usergroups', 'usergroups_users.groupId = usergroups.id');
         $query->andWhere(DbHelper::parseParam('usergroups.handle', $criteria->group, $query->params));
     }
     if ($criteria->username) {
         $query->andWhere(DbHelper::parseParam('users.username', $criteria->username, $query->params));
     }
     if ($criteria->firstName) {
         $query->andWhere(DbHelper::parseParam('users.firstName', $criteria->firstName, $query->params));
     }
     if ($criteria->lastName) {
         $query->andWhere(DbHelper::parseParam('users.lastName', $criteria->lastName, $query->params));
     }
     if ($criteria->email) {
         $query->andWhere(DbHelper::parseParam('users.email', $criteria->email, $query->params));
     }
     if ($criteria->preferredLocale) {
         $query->andWhere(DbHelper::parseParam('users.preferredLocale', $criteria->preferredLocale, $query->params));
     }
     if ($criteria->status) {
         $query->andWhere(DbHelper::parseParam('users.status', $criteria->status, $query->params));
     }
 }

 public function modifyQuery(DbCommand $query, $params = array())
 {
     $query->join(PlainMeta_PlainMetaRecord::TABLE_NAME, 'elements.id=' . craft()->db->tablePrefix . PlainMeta_PlainMetaRecord::TABLE_NAME . '.elementId');
 }

 /**
  * Modifies an element query targeting elements of this type.
  *
  * @param DbCommand            $query
  * @param ElementCriteriaModel $criteria
  *
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('submissions.id,
                        submissions.ipAddress,
                        submissions.userAgent,
                        submissions.submittedFrom,
                        submissions.dateCreated,
                        submissions.dateUpdated,
                        submissions.uid,
                        forms.id as formId,
                        forms.name as formName');
     $query->join('amforms_submissions submissions', 'submissions.id = elements.id');
     $query->join('amforms_forms forms', 'forms.id = submissions.formId');
     if ($criteria->id) {
         $query->andWhere(DbHelper::parseParam('submissions.id', $criteria->id, $query->params));
     }
     if ($criteria->formId) {
         $query->andWhere(DbHelper::parseParam('submissions.formId', $criteria->formId, $query->params));
     }
     if ($criteria->formHandle) {
         $query->andWhere(DbHelper::parseParam('forms.handle', $criteria->formHandle, $query->params));
     }
     if ($criteria->order) {
         // Trying to order by date creates ambiguity errors
         // Let's make sure mysql knows what we want to sort by
         if (stripos($criteria->order, 'elements.') === false && stripos($criteria->order, 'submissions.dateCreated') === false) {
             $criteria->order = str_replace('dateCreated', 'submissions.dateCreated', $criteria->order);
             $criteria->order = str_replace('dateUpdated', 'submissions.dateUpdated', $criteria->order);
         }
         // If we are sorting by title and do not have a source
         // We won't be able to sort, so bail on it
         if (stripos($criteria->order, 'title') !== false && !$criteria->formId) {
             $criteria->order = null;
         }
     }
 }

 /**
  * @inheritDoc IElementType::modifyElementsQuery()
  *
  * @param DbCommand            $query
  * @param ElementCriteriaModel $criteria
  *
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('tags.groupId')->join('tags tags', 'tags.id = elements.id');
     // Still support the deprecated params
     if ($criteria->name) {
         $query->andWhere(DbHelper::parseParam('content.title', $criteria->name, $query->params));
     }
     if ($criteria->setId && !$criteria->groupId) {
         craft()->deprecator->log('TagElementType::modifyElementsQuery():setId_param', 'The ‘setId’ tag param has been deprecated. Use ‘groupId’ instead.');
         $criteria->groupId = $criteria->setId;
         $criteria->setId = null;
     }
     if ($criteria->set && !$criteria->group) {
         craft()->deprecator->log('TagElementType::modifyElementsQuery():set_param', 'The ‘set’ tag param has been deprecated. Use ‘group’ instead.');
         $criteria->group = $criteria->set;
         $criteria->set = null;
     }
     if ($criteria->groupId) {
         $query->andWhere(DbHelper::parseParam('tags.groupId', $criteria->groupId, $query->params));
     }
     if ($criteria->group) {
         $query->join('taggroups taggroups', 'taggroups.id = tags.groupId');
         $query->andWhere(DbHelper::parseParam('taggroups.handle', $criteria->group, $query->params));
     }
     // Backwards compatibility with order=name (tags had names before 2.3)
     if (is_string($criteria->order)) {
         $criteria->order = preg_replace('/\\bname\\b/', 'title', $criteria->order);
     }
 }

 /**
  * Modifies an element query targeting elements of this type.
  *
  * @param DbCommand            $query
  * @param ElementCriteriaModel $criteria
  *
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('pushnotifications_notifications.appId, pushnotifications_notifications.title, pushnotifications_notifications.body, pushnotifications_notifications.command, pushnotifications_notifications.schedule')->join('pushnotifications_notifications pushnotifications_notifications', 'pushnotifications_notifications.id = elements.id');
     if ($criteria->appId) {
         $query->andWhere(DbHelper::parseParam('pushnotifications_notifications.appId', $criteria->appId, $query->params));
     }
     if ($criteria->app) {
         $query->join('pushnotifications_apps pushnotifications_apps', 'pushnotifications_apps.id = pushnotifications_notifications.appId');
         $query->andWhere(DbHelper::parseParam('pushnotifications_apps.handle', $criteria->app, $query->params));
     }
     if ($criteria->title) {
         $query->andWhere(DbHelper::parseParam('pushnotifications_notifications.title', $criteria->title, $query->params));
     }
     if ($criteria->body) {
         $query->andWhere(DbHelper::parseParam('pushnotifications_notifications.body', $criteria->body, $query->params));
     }
     if ($criteria->command) {
         $query->andWhere(DbHelper::parseParam('pushnotifications_notifications.command', $criteria->command, $query->params));
     }
     if ($criteria->schedule) {
         $query->andWhere(DbHelper::parseDateParam('pushnotifications_notifications.schedule', $criteria->schedule, $query->params));
     }
 }