PHP Query::createCommand方法代码示例

 /**
  * Perform the DB Query
  *
  * @return array
  */
 public function perform()
 {
     // Create the command
     $command = $this->query->createCommand();
     // Execute the command and return
     return $command->queryAll();
 }

 public function actionProspek()
 {
     $regional = Regional::find()->all();
     $query = new Query();
     $data = array();
     foreach ($regional as $rows) {
         $query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD3 IS NOT NULL')->all();
         $handphone = $query->createCommand()->queryScalar();
         $query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE3.FIELD6 IS NOT NULL')->all();
         $email = $query->createCommand()->queryScalar();
         $query->select('COUNT(*) AS JUMLAH')->from('TABLE1')->join('LEFT OUTER JOIN', 'TABLE3', 'TABLE3.FIELD1 = TABLE1.FIELD2')->join('LEFT OUTER JOIN', 'TABLE2', 'TABLE2.FIELD1 = TABLE3.FIELD2')->where(['TABLE3.FIELD7' => $rows->FIELD1])->andWhere('TABLE2.FIELD7 IS NOT NULL')->all();
         $phone = $query->createCommand()->queryScalar();
         array_push($data, array('regional' => $rows->FIELD1, 'handphone' => $handphone, 'email' => $email, 'phone' => $phone));
     }
     return $this->render('prospek', ['data' => $data, 'regional' => $regional]);
 }

 /**
  * @return \yii\web\Response
  */
 public function actionLoadTranslations()
 {
     $sourceMessageTable = Adm::getInstance()->manager->createSourceMessageQuery('tableName');
     $messageTable = Adm::getInstance()->manager->createMessageQuery('tableName');
     /* @var $i18n \pavlinter\translation\I18N */
     $i18n = Yii::$app->i18n;
     $languages = $i18n->getLanguages();
     $query = new Query();
     $query->from($sourceMessageTable)->select(['id']);
     /* @var $reader \yii\db\DataReader */
     $reader = $query->createCommand()->query();
     $count = 0;
     while ($row = $reader->read()) {
         $id = $row['id'];
         foreach ($languages as $language_id => $language) {
             $query = new Query();
             $exists = $query->from($messageTable)->where(['id' => $id, 'language_id' => $language_id])->exists();
             if (!$exists) {
                 Yii::$app->db->createCommand()->insert($messageTable, ['id' => $id, 'language_id' => $language_id, 'translation' => ''])->execute();
                 $count++;
             }
         }
     }
     Yii::$app->getSession()->setFlash('success', Adm::t('source-message', 'Loaded {count} translations.', ['count' => $count]));
     return $this->redirect(['index']);
 }

 public function uploadImage($route, $gallery_types_id, $gallery_groups_id)
 {
     $type = (new Query())->select('*')->from($this->tableTypes)->where(['id' => $gallery_types_id])->createCommand()->queryOne();
     $route = preg_replace('/\\/$/', '', $route);
     $destination = preg_replace('/\\/$/', '', $type['destination']);
     if (!is_dir($route . $destination)) {
         if (!mkdir($route . $destination)) {
             return ['success' => false, 'message' => 'Failed to add directory'];
         }
     }
     $imageFile = UploadedFile::getInstanceByName('image');
     $image = (new Query())->select('*')->from($this->tableImages)->where(['gallery_groups_id' => $gallery_groups_id])->andWhere(['name' => $imageFile->baseName . '.' . $imageFile->extension])->createCommand()->queryOne();
     if ($image) {
         return ['success' => false, 'message' => 'File downloaded previously in this group'];
     }
     $src_file = $route . $destination . '/' . $imageFile->baseName . '.' . $imageFile->extension;
     $imageFile->saveAs($src_file, true);
     $size = $this->getSize($src_file, $type);
     $image_small = $this->renderFilename($route . $destination, $imageFile->extension);
     $image_large = $this->renderFilename($route . $destination, $imageFile->extension);
     Image::$driver = [Image::DRIVER_GD2];
     Image::thumbnail($src_file, $size['small_width'], $size['small_height'])->save($route . $destination . '/' . $image_small . '.' . $imageFile->extension, ['quality' => $type['quality']]);
     Image::thumbnail($src_file, $size['large_width'], $size['large_height'])->save($route . $destination . '/' . $image_large . '.' . $imageFile->extension, ['quality' => $type['quality']]);
     unlink($src_file);
     $query = new Query();
     $query->createCommand()->insert($this->tableImages, ['gallery_groups_id' => $gallery_groups_id, 'small' => $destination . '/' . $image_small . '.' . $imageFile->extension, 'large' => $destination . '/' . $image_large . '.' . $imageFile->extension, 'name' => $imageFile->baseName . '.' . $imageFile->extension, 'seq' => $this->getLastSequence($gallery_groups_id) + 1])->execute();
     return ['success' => true, 'gallery_images_id' => Yii::$app->db->getLastInsertID(), 'gallery_groups_id' => $gallery_groups_id, 'small' => $destination . '/' . $image_small . '.' . $imageFile->extension, 'large' => $destination . '/' . $image_large . '.' . $imageFile->extension];
 }

 /**
  * Обновляет запись в таблицу
  *
  */
 public function update()
 {
     $query = new Query();
     $command = $query->createCommand();
     $command->update(self::TABLE, ['email' => $this->email, 'name_first' => $this->name_first, 'name_last' => $this->name_last, 'rating' => $this->rating], ['id' => $this->id])->execute();
     return true;
 }

 /**
  * @return html roles for roles column in admin index
  */
 public function getRolesHTML()
 {
     $query = new Query();
     $query->select('item_name')->from('{{%auth_assignment}}')->where('user_id=' . $this->id);
     $command = $query->createCommand();
     $roles = $command->queryAll();
     return $roles;
 }

 /**
  * 按 categoryid 查询该分类下的所有疾病
  * @param category $categoryId 分类ID
  */
 public function getDiseaseByCategoryId($categoryId)
 {
     $query = new Query();
     $query->select(['cdr.id as id', 'cdr.diseaseid', 'cdr.class_level11', 'cdr.class_level2', 'd.name'])->from('9939_category_disease_rel as cdr')->join('LEFT_JOIN', '9939_disease as d', 'd.id = cdr.diseaseid');
     $command = $query->createCommand(static::getDb());
     $res = $command->queryAll();
     return $res;
 }

 /**
  * Creates database view.
  * @param string $name    View name.
  * @param Query  $query   Query that is used to create view.
  * @param bool   $replace Whether to replace existing view with the same name.
  * @throws \yii\db\Exception
  * @see dropView
  */
 public function createView($name, Query $query, $replace = true)
 {
     echo "    > create table {$name} ...";
     $time = microtime(true);
     $sql = 'CREATE' . ($replace ? ' OR REPLACE' : '') . ' VIEW ' . $this->db->quoteTableName($name) . ' AS ' . $query->createCommand()->getRawSql();
     $this->db->createCommand($sql)->execute();
     echo ' done (time: ' . sprintf('%.3f', microtime(true) - $time) . "s)\n";
 }

 public static function getMeal()
 {
     $query = new Query();
     $query->select(['meal.*', 'meal_image.image_new_url', 'meal_image.image_org_url'])->from('meal')->join('LEFT JOIN', 'meal_image', 'meal_image.meal_id = meal.id')->where(['deal_status' => 1]);
     $command = $query->createCommand();
     $data = $command->queryAll();
     return $data;
 }

 /**
  * Обновляет запись в таблицу
  * @return boolean результат операции
  */
 public function update()
 {
     $query = new Query();
     $command = $query->createCommand();
     $fields = ['password' => md5($this->password)];
     $command->update(self::TABLE, $fields, ['id' => $this->id])->execute();
     return true;
 }

 protected function getProperties()
 {
     $result = [];
     $query = new Query();
     $query->select(Property::tableName() . '.key, ' . Property::tableName() . '.name')->from(Property::tableName());
     $query->innerJoin(PropertyGroup::tableName(), PropertyGroup::tableName() . '.id = ' . Property::tableName() . '.property_group_id');
     $query->andWhere([PropertyGroup::tableName() . '.object_id' => $this->object->id]);
     $command = $query->createCommand();
     return ArrayHelper::map($command->queryAll(), 'key', 'name');
 }

 public function getData()
 {
     $query = new Query();
     $query->select(Property::tableName() . '.id, ' . Property::tableName() . '.name')->from(Property::tableName());
     $query->leftJoin(PropertyGroup::tableName(), PropertyGroup::tableName() . '.id = ' . Property::tableName() . '.property_group_id');
     $query->andWhere([PropertyGroup::tableName() . '.object_id' => $this->objectId]);
     $command = $query->createCommand();
     $this->data = ArrayHelper::map($command->queryAll(), 'id', 'name');
     return parent::getData();
 }

 /**
  * Returns list of users' full names
  * @param int $limit records count
  * @return array list of users' names
  */
 public static function getUserList($limit = null)
 {
     $query = new Query();
     $query->select(['id', 'text' => "CONCAT(`u`.`first_name`,' ', `u`.`last_name`)"])->from(['u' => 'User']);
     if (isset($limit)) {
         $query->limit($limit);
     }
     $query->each();
     $command = $query->createCommand();
     return $command->queryAll();
 }

 /**
  * @param Cart $cart
  *
  * @return array|mixed
  */
 public function load(Cart $cart)
 {
     $items = [];
     $identifier = $this->getIdentifier(Yii::$app->session->getId());
     $query = new Query();
     $query->select($this->dataField)->from($this->table)->where([$this->idField => $identifier]);
     if ($data = $query->createCommand($this->_db)->queryScalar()) {
         $items = unserialize($data);
     }
     return $items;
 }

 /**
  * Обновляет запись в таблице
  */
 public function update()
 {
     $query = new Query();
     $command = $query->createCommand();
     $command->update(self::TABLE, ['email' => $this->email, 'name_first' => $this->name_first, 'name_last' => $this->name_last], ['id' => $this->id])->execute();
     $command->delete(self::TABLE_LINK, ['user_id' => $this->id])->execute();
     foreach ($this->roles as $roleId) {
         $command->insert(self::TABLE_LINK, ['user_id' => $this->id, 'role_id' => $roleId])->execute();
     }
     return true;
 }