Sets the FROM part of the query.
public from ( string | array $tables ) | ||
$tables | string | array | the table(s) to be selected from. This can be either a string (e.g. `'user'`) or an array (e.g. `['user', 'profile']`) specifying one or several table names. Table names can contain schema prefixes (e.g. `'public.user'`) and/or table aliases (e.g. `'user u'`). The method will automatically quote the table names unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). When the tables are specified as an array, you may also use the array keys as the table aliases (if a table does not need alias, do not use a string key). Use a Query object to represent a sub-query. In this case, the corresponding array key will be used as the alias for the sub-query. Here are some examples: ```php // SELECT * FROM `user` `u`, `profile`; $query = (new \yii\db\Query)->from(['u' => 'user', 'profile']); // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; $subquery = (new \yii\db\Query)->from('user')->where(['active' => true]) $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); // subquery can also be a string with plain SQL wrapped in parenthesis // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; $subquery = "(SELECT * FROM `user` WHERE `active` = 1)"; $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); ``` |
public function actionIndex() { $query = new Query(); /*$department = $query->select(['department_id','department_name']) ->from('department') ->all();*/ //$department = $query->from('department')->all(); //เทียบได้กับโดยไม่ต้องใส่ select ใส่ from เลย //yii จะใส่ select ให้ SELECT * FROM department /*$search ='กลุ่ม' $department = $query->select(['department_id','department_name']) ->from('department') ->where([ '>like','department_id',$search ]) ->all();*/ //order by อีกหนึ่งวิธี //$department = $query->from('department_id')->orderBy('department_id DESC')->all(); $department = $query->from('department')->orderBy(['department_name' => SORT_ASC])->all(); $count = $query->from('department')->count(); $max = $query->from('department')->max('department_id'); $sum = $query->from('department')->sum('department_id'); // echo sql $sql = $query->from('department')->orderBy(['department_name' => SORT_ASC])->createCommand(); return $this->render('index', ['departments' => $department, 'count' => $count, 'max' => $max, 'sum' => $sum, 'sql' => $sql]); }
/** * Prepare the DB query * * @return $this */ public function prepare() { // Throw a exception if a required parameter is not defined $this->verifyRequiredParameters(); // Build the query $this->query = new Query(); $this->query->select('*'); $this->query->from($this->getTableName()); $this->query->where('app_id=:appId', [':appId' => $this->appId]); return $this; }
public function run() { $str = ''; $query = new Query(); $res = $query->select('sum(click) as click')->from('blog_post')->one(); $click = $res['click']; $str .= '<div class="site-stat">点击数量:' . $click . '</div>'; $postCount = $query->from('blog_post')->count(); $str .= '<div class="site-stat">文章数量:' . $postCount . '</div>'; $commentCount = $query->from('blog_comment')->count(); $str .= '<div class="site-stat">评论数量:' . $commentCount . '</div>'; return $this->render('portal', ['title' => $this->title, 'content' => $str]); }
/** * @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'); }
public static function getPostByTags() { $query = new Query(); $query->from('tags')->join('JOIN', 'posts_tags', 'posts_tags.tag_id = tags.id')->join('JOIN', 'posts', 'posts_tags.post_id = posts.id')->where(['tags.name' => $_GET['tags']]); $rows = $query->all(); return $rows; }
/** * Lists all Job models. * @return mixed */ public function actionIndex() { $model = new Job(); // This is used to search/filter organizations $dataProvider = null; if (isset($_GET['user_id'])) { $user_id = $_GET['user_id']; if ($user_id !== null) { $query = new Query(); $query->from(Job::tableName()); $query->where(['user_id' => $user_id]); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]); } } else { $dataProvider = new ActiveDataProvider(['query' => Job::find(), 'pagination' => ['pageSize' => 5]]); } if ($model->load(Yii::$app->request->post())) { $query = new Query(); $query->from(Job::tableName()); if (!is_null($model->employment_type) && is_array($model->employment_type)) { $query->where(['employment_type' => array_map('intval', $model->employment_type)]); } if (!is_null($model->job_type) && is_array($model->job_type)) { $query->where(['job_type' => array_map('intval', $model->job_type)]); } if (!is_null($model->work_domain) && is_array($model->work_domain)) { $query->andWhere(['work_domain' => array_map('intval', $model->work_domain)]); } if (isset($_GET['user_id'])) { $query->andWhere(['user_id' => $_GET['user_id']]); } $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]); } return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]); }
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 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]; }
/** * Initializes the DbMessageSource component. */ public function init() { parent::init(); if ($this->autoInsert) { $this->on(static::EVENT_MISSING_TRANSLATION, function ($event) { if (!isset($this->messagesId[$event->message])) { $query = new Query(); $id = $query->select("id")->from($this->sourceMessageTable)->where(['category' => $event->category, 'message' => $event->message])->scalar($this->db); if ($id === false) { $this->db->createCommand()->insert($this->sourceMessageTable, ['category' => $event->category, 'message' => $event->message])->execute(); $id = $this->db->lastInsertID; } /* @var $i18n I18N */ $i18n = Yii::$app->i18n; $languages = $i18n->getLanguages(); foreach ($languages as $language_id => $language) { $query = new Query(); $exists = $query->from($this->messageTable)->where(['id' => $id, 'language_id' => $language_id])->exists($this->db); if (!$exists) { $this->db->createCommand()->insert($this->messageTable, ['id' => $id, 'language_id' => $language_id, 'translation' => ''])->execute(); } } $this->messagesId[$event->message] = $id; } $event->translatedMessage = $event->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 actionRegion($id = null) { if ($id) { $model = Geo::getOne($id); //получчаем количество розничных точек в регионе $col_apteki = Apteki::find()->where(['region_id' => $id])->count(); //получаем количество Юр.лиц $col_ur_l = RegionUrL::find()->where(['id_reg' => $id])->count(); $db = new Query(); $db->from('region_ur_l'); $db->InnerJoin('ur_l', 'id_ur = ur_l.id'); $db->andWhere(['=', 'id_reg', $id]); $db->andWhere(['=', 'ur_l.regional_id', $model['regional_id']]); $col_ur_l_regpred = $db->count(); // $col_ur_l_regpred = RegionUrL::find()->where(['id_reg' => $id])->andWhere(['$model'=>])->count(); } else { $model = Geo::getAll(); } $region = new Geo(); if ($_POST['Geo']) { $model->attributes = $_POST['Geo']; if ($model->validate() && $model->save()) { return $this->render('/edit/region', ['model' => $model, 'region' => $region, 'ok' => 1, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]); } } if ($id) { return $this->render('/edit/region', ['model' => $model, 'col_apteki' => $col_apteki, 'col_ur_l' => $col_ur_l, 'col_ur_l_regpred' => $col_ur_l_regpred]); } else { return $this->render('region', ['model' => $model]); } }
public function actionIndex($date = null) { // $apteki=LogReestr::find()->where(['resstr' => 'apteki'])->all(); if (!$date) { $date = date('Y-m'); } $db = new Query(); $db->from(LogReestr::tableName()); $db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.action', 'log_reestr.name', 'log_reestr.ur_l_id', 'log_reestr.id_resstr', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']); $db->where(['=', 'resstr', 'apteki']); $db->leftJoin('users', "users.id = log_reestr.user"); $db->orderBy('log_reestr.created_at DESC'); $date_search = $date . '%'; $db->andWhere(['like', 'log_reestr.created_at', $date_search, false]); $apteki = $db->all(); // $apteki_count = $db->count(); $db = new Query(); $db->from(LogReestr::tableName()); $db->select(['log_reestr.created_at', 'log_reestr.address', 'log_reestr.resstr', 'log_reestr.id_resstr', 'log_reestr.name', 'log_reestr.action', 'log_reestr.change', 'users.username', 'log_reestr.id_resstr']); $db->where(['=', 'resstr', 'ur_l']); $db->leftJoin('users', "users.id = log_reestr.user"); $db->andWhere(['like', 'log_reestr.created_at', $date_search, false]); $db->orderBy('log_reestr.created_at DESC'); $ur_l = $db->all(); // $ur_l_count = $db->count(); $statm = \Yii::$app->db->createCommand("SELECT users.username , COUNT(*) as count FROM log_reestr INNER JOIN users ON users.id=log_reestr.user\n where log_reestr.created_at like '" . $date . "%'\n GROUP BY USER order by count DESC"); $stat = $statm->queryAll(); $statAllm = \Yii::$app->db->createCommand("SELECT COUNT(*) as count FROM log_reestr\n where log_reestr.created_at like '" . $date . "%' "); $statAll = $statAllm->queryOne(); return $this->render('index', ['apteki' => $apteki, 'ur_l' => $ur_l, 'date' => $date, 'stat' => $stat, 'statAll' => $statAll]); }
public function actionCurrent_list() { $query = new Query(); $list_id = Yii::$app->request->post('listID'); $listInfo = $query->from('user_list')->select('*')->where(['userId' => Yii::$app->user->id, 'list_id' => $list_id])->all(); echo json_encode($listInfo); }
public function retrieve($className, $primaryKey) { $tableName = call_user_func([$className, "tableName"]); $current = $className::find()->where(['id' => $primaryKey])->asArray()->one(); $query = new Query(); $query->select(['field_name', 'old_value', 'event', 'action_uuid', 'created_at']); $query->from($this->tableName); $query->where(['field_id' => $primaryKey, 'table_name' => $tableName]); $query->orderBy(['created_at' => SORT_ASC]); $changes = []; foreach ($query->all() as $element) { $uuid = $element['action_uuid']; if (!isset($changes[$uuid])) { $changes[$uuid] = $current; } $changes[$uuid][$element['field_name']] = $element['old_value']; $current = $changes[$uuid]; } $models = array_map(function ($element) use($className) { $model = $className::instantiate($element); $className::populateRecord($model, $element); return $model; }, $changes); return new ArrayDataProvider(['allModels' => array_values($models)]); }
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)); }
/** * Restores record from audit * * @return Response last visited page */ public function actionRestore() { $post = Yii::$app->request->post(); $query = new Query(); $audit = $query->from("audits.{$post['table']}")->where('audit_id = :audit_id', ['audit_id' => $post['audit_id']])->one(); $this->setCurrentModel($post['table']); $criteria = []; /** @var \yii\db\ActiveRecord $model */ $model = $this->_currentModel; foreach ($model->primaryKey() as $primaryKey) { $criteria[$primaryKey] = $audit[$primaryKey]; } foreach ($this->module->tables[$post['table']]['updateSkip'] as $column) { if (isset($audit[$column])) { unset($audit[$column]); } } /** @var \yii\db\ActiveRecord $record */ $record = $model->findOne($criteria); $record->setAttributes($audit, false); if ($record->save()) { Yii::$app->session->setFlash('success', Yii::t('app', 'Record has been restored.')); } else { Yii::$app->session->setFlash('danger', Yii::t('app', 'Failed to restore record.')); } return $this->redirect(Yii::$app->request->referrer); }
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; }
/** * @param string $method * @return array * Получаем выборку данных по запросу */ public function soap($method) { $requestData = $this->getData(); $query = new Query(); $result = $query->from('finddetails')->where($requestData)->all(); return $result; }
/** * @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']); }
/** * @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 behaviors() { return ['access' => ['class' => AccessControl::className(), 'only' => ['index', 'view', 'create', 'update', 'delete'], 'rules' => [['actions' => ['index', 'view', 'create', 'update', 'delete'], 'allow' => true, 'roles' => ['@']]]], 'verbs' => ['class' => VerbFilter::className(), 'actions' => ['delete' => ['post'], 'send-message' => ['post']]], ['class' => 'yii\\filters\\HttpCache', 'only' => ['index'], 'lastModified' => function ($action, $params) { $q = new Query(); $res = $q->from(User::tableName())->max('create_date'); return strtotime($res); }]]; }
public function GetItem($id = 0) { $query = new Query(); $query->from($this->table); $query->where(["id" => $id]); $result = $query->one(); return $result; }
public static function getApteki($id) { $db = new Query(); //$db->select('id','name'); $db->where(['=', 'ur_l_id', $id]); $db->from('apteki'); return $db->all(); }
public static function getRegion() { $db = new yii\db\Query(); $db->select('id', 'name'); $db->from('region'); $db->orderBy('name'); return $db->all(); }
public function getRewards() { $storeId = VendorRegister::find()->select(['vendor_unique_id'])->where(['uid' => Yii::$app->user->id]); $query = new Query(); $query->from('rewards_main')->where(['storeId' => $storeId]); $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 25]]); return $dataProvider; }
public static function getPopularVideos() { $query = new Query(); $query->select('videos.*,(SELECT COUNT(*) FROM videos_comments WHERE videos.id = videos_comments.video_id) as commentsCount'); $query->from('videos'); $query->orderBy('commentsCount DESC'); $query->limit('4'); return $query->all(); }
public function actionIndex() { $this->layout = "bootstrap"; $query = new Query(); $command = $query->from('advert')->orderBy('idadvert desc')->limit(5); $result_general = $command->all(); $count_general = $command->count(); return $this->render('index', ['result_general' => $result_general, 'count_general' => $count_general]); }
public static function getMostActive() { $query = new Query(); $query->select('profile.*,(SELECT COUNT(*) FROM discussion_post_reply WHERE profile.user_id = discussion_post_reply.user_id) as repliesCount'); $query->from('profile'); $query->orderBy('repliesCount DESC'); $query->limit('4'); return $query->all(); }
public function actionSuccess($id) { $query = new Query(); // compose the query $query->from('event')->where('Eid=:id', array(':id' => $id)); // build and execute the query $detail = $query->one(); return $this->render('success', array('detail' => $detail)); }
public static function getPopularPosts() { $query = new Query(); $query->select('article.*,(SELECT COUNT(*) FROM article_comments WHERE article.id = article_comments.article_id) as commentsCount'); $query->from('article'); $query->orderBy('commentsCount DESC'); $query->limit('4'); return $query->all(); }