PHP DbCommand类代码示例

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

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

 /**
  * Returns the data for a run chart, based on a given DB query, start/end dates, and the desired time interval unit.
  *
  * The query’s SELECT clause should already be set to a column aliased as `value`.
  *
  * The $options array can override the following defaults:
  *
  *  - `intervalUnit` - The time interval unit to use ('hour', 'day', 'month', or 'year').
  *                     By default, a unit will be decided automatically based on the start/end date duration.
  *  - `categoryLabel` - The label to use for the chart categories (times). Defaults to "Date".
  *  - `valueLabel` - The label to use for the chart values. Defaults to "Value".
  *  - `valueType` - The type of values that are being plotted ('number', 'currency', 'percent', 'time'). Defaults to 'number'.
  *
  * @param DbCommand  $query        The DB query that should be used
  * @param DateTime   $startDate    The start of the time duration to select (inclusive)
  * @param DateTime   $endDate      The end of the time duratio to select (exclusive)
  * @param string     $dateColumn   The column that represents the date
  * @param array|null $options      Any customizations that should be made over the default options
  *
  * @return array
  */
 public static function getRunChartDataFromQuery(DbCommand $query, DateTime $startDate, DateTime $endDate, $dateColumn, $options = array())
 {
     // Setup
     $options = array_merge(array('intervalUnit' => null, 'categoryLabel' => Craft::t('Date'), 'valueLabel' => Craft::t('Value'), 'valueType' => 'number'), $options);
     $craftTimezone = new \DateTimeZone(craft()->timezone);
     $utc = new \DateTimeZone(DateTime::UTC);
     if ($options['intervalUnit'] && in_array($options['intervalUnit'], array('year', 'month', 'day', 'hour'))) {
         $intervalUnit = $options['intervalUnit'];
     } else {
         $intervalUnit = self::getRunChartIntervalUnit($startDate, $endDate);
     }
     switch ($intervalUnit) {
         case 'year':
             $sqlDateFormat = '%Y-01-01';
             $phpDateFormat = 'Y-01-01';
             $sqlGroup = "YEAR({$dateColumn})";
             $cursorDate = new DateTime($startDate->format('Y-01-01'), $craftTimezone);
             break;
         case 'month':
             $sqlDateFormat = '%Y-%m-01';
             $phpDateFormat = 'Y-m-01';
             $sqlGroup = "YEAR({$dateColumn}), MONTH({$dateColumn})";
             $cursorDate = new DateTime($startDate->format('Y-m-01'), $craftTimezone);
             break;
         case 'day':
             $sqlDateFormat = '%Y-%m-%d';
             $phpDateFormat = 'Y-m-d';
             $sqlGroup = "YEAR({$dateColumn}), MONTH({$dateColumn}), DAY({$dateColumn})";
             $cursorDate = new DateTime($startDate->format('Y-m-d'), $craftTimezone);
             break;
         case 'hour':
             $sqlDateFormat = '%Y-%m-%d %H:00:00';
             $phpDateFormat = 'Y-m-d H:00:00';
             $sqlGroup = "YEAR({$dateColumn}), MONTH({$dateColumn}), DAY({$dateColumn}), HOUR({$dateColumn})";
             $cursorDate = new DateTime($startDate->format('Y-m-d'), $craftTimezone);
             break;
     }
     // Execute the query
     $results = $query->addSelect("DATE_FORMAT({$dateColumn}, '{$sqlDateFormat}') as date")->andWhere(array('and', $dateColumn . ' >= :startDate', $dateColumn . ' < :endDate'), array(':startDate' => $startDate->mySqlDateTime(), ':endDate' => $endDate->mySqlDateTime()))->group($sqlGroup)->order($dateColumn . ' asc')->queryAll();
     // Assembe the data
     $rows = array();
     $endTimestamp = $endDate->getTimestamp();
     while ($cursorDate->getTimestamp() < $endTimestamp) {
         // Do we have a record for this date?
         $formattedCursorDate = $cursorDate->format($phpDateFormat, $utc);
         if (isset($results[0]) && $results[0]['date'] == $formattedCursorDate) {
             $value = (double) $results[0]['value'];
             array_shift($results);
         } else {
             $value = 0;
         }
         $rows[] = array($formattedCursorDate, $value);
         $cursorDate->modify('+1 ' . $intervalUnit);
     }
     return array('columns' => array(array('type' => $intervalUnit == 'hour' ? 'datetime' : 'date', 'label' => $options['categoryLabel']), array('type' => $options['valueType'], 'label' => $options['valueLabel'])), 'rows' => $rows);
 }

 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 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_item.elementId, shortlist_item.elementType, shortlist_item.listId, shortlist_list.ownerId')->join('shortlist_item shortlist_item', 'shortlist_item.id = elements.id')->join('shortlist_list shortlist_list', 'shortlist_item.listId = shortlist_list.id');
     if ($criteria->listId) {
         $query->andWhere(DbHelper::parseParam('shortlist_item.listId', $criteria->listId, $query->params));
     }
     if ($criteria->elementId) {
         $query->andWhere(DbHelper::parseParam('shortlist_item.elementId', $criteria->elementId, $query->params));
     }
     if ($criteria->ownerId) {
         $query->andWhere(DbHelper::parseParam('shortlist_list.ownerId', $criteria->ownerId, $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));
            }
        }
    }

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

 /**
  * Get definition of specifique table
  *
  * @param $table The name of table
  * @return array
  * @author Wilker
  **/
 public function __get($table)
 {
     if (!isset($this->tables[$table])) {
         $this->tables[$table] = DbCommand::table_fields($table);
     }
     return $this->tables[$table];
 }

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

 public function modifyElementsQuery(DbCommand $query, ElementCriteriaModel $criteria)
 {
     $query->addSelect('
         workflow_submissions.ownerId,
         workflow_submissions.draftId,
         workflow_submissions.editorId,
         workflow_submissions.publisherId,
         workflow_submissions.status,
         workflow_submissions.notes,
         workflow_submissions.dateApproved,
         workflow_submissions.dateRejected,
         workflow_submissions.dateRevoked
     ')->join('workflow_submissions workflow_submissions', 'workflow_submissions.id = elements.id');
     if ($criteria->ownerId) {
         $query->andWhere(DbHelper::parseParam('workflow_submissions.ownerId', $criteria->ownerId, $query->params));
     }
     if ($criteria->draftId) {
         $query->andWhere(DbHelper::parseParam('workflow_submissions.draftId', $criteria->draftId, $query->params));
     }
     if ($criteria->editorId) {
         $query->andWhere(DbHelper::parseParam('workflow_submissions.editorId', $criteria->editorId, $query->params));
     }
     if ($criteria->publisherId) {
         $query->andWhere(DbHelper::parseParam('workflow_submissions.publisherId', $criteria->publisherId, $query->params));
     }
     if ($criteria->status) {
         $query->andWhere(DbHelper::parseParam('workflow_submissions.status', $criteria->status, $query->params));
     }
     if ($criteria->notes) {
         $query->andWhere(DbHelper::parseParam('workflow_submissions.notes', $criteria->notes, $query->params));
     }
     if ($criteria->dateApproved) {
         $query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateApproved', $criteria->dateApproved, $query->params));
     }
     if ($criteria->dateRejected) {
         $query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateRejected', $criteria->dateRejected, $query->params));
     }
     if ($criteria->dateRevoked) {
         $query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateRevoked', $criteria->dateRevoked, $query->params));
     }
     if ($criteria->dateCreated) {
         $query->andWhere(DbHelper::parseDateParam('workflow_submissions.dateCreated', $criteria->dateCreated, $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));
     }
 }

 protected function break_relation($object)
 {
     if (isset($this->options['through'])) {
         $rel_table = $this->options['through'];
         $local_field = $this->get_local_field();
         $foreign_field = $this->get_foreign_field($this->foreign_model);
         $local_id = $this->local_model->primary_key_value();
         $foreign_id = $object->primary_key_value();
         //TODO: fix it to a more generic way
         if (isset($this->options['module'])) {
             $module = $this->options['module'];
             DbCommand::execute("DELETE FROM `{$rel_table}` WHERE `{$local_field}` = '{$local_id}' and `{$foreign_field}` = '{$foreign_id}' and `module` = '{$module}'");
         } else {
             DbCommand::execute("DELETE FROM `{$rel_table}` WHERE `{$local_field}` = '{$local_id}' and `{$foreign_field}` = '{$foreign_id}'");
         }
     } else {
         $local_field = $this->get_foreign_field($this->local_model);
         $object->{$local_field} = null;
         $object->save();
     }
 }

 /**
  * @inheritDoc IFieldType::modifyElementsQuery()
  *
  * @param DbCommand $query
  * @param mixed     $value
  *
  * @return null|false
  */
 public function modifyElementsQuery(DbCommand $query, $value)
 {
     if ($value == 'not :empty:') {
         $value = ':notempty:';
     }
     if ($value == ':notempty:' || $value == ':empty:') {
         $alias = 'relations_' . $this->model->handle;
         $operator = $value == ':notempty:' ? '!=' : '=';
         $query->andWhere("(select count({$alias}.id) from {{relations}} {$alias} where {$alias}.sourceId = elements.id and {$alias}.fieldId = :fieldId) {$operator} 0", array(':fieldId' => $this->model->id));
     } else {
         if ($value !== null) {
             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));
     }
 }