Creates a DB command that can be used to execute this query.
public createCommand ( |
||
$db | the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used. | |
Результат | the created DB command instance. |
/** * 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; }
/** * Displays a single Event model. * @param integer $id * @return mixed */ public function actionView($id) { $query = new Query(); $query->select(['*'])->distinct()->from('checkin')->where('event_id=' . $id)->join('JOIN', 'message', 'message.sender_id =checkin.user_id')->join('JOIN', 'user', 'user.id = checkin.user_id')->all(); $command = $query->createCommand(); $data = $command->queryAll(); $searchModel2 = new CheckinSearch(); $dataProvider2 = $searchModel2->searchUsers(\Yii::$app->user->identity->id); //die(); return $this->render('view', ['model' => $this->findModel($id), 'messages' => $data, 'dataProvider2' => $dataProvider2]); }
public function actionMahasiswalist($q = null) { $query = new Query(); $query->select('nama_mahasiswa, nim')->from('aitk_r_mahasiswa')->where('nama_mahasiswa LIKE "%' . $q . '%"')->orderBy('nama_mahasiswa'); $command = $query->createCommand(); $data = $command->queryAll(); $out = []; foreach ($data as $d) { $out[] = ['value' => $d['nama_mahasiswa'] . '(' . $d['nim'] . ')']; } echo Json::encode($out); }
public function actionContactList($q = null) { $out = []; if (!is_null($q)) { $query = new Query(); $query->select('ID as id, Name As name')->from('pbk')->where('Name LIKE "%' . $q . '%"'); $command = $query->createCommand(); $data = $command->queryAll(); $out = array_values($data); } echo Json::encode($out); }
public function actionGetNikkesList($q = null) { $query = new Query(); $query->select('nikkes')->from('peserta')->where('nikkes LIKE "%' . $q . '%"')->orderby('nikkes'); $command = $query->createCommand(); $data = $command->queryAll(); foreach ($data as $d) { # code... $out[] = ['value' => $d['nikkes']]; } echo Json::encode($out); }
public function updateStatus($stationId, $decimal) { $binary = Convert::dec2Bin($decimal, 10); // get equipments $query = new Query(); $query->select('b1.*, b2.sort')->from('equipment_status b1')->leftJoin('equipment b2', 'b1.equipment_id = b2.id')->where(['station_id' => $stationId]); $equipments = $query->createCommand()->queryAll(); if (!empty($equipments)) { foreach ($equipments as $e) { Yii::$app->db->createCommand()->update('equipment_status', ['status' => $binary[$e['sort']]], ['station_id' => $stationId, 'equipment_id' => $e['equipment_id']])->execute(); } } }
/** * Displays a single MealItem model. * @param string $id * @return mixed */ public function actionView($id) { $query = new Query(); $query->select(['meal_item.*', 'meal_item_image.image_new_url'])->from('meal_item')->join('LEFT JOIN', 'meal_item_image', 'meal_item_image.itemId = meal_item.id')->where(['meal_item.id' => $id]); $command = $query->createCommand(); $data = $command->queryAll(); // echo '<pre>'; // print_r($data);die; /* return $this->render('view', [ 'model' => $this->findModel($id), 'productImages' => $productImages, ]);*/ return $this->render('view', ['model' => $data[0]]); }
public function actionGetLastDate($nikkes) { $query = new Query(); $query->select('*')->from('monitoring_kacamata')->where('nikkes LIKE "' . $nikkes . '"')->orderby('tgl_ambil DESC')->limit(3); $command = $query->createCommand(); $data = $command->queryAll(); //$out = ""; foreach ($data as $d) { # code... $out[] = ['nikkes' => $d['nikkes'], 'tgl_ambil' => $d['tgl_ambil'], 'hak' => $d['hak_kacamata_id']]; } echo Json::encode(!empty($out) ? $out : null); //echo Json::encode($out); }
public function actionSearch($q = null) { if (!$q) { return $this->render('/site/error', ['name' => 'Bad request', 'message' => 'q is not defined']); } \Yii::$app->response->format = \yii\web\Response::FORMAT_JSON; $out = ['results' => ['id' => '', 'text' => '']]; $query = new Query(); $query->select('id, name AS text')->from('student')->where(['like', 'name', $q])->limit(20); $command = $query->createCommand(); $data = $command->queryAll(); $out['results'] = array_values($data); return $out; }
public function actionGetmessages($mr_id) { $model = new RoomsConversations(); /*$model=new RoomsConversations(); $model = RoomsConversations::find()->where(['mr_id'=>$mr_id])->all(); return $model; */ $query = new Query(); $jointype = "LEFT OUTER JOIN"; $query->select('personal_information.pi_id,personal_information.pi_name, user.id,message_rooms.mr_id,conversations.cr_id,conversations.message,conversations.timestamp,message_rooms.user_id_2')->from('rooms_conversations')->join($jointype, 'message_rooms', 'message_rooms.mr_id=rooms_conversations.mr_id')->join($jointype, 'conversations', 'rooms_conversations.cr_id=conversations.cr_id')->join($jointype, 'user', 'user.id=conversations.user_id')->join($jointype, 'personal_information', 'user.pi_id=personal_information.pi_id')->where(['rooms_conversations.mr_id' => $mr_id]); $command = $query->createCommand(); $data = $command->queryAll(); return $data; }
/** * Action Ajax for searc customer list in select2 widget * @param [type] $search [description] * @param [type] $id [description] * @return [json] JSON['customer_id'=>'name'] */ public function actionSearchCustomer($search = null, $id = null) { $out = ['more' => false]; $q = new Query(); if (!is_null($search)) { $q->select('p.id, p.name as text')->from(ResPartner::tableName() . ' as p')->where('LOWER(p.name) LIKE :search')->andWhere('employee is false and customer is true')->addParams([':search' => '%' . strtolower($search) . '%']); $users = $q->createCommand()->queryAll(); $out['results'] = array_values($users); } elseif ($id > 0) { $out['results'] = ['id' => $id, 'text' => ResPartner::find()->where(['id' => $id])->one()->name]; } else { $out['results'] = ['id' => 0, 'text' => 'No matching records found']; } echo Json::encode($out); }
/** * @param int $limit * * @param null $version * * @return array */ protected function getMigrationHistory($limit, $version = null) { if ($this->db->schema->getTableSchema($this->migrationTable, true) === null) { $this->createMigrationHistoryTable(); } $query = new Query(); $query->select(['version', 'apply_time'])->from($this->migrationTable)->orderBy('version DESC')->limit($limit); if ($version) { $query->where(['app_version' => $version]); } $rows = $query->createCommand($this->db)->queryAll(); $history = ArrayHelper::map($rows, 'version', 'apply_time'); unset($history[self::BASE_MIGRATION]); return $history; }
public function run($search = null, $id = null) { $out = ['more' => false]; if (!is_null($search)) { $query = new Query(); $query->select('[[id]], [[username]] AS [[text]]')->from('{{%user}}')->filterWhere(['like', '[[username]]', $search])->limit(20); $command = $query->createCommand(); $data = $command->queryAll(); $out['results'] = array_values($data); } elseif ($id > 0) { $out['results'] = ['id' => $id, 'text' => User::findOne($id)->username]; } else { $out['results'] = ['id' => 0, 'text' => Yii::t('app', 'No matching records found')]; } return Json::encode($out); }
public function actionPartnerlist($search = null, $id = null) { $out = ['more' => false]; if (!is_null($search)) { $query = new Query(); $query->select('id, name AS text')->from('res_partner')->where(['like', 'name', $search])->andWhere(['customer' => true])->andWhere(['is_company' => true])->limit(20); $command = $query->createCommand(); $data = $command->queryAll(); $out['results'] = array_values($data); } elseif ($id > 0) { $out['results'] = ['id' => $id, 'text' => ResPartner::find($id)->name]; } else { $out['results'] = ['id' => 0, 'text' => 'No matching records found']; } echo Json::encode($out); }
/** * Lists all Test1 models. * @return mixed */ public function actionIndex() { $params = $_REQUEST; $filter = array(); $sort = ""; $page = 1; $limit = 10; if (isset($params['page'])) { $page = $params['page']; } if (isset($params['limit'])) { $limit = $params['limit']; } $offset = $limit * ($page - 1); /* Filter elements */ if (isset($params['filter'])) { $filter = (array) json_decode($params['filter']); } if (isset($params['datefilter'])) { $datefilter = (array) json_decode($params['datefilter']); } if (isset($params['sort'])) { $sort = $params['sort']; if (isset($params['order'])) { if ($params['order'] == "false") { $sort .= " desc"; } else { $sort .= " asc"; } } } $query = new Query(); $query->offset($offset)->limit($limit)->from('test1')->orderBy($sort); $query->andFilterWhere(['id' => $filter['id'], 'createdAt' => $filter['createdAt'], 'updatedAt' => $filter['updatedAt']]); $query->andFilterWhere(['like', 'name', $filter['name']]); if ($datefilter['from']) { $query->andWhere("createdAt >= '" . $datefilter['from'] . "' "); } if ($datefilter['to']) { $query->andWhere("createdAt <= '" . $datefilter['to'] . "'"); } $command = $query->createCommand(); $models = $command->queryAll(); $totalItems = $query->count(); $this->setHeader(200); echo json_encode(array('status' => 1, 'data' => $models, 'totalItems' => $totalItems), JSON_PRETTY_PRINT); }
public function __set($name, $value) { if (is_array($value) && count($value) > 0 && !$value[0] instanceof Object || !is_array($value) && !$value instanceof Object) { $getter = 'get' . $name; /** @var ActiveQuery $activeQuery */ $activeQuery = $this->owner->{$getter}(); /* @var $modelClass ActiveRecord */ $modelClass = $activeQuery->modelClass; $value = $modelClass::findAll($value); if (!empty($activeQuery->via)) { list($junctionTable, $primaryModelColumn, $relatedModelColumn) = $this->parseQuery($activeQuery); $query = new Query(['select' => [$relatedModelColumn], 'from' => [$junctionTable], 'where' => [$primaryModelColumn => $this->owner->primaryKey]]); $this->oldRelations[$name] = $query->createCommand()->queryColumn(); } } $this->owner->populateRelation($name, $value); }