Query provides a set of methods to facilitate the specification of different clauses
in a SELECT statement. These methods can be chained together.
By calling Query::createCommand, we can get a Command instance which can be further
used to perform/execute the DB query against a database.
For example,
~~~
$query = new Query;
compose the query
$query->select('id, name')
->from('tbl_user')
->limit(10);
build and execute the query
$rows = $query->all();
alternatively, you can create DB command and execute it
$command = $query->createCommand();
$command->sql returns the actual SQL
$rows = $command->queryAll();
~~~
/** * Lists all Articlecomment models. * @return mixed */ public function actionIndex() { $list = ''; $post = Yii::$app->request->post(); if ($post['id']) { $mod = new Query(); $comment = $mod->select(['a.id', 'a.parentId', 'a.articleId', 'a.content', 'a.createTime', 'b.username'])->from('articlecomment as a')->leftJoin('user as b', 'a.userId = b.id')->where(['commentId' => $post['id']])->orderBy(['createTime' => 'DESC', 'id' => 'DESC'])->createCommand()->queryAll(); if ($comment) { foreach ($comment as $v) { $content = '回复@' . Articlecomment::getCommentByParId($v['parentId']) . ':' . $v['content']; $ahtml = html::a(html::tag("i", "", ["class" => "fa fa-thumbs-o-up"]) . html::tag("span", "回复"), ["/main/viewart", "id" => $v["articleId"], "parId" => $v['id']]); $list .= '<div class="infos small-comment' . $post['id'] . '" style="border:1px solid;"> <div class="media-body markdown-reply content-body"> <p>' . $content . '</p> <span class="opts pull-right"> <a class="author" >' . $v["username"] . '</a> • <addr title="' . $v["createTime"] . '">' . Html::tag("span", Yii::$app->formatter->asRelativeTime($v["createTime"])) . '</addr> ' . $ahtml . ' </span> </div> </div>'; } } } $result = array('success' => true, 'message' => $list); echo json_encode($result); die; return $this->renderAjax('index', ['success' => true, 'message' => '']); }
public static function mostPopular() { $query = new Query(); $query->select('meme_id, COUNT(meme_id) AS n_memes')->from('meme_vidmage')->groupBy('meme_id')->orderBy(['n_memes' => SORT_DESC])->limit(1); $row = $query->one(); return self::findOne($row['meme_id']); }
/** * Returns calculated availability */ public function getAvailability() { $query = new Query(); $query->select('Product, Warehouse, Availability')->from('GetProductAvailability')->where("Product = {$this->ID}"); $availability = $query->all(); return $availability; }
/** * Updates an existing PageData model. * If update is successful, the browser will be redirected to the 'view' page. * @param integer $id_page * @param string $language * @return mixed */ public function actionUpdate($id_page, $language) { $model = $this->findModel($id_page, $language); $model->setScenario('update'); //if (!Yii::$app->user->can('ownAction', ['model' => $model])) { // throw new ForbiddenHttpException('You are not allowed to perform this action.'); //} /* copy to other languages */ $languages = Message::getLocaleList(); $q = new Query(); $pageLang = $q->select(['language'])->from('{{%core_page_data}}')->where(['id_page' => $id_page])->all(); foreach ($pageLang as $pl) { if (in_array($pl['language'], array_keys($languages))) { unset($languages[$pl['language']]); } } //var_dump($languages); /* submitted */ if (Yii::$app->request->isPost) { /* page data */ $model->load(Yii::$app->request->post()); $model->page->load(Yii::$app->request->post()); $model->page->save(); $model->save(); return $this->redirect(['update', 'id_page' => $model->id_page, 'language' => $model->language]); } else { return $this->render('update', ['model' => $model, 'languages' => $languages]); } }
/** * Lists all Feed models. * @return mixed */ public function actionIndex() { $query = new Query(); $query->select('id, content, feed_data, template, created_at')->from('{{%home_feed}}')->where('user_id=:user_id', [':user_id' => Yii::$app->user->id])->orderBy('created_at DESC'); $pages = Tools::Pagination($query); return $this->render('index', ['feeds' => $pages['result'], 'pages' => $pages['pages']]); }
/** * @return string */ public function actionSendorder() { $session = Yii::$app->session; $session->open(); $model = new Orders(); $query = new Query(); if ($model->load(Yii::$app->request->post()) && $model->validate()) { $model->total_sum = $_SESSION['total_price']; $model->date = date('d-m-Y'); $model->time = Yii::$app->getFormatter()->asTime(time()); $id = array_keys($_SESSION['cart']); $model->complite = 0; for ($i = 0; $i < count($id); $i++) { $products[] = $query->select('title')->from('data')->where(['id' => $id[$i]])->one(); $products[$i]['quantity'] = $_SESSION['cart'][$id[$i]]['quant']; } $model->adres = strip_tags($model->adres); $model->product = serialize($products); $model->save(); // $session->destroy(); ////// $mrh_login = Yii::$app->params['mrh_login']; $mrh_pass1 = Yii::$app->params['mrh_pass1']; $inv_id = $model->id; $inv_desc = Yii::$app->params['inv_desc']; $out_summ = $model->total_sum; $crc = md5("{$mrh_login}:{$out_summ}:{$inv_id}:{$mrh_pass1}"); return $this->render('pay', ['name' => $model->name, 'out_summ' => $out_summ, 'mrh_login' => $mrh_login, 'inv_id' => $inv_id, 'inv_desc' => $inv_desc, 'crc' => $crc]); } else { return $this->render('error', ['message' => "Ошибка приложения!"]); } }
public function search_details($params) { $p['store_id'] = isset($params['store_id']) ? $params['store_id'] : 109; $p['detailnumber'] = isset($params['article']) ? $params['article'] : ''; $query = new Yii\db\Query(); return $query->from('finddetails')->where($p)->all(); }
public function getPhotos() { $query = new Query(); $query->select('id, name, path')->from('{{%home_photo}}')->where('album_id=:id', [':id' => $this->id]); $photos = Tools::Pagination($query); return ['photos' => $photos['result'], 'pages' => $photos['pages']]; }
/** * @return string */ public function actionIndex() { $this->layout = "bootstrap"; $query = new Query(); $query_advert = $query->from('advert')->orderBy('id desc'); $command = $query_advert->limit(5); $result_general = $command->all(); $count_general = $command->count(); $featured = $query_advert->limit(15)->all(); $recommend_query = $query_advert->where('recommend = 1')->limit(5); $recommend = $recommend_query->all(); $recommend_count = $recommend_query->count(); return $this->render('index', ['result_general' => $result_general, 'count_general' => $count_general, 'featured' => $featured, 'recommend' => $recommend, 'recommend_count' => $recommend_count]); /* $command = $query->from('advert')->orderBy('id desc')->limit(5); $result_general = $command->all(); $count_general = $command->count(); return $this->render('index', ['result_general' => $result_general, 'count_general' => $count_general]); */ //$this->layout = "inner"; /* $locator = \Yii::$app->locator; $cache = $locator->cache; $cache->set('test', 1); print $cache->get('test'); */ //return $this->render('index'); }
/** * setup search function for filtering and sorting * based on fullName field */ public function search($params, Query $query) { $dataProvider = new ActiveDataProvider(['query' => $query]); /** * Setup your sorting attributes * Note: This is setup before the $this->load($params) * statement below */ $dataProvider->setSort(['attributes' => ['action' => ['label' => T::t('Action'), 'asc' => ['action' => SORT_ASC], 'desc' => ['action' => SORT_DESC], 'default' => SORT_ASC], 'created' => ['label' => T::t('Date'), 'asc' => ['created' => SORT_ASC, 'action' => SORT_ASC], 'desc' => ['created' => SORT_DESC, 'action' => SORT_ASC], 'default' => SORT_DESC], 'task_price' => ['label' => T::t('Reward'), 'asc' => ['task_price' => SORT_ASC, 'action' => SORT_ASC], 'desc' => ['task_price' => SORT_DESC, 'action' => SORT_ASC], 'default' => SORT_DESC]]]); if (!($this->load($params) && $this->validate())) { return $dataProvider; } if (isset($params[$this->formName()])) { foreach ($params[$this->formName()] as $prop => $propValue) { if ("" != $propValue && property_exists($this, $prop)) { /* map objet_id to type */ if (in_array($prop, ['object_id', 'task_price'])) { $query->andWhere(sprintf('%s=%d', $prop, $this->{$prop})); } else { $query->andWhere(sprintf('%s LIKE "%%%s%%"', $prop, $this->{$prop})); } } } } return $dataProvider; }
public function getAllCas() { $query = new Query(); $query->select('num,cpt,sumnote,year')->from('aggreg_cas_weekly')->orderBy('num ASC'); $data = $query->all(); return $this->arrayToWeekCas($data); }
public function getPsychologistProblemsList($psychologistId) { $query = new Query(); $query->select('problems.*')->from('problems')->join('left outer join', 'psychologist_problems', 'problems.id = psychologist_problems.problem_id and psychologist_problems.psychologist_id= ' . $psychologistId)->orderBy('problems.id'); return $query->all(); }
public function getIdByNumTitre($num, $titre) { $query = new Query(); $query->select('id')->from('annales_track')->where('num = ' . $num . ' AND titre LIKE "' . $titre . '"'); $data = $query->all(); return $data[0]['id']; }
public static function getUsersModulesList() { $query = new Query(); $query->select('modules.*,users_modules.user_id as active')->from('modules')->join('left join', 'users_modules', 'modules.id = users_modules.module_id and users_modules.user_id= ' . Yii::$app->user->id)->orderBy('modules.id'); return $query->all(); }
/** * @inheritdoc */ public function actionAddHint() { Yii::$app->response->format = Response::FORMAT_JSON; $id = (int) Yii::$app->request->post('id'); if (Yii::$app->user->isGuest || $this->module->storage === Hint::TYPE_COOKIE) { $tooltips = Yii::$app->request->cookies->getValue($this->module->cookieName); if (!is_array($tooltips)) { $tooltips = []; } if (!isset($tooltips[$id])) { $tooltips[$id] = 1; $options['name'] = $this->module->cookieName; $options['value'] = $tooltips; $options['expire'] = time() + 86400 * 365; $cookie = new \yii\web\Cookie($options); Yii::$app->response->cookies->add($cookie); } } else { $query = new Query(); $res = $query->from($this->module->userTooltipTable)->where(['user_id' => Yii::$app->getUser()->getId(), 'source_message_id' => $id])->exists(); if (!$res) { Yii::$app->db->createCommand()->insert($this->module->userTooltipTable, ['user_id' => Yii::$app->getUser()->getId(), 'source_message_id' => $id])->execute(); } } return ['r' => 1]; }
public static function getVideosCategories($videoId) { $query = new Query(); $query->select('video_categories.*,video_categories_bind.video_id as active')->from('video_categories')->join('left outer join', 'video_categories_bind', 'video_categories.id = video_categories_bind.category_id and video_categories_bind.video_id= ' . $videoId)->orderBy('video_categories.id'); return $query->all(); }
/** * Получает список страниц для всплывающего окна * @return type */ public function GetPagesList() { $query = new Query(); $query->select(['p.name', 'm.name as module'])->join("LEFT JOIN", "module as m", "m.id = p.module")->from("backend__pages as p")->where(["p.visible" => 1, "p.active" => 1]); $result = $query->all(); return $result; }
public function actionIndex() { $query = new Query(); $query->select('*')->from('auth_item')->leftJoin('auth_item_child', 'auth_item.name=auth_item_child.child')->where(['auth_item.type' => 1]); $models = $query->all(); return $this->render('index', ['models' => $models]); }
/** * @inheritdoc */ public function behaviors() { return ['access' => ['class' => AccessControl::className(), 'only' => ['profile', 'return-to-edit', 'profile-to-pdf', 'spec-list', 'spec-items', 'agreement'], 'rules' => [['actions' => ['profile', 'return-to-edit', 'profile-to-pdf', 'spec-list', 'spec-items', 'agreement'], 'allow' => true, 'roles' => ['@']]]], 'verbs' => ['class' => VerbFilter::className(), 'actions' => ['return-to-edit' => ['post']]], ['class' => \yii\filters\HttpCache::className(), 'only' => ['view'], 'lastModified' => function ($action, $params) { $q = new \yii\db\Query(); return $q->from('profile')->max('updated_at'); }]]; }
public function actionTraining($id) { $q = new Query(); $q->from('train')->where('HeroId=:id', array(':id' => $id)); $training = $q->all(); return json_encode($training); }
/** * @inheritdoc */ public function writeSession($id, $data) { // exception must be caught in session write handler // http://us.php.net/manual/en/function.session-set-save-handler.php try { $userId = new Expression('NULL'); if (!Yii::$app->user->isGuest) { $userId = Yii::$app->user->id; } $expire = time() + $this->getTimeout(); $query = new Query(); $exists = $query->select(['id'])->from($this->sessionTable)->where(['id' => $id])->createCommand($this->db)->queryScalar(); if ($exists === false) { $this->db->createCommand()->insert($this->sessionTable, ['id' => $id, 'data' => $data, 'expire' => $expire, 'user_id' => $userId])->execute(); } else { $this->db->createCommand()->update($this->sessionTable, ['data' => $data, 'expire' => $expire, 'user_id' => $userId], ['id' => $id])->execute(); } } catch (\Exception $e) { $exception = ErrorHandler::convertExceptionToString($e); // its too late to use Yii logging here error_log($exception); echo $exception; return false; } return true; }
/** * @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 actionIndex($id) { $session = \Yii::$app->session; $request = \Yii::$app->request; $donateForm = new DonateForm(); $buyForm = new BuyForm(); $supportForm = new SupportForm(); if ($donateForm->load($request->post()) && $donateForm->submit()) { $session->set('donate', $request->post('DonateForm')); return $this->redirect('@web/index.php?r=support/donate'); } else { if ($buyForm->load($request->post()) && $buyForm->submit()) { $session->set('bought', $request->post('BuyForm')); return $this->redirect('@web/index.php?r=support/buy'); } else { if ($supportForm->load($request->post()) && $supportForm->submit()) { $session->set('motive', $request->post('SupportForm')); return $this->redirect('@web/index.php?r=support/motivation'); } } } $query = new Query(); // compose the query $query->from('hero')->where('Hid=:id', array(':id' => $id)); $hero = $query->one(); $session->set('hero', $hero); return $this->render('support', array('hero' => $hero)); }
public function actionPermissions($name) { $role = $this->auth->getRoles(); $roles = array_keys($role); $query = new Query(); $query->select('*')->from('auth_item')->leftJoin('auth_item_child', ['and', 'auth_item.name=auth_item_child.child', 'auth_item_child.parent not in (\'' . implode("','", $roles) . '\')'])->where(['auth_item.type' => 2])->andWhere(['not like', 'name', '/']); $models = $query->all(); $targetHasModels = $this->auth->getPermissionsByRole($name); $targetHasModels = array_keys($targetHasModels); //取用户所有权限的集合 $hasModel = []; foreach ($roles as $rkey => $subRole) { $subPermission = $this->auth->getPermissionsByRole($subRole); $hasModel = array_merge($hasModel, $subPermission); } $hasModels = array_keys($hasModel); $hasModelsTree = []; foreach ($models as $key => $model) { if (in_array($model['name'], $hasModels)) { array_push($hasModelsTree, $model); } else { $subModels = $this->getSubModels($model, $models); foreach ($subModels as $subKey => $subModel) { if (in_array($subModel['name'], $hasModels)) { array_push($hasModelsTree, $model); } } } } return $this->render('permissions', ['models' => $hasModelsTree, 'hasModels' => $targetHasModels]); }
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]; }
/** * @param string $method * @return array * Получаем выборку данных по запросу */ public function soap($method) { $requestData = $this->getData(); $query = new Query(); $result = $query->from('finddetails')->where($requestData)->all(); return $result; }
public function init() { parent::init(); if ($this->page == 'index') { if ($this->cat == 'all' or $this->cat == null) { $query = Logotypes::find()->limit(15); } else { $query_cat = new Query(); $query_cat->select('id')->from('category')->where(['name' => $this->cat]); $cat_id = $query_cat->all()[0]['id']; $query = Logotypes::find()->limit(15)->where(['category' => $cat_id]); } $logos = $query->all(); shuffle($logos); } else { if ($this->cat == 'all' or $this->cat == null) { $query = Logotypes::find(); } else { $query_cat = new Query(); $query_cat->select('id')->from('category')->where(['name' => $this->cat]); $cat_id = $query_cat->all()[0]['id']; $query = Logotypes::find()->where(['category' => $cat_id]); } $countQuery = clone $query; $pages = new Pagination(['totalCount' => $countQuery->count(), 'pageSize' => 15]); $logos = $query->offset($pages->offset)->limit($pages->limit)->all(); $this->pages = $pages; } $this->logos = $logos; }
public static function getCustomFieldsArray($tour_id) { $query = new Query(); $query->from(CustomFields::tableName())->where([CustomFields::FIELD_TOUR_ID => $tour_id]); $result = $query->all(); return $result; }
public function findAllAuthors() { $query = new Query(); $authorsArrayDataProvider = new ArrayDataProvider(['allModels' => $query->select(["CONCAT(firstname, ' ', lastname) AS full_name", 'id'])->from('authors')->all(), 'pagination' => false, 'key' => 'id']); $authors = $authorsArrayDataProvider->allModels; return $authors; }
public function init() { parent::init(); $db = Instance::ensure($this->db, Connection::className()); $query = new Query(); $this->ticket = $query->select(['*'])->from($this->table)->createCommand($db)->queryAll(); }