PHP DbCommand::andWhere方法代码示例

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

 /**
  * 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('venti.startDate, venti.endDate, venti.allDay, venti.isrepeat, venti.eid, venti.eventid, venti.repeat, venti.rRule, venti.summary, venti.locale, entries.postDate, entries.expiryDate')->leftJoin('venti_events venti', 'venti.eventid = elements.id')->leftJoin('entries entries', 'entries.id = eventid')->group('venti.startDate');
     if ($criteria->locale) {
         $query->andWhere(DbHelper::parseParam('venti.locale', $criteria->locale, $query->params));
     }
     if ($criteria->startDate) {
         $query->andWhere(DbHelper::parseDateParam('venti.startDate', $criteria->startDate, $query->params));
     }
     if ($criteria->id) {
         $query->andWhere(DbHelper::parseParam('venti.eventid', $criteria->eventid, $query->params));
     }
     if ($criteria->eventid) {
         $query->andWhere(DbHelper::parseParam('venti.eventid', $criteria->eventid, $query->params));
     }
     if ($criteria->endDate) {
         $query->andWhere(DbHelper::parseDateParam('venti.endDate', $criteria->endDate, $query->params));
     }
     if ($criteria->summary) {
         $query->andWhere(DbHelper::parseParam('venti.summary', $criteria->summary, $query->params));
     }
     if ($criteria->isrepeat) {
         $query->andWhere(DbHelper::parseParam('venti.isrepeat', $criteria->isrepeat, $query->params));
     }
     if ($criteria->rRule) {
         $query->andWhere(DbHelper::parseParam('venti.rRule', $criteria->rRule, $query->params));
     }
     if ($criteria->eid) {
         $query->andWhere(DbHelper::parseParam('venti.eid', $criteria->eid, $query->params));
     }
     if ($criteria->repeat) {
         $query->andWhere(DbHelper::parseParam('venti.repeat', $criteria->repeat, $query->params));
     }
     if ($criteria->allDay) {
         $query->andWhere(DbHelper::parseDateParam('venti.allDay', $criteria->allDay, $query->params));
     }
     if ($criteria->between) {
         $dates = array();
         $interval = array();
         if (!is_array($criteria->between)) {
             $criteria->between = ArrayHelper::stringToArray($criteria->between);
         }
         if (count($criteria->between) == 2) {
             foreach ($criteria->between as $ref) {
                 if (!$ref instanceof \DateTime) {
                     $dates[] = DateTime::createFromString($ref, craft()->getTimeZone());
                 } else {
                     $dates[] = $ref;
                 }
             }
             if ($dates[0] > $dates[1]) {
                 $interval[0] = $dates[1];
                 $interval[1] = $dates[0];
             } else {
                 $interval = $dates;
             }
             $query->andWhere('(venti.startDate BETWEEN :betweenStartDate AND :betweenEndDate) OR (:betweenStartDate BETWEEN venti.startDate AND venti.endDate)', array(':betweenStartDate' => DateTimeHelper::formatTimeForDb($interval[0]->getTimestamp()), ':betweenEndDate' => DateTimeHelper::formatTimeForDb($interval[1]->getTimestamp())));
         }
     }
 }

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

 /**
  * 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('tags.setId, tags.name')->join('tags tags', 'tags.id = elements.id');
     if ($criteria->name) {
         $query->andWhere(DbHelper::parseParam('tags.name', $criteria->name, $query->params));
     }
     if ($criteria->setId) {
         $query->andWhere(DbHelper::parseParam('tags.setId', $criteria->setId, $query->params));
     }
     if ($criteria->set) {
         $query->join('tagsets tagsets', 'tagsets.id = tags.setId');
         $query->andWhere(DbHelper::parseParam('tagsets.handle', $criteria->set, $query->params));
     }
 }

 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('formbuilder_entries.formId, formbuilder_entries.title, formbuilder_entries.data')->join('formbuilder_entries formbuilder_entries', 'formbuilder_entries.id = elements.id');
     if ($criteria->formId) {
         $query->andWhere(DbHelper::parseParam('formbuilder_entries.formId', $criteria->formId, $query->params));
     }
 }

 /**
  * @inheritDoc IElementType::modifyElementsQuery()
  *
  * @param DbCommand $query
  * @param ElementCriteriaModel $criteria
  *
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('globalsets.name, globalsets.handle, globalsets.fieldLayoutId')->join('globalsets globalsets', 'globalsets.id = elements.id');
     if ($criteria->handle) {
         $query->andWhere(DbHelper::parseParam('globalsets.handle', $criteria->handle, $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));
     }
 }

 /**
  * 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('shortlist_list.default, shortlist_list.userSlug, shortlist_list.public, shortlist_list.type, shortlist_list.ownerId, shortlist_list.ownerType')->join('shortlist_list shortlist_list', 'shortlist_list.id = elements.id');
     if ($criteria->default) {
         $query->andWhere(DbHelper::parseParam('shortlist_list.default', $criteria->default, $query->params));
     }
     if ($criteria->userSlug) {
         $query->andWhere(DbHelper::parseParam('shortlist_list.userSlug', $criteria->userSlug, $query->params));
     }
     if ($criteria->public) {
         $query->andWhere(DbHelper::parseParam('shortlist_list.public', $criteria->public, $query->params));
     }
     if ($criteria->ownerId) {
         $query->andWhere(DbHelper::parseParam('shortlist_list.ownerId', $criteria->ownerId, $query->params));
     }
     if ($criteria->ownerType) {
         $query->andWhere(DbHelper::parseParam('shortlist_list.ownerType', $criteria->ownerType, $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 ($criteria->sourceId) {
         $query->andWhere(DbHelper::parseParam('assetfiles.sourceId', $criteria->sourceId, $query->params));
     }
     if ($criteria->folderId) {
         $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));
     }
 }

 /**
  * @inheritDoc IElementType::modifyElementsQuery()
  *
  * @param DbCommand            $query
  * @param ElementCriteriaModel $criteria
  *
  * @return mixed
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('categories.groupId')->join('categories categories', 'categories.id = elements.id')->join('categorygroups categorygroups', 'categorygroups.id = categories.groupId')->leftJoin('structures structures', 'structures.id = categorygroups.structureId')->leftJoin('structureelements structureelements', array('and', 'structureelements.structureId = structures.id', 'structureelements.elementId = categories.id'));
     if ($criteria->groupId) {
         $query->andWhere(DbHelper::parseParam('categories.groupId', $criteria->groupId, $query->params));
     }
     if ($criteria->group) {
         $query->andWhere(DbHelper::parseParam('categorygroups.handle', $criteria->group, $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('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));
     }
 }

 /**
  * @inheritDoc IFieldType::modifyElementsQuery()
  *
  * @param DbCommand $query
  * @param mixed     $value
  *
  * @return null|false
  */
 public function modifyElementsQuery(DbCommand $query, $value)
 {
     if ($value !== null) {
         if ($this->defineContentAttribute()) {
             $handle = $this->model->handle;
             $query->andWhere(DbHelper::parseParam('content.' . craft()->content->fieldColumnPrefix . $handle, $value, $query->params));
         } else {
             return false;
         }
     }
 }

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

 /**
  * @inheritDoc IElementType::modifyElementsQuery()
  *
  * @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.client, users.locked, users.pending, users.suspended, users.archived, users.lastLoginDate, users.lockoutDate, users.preferredLocale')->join('users users', 'users.id = elements.id');
     if ($criteria->admin) {
         $query->andWhere(DbHelper::parseParam('users.admin', $criteria->admin, $query->params));
     }
     if ($criteria->client && craft()->getEdition() == Craft::Client) {
         $query->andWhere(DbHelper::parseParam('users.client', $criteria->client, $query->params));
     }
     if ($criteria->can && craft()->getEdition() == Craft::Pro) {
         // Get the actual permission ID
         if (is_numeric($criteria->can)) {
             $permissionId = $criteria->can;
         } else {
             $permissionId = craft()->db->createCommand()->select('id')->from('userpermissions')->where('name = :name', array(':name' => strtolower($criteria->can)))->queryScalar();
         }
         // Find the users that have that permission, either directly or through a group
         $permittedUserIds = array();
         // If the permission hasn't been assigned to any groups/users before, it won't have an ID. Don't bail
         // though, since we still want to look for admins.
         if ($permissionId) {
             // Get the user groups that have that permission
             $permittedGroupIds = craft()->db->createCommand()->select('groupId')->from('userpermissions_usergroups')->where('permissionId = :permissionId', array(':permissionId' => $permissionId))->queryColumn();
             if ($permittedGroupIds) {
                 $permittedUserIds = $this->_getUserIdsByGroupIds($permittedGroupIds);
             }
             // Get the users that have that permission directly
             $permittedUserIds = array_merge($permittedUserIds, craft()->db->createCommand()->select('userId')->from('userpermissions_users')->where('permissionId = :permissionId', array(':permissionId' => $permissionId))->queryColumn());
         }
         if ($permittedUserIds) {
             $permissionConditions = array('or', 'users.admin = 1', array('in', 'elements.id', $permittedUserIds));
         } else {
             $permissionConditions = 'users.admin = 1';
         }
         $query->andWhere($permissionConditions);
     }
     if ($criteria->groupId) {
         $userIds = $this->_getUserIdsByGroupIds($criteria->groupId);
         if (!$userIds) {
             return false;
         }
         $query->andWhere(array('in', 'elements.id', $userIds));
     }
     if ($criteria->group) {
         // Get the actual group ID(s)
         $groupIdsQuery = craft()->db->createCommand()->select('id')->from('usergroups');
         $groupIdsQuery->where(DbHelper::parseParam('handle', $criteria->group, $groupIdsQuery->params));
         $groupIds = $groupIdsQuery->queryColumn();
         // In the case where the group doesn't exist.
         if (!$groupIds) {
             return false;
         }
         $userIds = $this->_getUserIdsByGroupIds($groupIds);
         // In case there are no users in the groups.
         if (!$userIds) {
             return false;
         }
         $query->andWhere(array('in', 'elements.id', $userIds));
     }
     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->lastLoginDate) {
         $query->andWhere(DbHelper::parseDateParam('users.lastLoginDate', $criteria->lastLoginDate, $query->params));
     }
 }

 /**
  * @param DbCommand $query
  * @param ElementCriteriaModel $criteria
  *
  * @return void
  */
 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('orders.id,
     orders.number,
     orders.couponCode,
     orders.itemTotal,
     orders.baseDiscount,
     orders.baseShippingCost,
     orders.totalPrice,
     orders.totalPaid,
     orders.orderStatusId,
     orders.dateOrdered,
     orders.email,
     orders.dateOrdered,
     orders.datePaid,
     orders.currency,
     orders.lastIp,
     orders.message,
     orders.returnUrl,
     orders.cancelUrl,
     orders.orderStatusId,
     orders.billingAddressId,
     orders.billingAddressData,
     orders.shippingAddressId,
     orders.shippingAddressData,
     orders.shippingMethodId,
     orders.paymentMethodId,
     orders.customerId,
     orders.dateUpdated')->join('market_orders orders', 'orders.id = elements.id');
     if ($criteria->completed) {
         if ($criteria->completed == true) {
             $query->andWhere('orders.dateOrdered is not null');
             $criteria->completed = null;
         }
     }
     if ($criteria->dateOrdered) {
         $query->andWhere(DbHelper::parseParam('orders.dateOrdered', $criteria->dateOrdered, $query->params));
     }
     if ($criteria->number) {
         $query->andWhere(DbHelper::parseParam('orders.number', $criteria->number, $query->params));
     }
     if ($criteria->orderStatus) {
         if ($criteria->orderStatus instanceof Market_OrderStatusModel) {
             $criteria->orderStatusId = $criteria->orderStatus->id;
             $criteria->orderStatus = NULL;
         } else {
             $query->andWhere(DbHelper::parseParam('orders.orderStatusId', $criteria->orderStatus, $query->params));
         }
     }
     if ($criteria->orderStatusId) {
         $query->andWhere(DbHelper::parseParam('orders.orderStatusId', $criteria->orderStatusId, $query->params));
     }
     if ($criteria->customer) {
         if ($criteria->customer instanceof Market_CustomerModel) {
             if ($criteria->customer->id) {
                 $criteria->customerId = $criteria->customer->id;
                 $criteria->customer = null;
             } else {
                 $query->andWhere(DbHelper::parseParam('orders.customerId', 'IS NULL', $query->params));
             }
         }
     }
     if ($criteria->customerId) {
         $query->andWhere(DbHelper::parseParam('orders.customerId', $criteria->customerId, $query->params));
     }
     if ($criteria->updatedOn) {
         $query->andWhere(DbHelper::parseDateParam('orders.dateUpdated', $criteria->updatedOn, $query->params));
     } else {
         if ($criteria->updatedAfter) {
             $query->andWhere(DbHelper::parseDateParam('orders.dateUpdated', '>=' . $criteria->updatedAfter, $query->params));
         }
         if ($criteria->updatedBefore) {
             $query->andWhere(DbHelper::parseDateParam('orders.dateUpdated', '<' . $criteria->updatedBefore, $query->params));
         }
     }
 }