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