PHP Query::andWhere方法代码示例

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

 protected function applyContextFilters()
 {
     if (isset($this->topicId)) {
         $this->query->joinWith('source.topics');
         $this->query->andWhere(['scoopit_source_topic.topic_id' => $this->topicId]);
     }
 }

 /**
  * 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]);
 }

 /**
  * 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 static function getSalesData($duration)
 {
     $dates = [];
     $transactions = [];
     $transactionskv = [];
     $amount = [];
     $statusPaid = [Order::STATUS_PAID, Order::STATUS_DELIVERED];
     $statusPaid = join(",", $statusPaid);
     $orderTable = CartTables::TABLE_ORDER;
     $txnTable = CartTables::TABLE_ORDER_TRANSACTION;
     $query = new Query();
     $query->select(["date(`{$txnTable}`.`createdAt`) as date", 'sum( amount ) as amount']);
     $query->from($txnTable);
     $query->join('LEFT JOIN', $orderTable, "orderId = `{$orderTable}`.`id`");
     $query->where(" {$orderTable}.status in ( {$statusPaid} )");
     switch ($duration) {
         case 0:
             // Current Week - Starting with Sun
             $dates = DateUtil::getCurrentWeekDates();
             $transactions = $query->andWhere("YEARWEEK( `{$txnTable}`.`createdAt` ) = YEARWEEK( CURRENT_DATE ) ")->groupBy(['date'])->all();
             break;
         case 1:
             // Last Week - Starting with Sun
             $dates = DateUtil::getLastWeekDates();
             $transactions = $query->andWhere("YEARWEEK( `{$txnTable}`.`createdAt` ) = YEARWEEK( CURRENT_DATE - INTERVAL 7 DAY ) ")->groupBy(['date'])->all();
             break;
         case 2:
             // This Month
             $dates = DateUtil::getCurrentMonthDates();
             $transactions = $query->andWhere("MONTH( `{$txnTable}`.`createdAt` ) = ( MONTH( NOW() ) ) AND YEAR( `{$txnTable}`.`createdAt` ) = YEAR( NOW() ) ")->groupBy(['date'])->all();
             break;
         case 3:
             // Last Month
             $dates = DateUtil::getLastMonthDates();
             $transactions = $query->andWhere("MONTH( `{$txnTable}`.`createdAt` ) = ( MONTH( NOW() ) - 1 ) AND YEAR( `{$txnTable}`.`createdAt` ) = YEAR( NOW() ) ")->groupBy(['date'])->all();
             break;
     }
     foreach ($transactions as $transaction) {
         $transactionskv[$transaction['date']] = $transaction['amount'];
     }
     foreach ($dates as $date) {
         if (isset($transactionskv[$date])) {
             $amount[] = $transactionskv[$date];
         } else {
             $amount[] = 0;
         }
     }
     return $amount;
 }

 public function actionIndex()
 {
     $request = Yii::$app->request;
     $menuId = 31;
     $theadArray = QueryField::find()->where(['menuId' => $menuId])->asArray()->with('queryTable')->all();
     $tables = QueryTable::find()->where(['menuId' => $menuId])->asArray()->all();
     $masterTable = $this->getMasterTable($tables);
     $query = new Query();
     $query->from($masterTable['dbName'] . '.' . $masterTable['tabName']);
     $query->select($masterTable['tabName'] . '.' . 'id');
     foreach ($tables as $table) {
         if ($table['isMain'] != '1') {
             $query->leftJoin($table['dbName'] . '.' . $table['tabName'], $table['condition']);
         }
     }
     //排序字段
     $attributes = [];
     foreach ($theadArray as $thead) {
         if ($thead['queryTable']['reName']) {
             $addSelect = $thead['queryTable']['reName'];
         } else {
             $addSelect = $thead['queryTable']['tabName'];
         }
         $addSelect = $addSelect . '.' . $thead['fieldName'];
         if ($thead['makeTbName'] != 1) {
             $addSelect = $thead['fieldName'];
         }
         if ($thead['reName']) {
             //组装排序字段
             array_push($attributes, $thead['reName']);
             //查询字段
             $addSelect = $addSelect . ' ' . 'as' . ' ' . $thead['reName'];
         } else {
             array_push($attributes, $thead['fieldName']);
         }
         $query->addSelect($addSelect);
         //组装查询条件
         if ($thead['isQuery'] == '1' && $thead['reName'] && $request->get($thead['reName'])) {
             $query->andWhere(['like', $thead['reName'], $request->get($thead['reName'])]);
         } elseif ($thead['isQuery'] == '1' && $request->get($thead['fieldName'])) {
             $query->andWhere(['like', $thead['fieldName'], $request->get($thead['fieldName'])]);
         }
     }
     $sort = new WetSort(['attributes' => $attributes]);
     $pages = new Pagination(['pageParam' => 'pageCurrent', 'pageSizeParam' => 'pageSize', 'defaultPageSize' => 20]);
     $provider = new ActiveDataProvider(['query' => $query, 'pagination' => $pages, 'sort' => $sort]);
     $models = $provider->getModels();
     return $this->render('index', ['models' => $models, 'pages' => $pages]);
 }

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

 /**
  * 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 Edit($id_apteka, $id_preparat)
 {
     $query = new Query();
     $query->from('preparats_ansver');
     $query->where(['=', 'id_apteka', $id_apteka]);
     $query->andWhere(['=', 'id_o', $id_preparat]);
     return $query->one();
 }

 public function beforeInsert()
 {
     if (!is_null($this->where)) {
         if (!is_array($this->where)) {
             $this->where = array($this->where);
         }
         foreach ($this->where as $where) {
             $this->query->andWhere([$where => $this->owner->{$where}]);
         }
     }
     $last = $this->query->orderBy([$this->orderAttribute => SORT_DESC])->limit(1)->one();
     if ($last === null) {
         $this->owner->{$this->orderAttribute} = 1;
     } else {
         $this->owner->{$this->orderAttribute} = $last->{$this->orderAttribute} + 1;
     }
 }

 /**
  * Lists all Organization models.
  * @return mixed
  */
 public function actionIndex()
 {
     $model = new Organization();
     // 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(Organization::tableName());
             $query->where(['user_id' => $user_id]);
             $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]);
         }
     } else {
         if (isset($_GET['search_keyword']) && $_GET['search_keyword'] !== null && strlen($_GET['search_keyword']) > 0 || isset($_GET['search_location']) && $_GET['search_location'] !== null && strlen($_GET['search_location']) > 0) {
             $search_keyword = $_GET['search_keyword'];
             if ($search_keyword !== null) {
                 $query = new Query();
                 $query->from(Organization::tableName());
                 $query->where(['name' => $search_keyword]);
                 $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]);
             }
         } else {
             $dataProvider = new ActiveDataProvider(['query' => Organization::find(), 'pagination' => ['pageSize' => 5]]);
         }
     }
     if ($model->load(Yii::$app->request->post())) {
         $query = new Query();
         $query->from(Organization::tableName());
         if (!is_null($model->org_type) && is_array($model->org_type)) {
             $query->where(['org_type' => array_map('intval', $model->org_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']]);
         }
         if (isset($_GET['search_keyword']) && $_GET['search_keyword'] !== null && strlen($_GET['search_keyword']) > 0 || isset($_GET['search_location']) && $_GET['search_location'] !== null && strlen($_GET['search_location']) > 0) {
             $query->andWhere(['name' => $_GET['search_keyword']]);
         }
         $dataProvider = new ActiveDataProvider(['query' => $query, 'pagination' => ['pageSize' => 5]]);
     }
     return $this->render('index', ['model' => $model, 'dataProvider' => $dataProvider]);
 }

 public function getPermissionsByRole($role)
 {
     $query = new Query();
     $query->select(['p.id', 'p.category', 'p.name', 'p.description', 'p.form', 'p.default_value', 'p.rule', 'p.sort_num', 'r.role', 'r.value']);
     $query->from(['p' => $this->permissionTable, 'r' => $this->relationTable]);
     $query->where('r.permission=p.id');
     $query->andWhere(['r.role' => $role]);
     $rows = $query->all();
     return $this->convertPermissionValue($rows);
 }

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

 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 static function queryDatas($tableName, $conditions, $offset, $size, $orderby, $isGetTotal = false)
 {
     $query = new Query();
     $query->select('*')->from($tableName);
     if (!empty($conditions)) {
         foreach ($conditions as $name => $value) {
             if (is_array($value)) {
                 $query->andWhere($value);
             } else {
                 $query->andWhere([$name => $value]);
             }
         }
     }
     $total = 0;
     if ($isGetTotal) {
         $total = $query->count('*');
     }
     $query->offset($offset)->limit($size)->orderBy($orderby);
     $list = $query->all();
     return ['list' => $list, 'total' => $total];
 }