PHP Query::where方法代码示例

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

 /**
  * 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 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($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 getAll($where = null, $params = null)
 {
     $query = new Query();
     $query->select('*')->from('forum_post');
     if ($where != null) {
         if (is_string($where)) {
             $query->where($where);
         } else {
             throw new Exception('Erreur : la clause Where doit être une chaîne de caractères');
         }
     }
     if ($params != null) {
         foreach ($params as $k => $v) {
             if ($k == 'orderBy') {
                 $query->orderBy = $v;
             }
             if ($k == 'limit') {
                 $query->limit = intval($v);
             }
             if ($k == 'offset') {
                 $query->offset = intval($v);
             }
         }
     }
     //var_dump($query);
     $items = $query->all();
     return $items;
 }

 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 function getQuiz($id)
 {
     $query = new Query();
     $query->select('quiz_questions.id as qid,quiz_questions.*,questions_answers.*')->from('quiz_questions');
     $query->join('join', 'questions_answers', 'quiz_questions.id=question_id');
     $query->where('quiz_questions.quiz_id=' . $id);
     return $this->_group_by($query->all(), 'qid');
 }

 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);
     if (!$masterTable) {
         $NullPages = new Pagination(['pageParam' => 'pageCurrent', 'pageSizeParam' => 'pageSize', 'totalCount' => 0, 'defaultPageSize' => 20]);
         return $this->render('index', ['models' => [], 'pages' => $NullPages, 'theadArray' => []]);
     }
     $query = new Query();
     $query->from($masterTable['tabName']);
     $query->select($masterTable['tabName'] . '.' . 'id');
     foreach ($tables as $table) {
         if ($table['isMain'] != '1') {
             $query->leftJoin($table['tabName'], $table['condition']);
         }
     }
     //排序字段
     $attributes = [];
     //查询条件
     $where = [];
     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']) {
             $where[$thead['reName']] = $request->get($thead['reName']);
         } elseif ($thead['isQuery'] == '1') {
             $where[$thead['fieldName']] = $request->get($thead['fieldName']);
         }
     }
     $query->where($where);
     $pages = new Pagination(['pageParam' => 'pageCurrent', 'pageSizeParam' => 'pageSize', 'defaultPageSize' => 20]);
     $sort = new WetSort(['attributes' => $attributes]);
     $provider = new ActiveDataProvider(['query' => $query, 'pagination' => $pages, 'sort' => $sort]);
     $models = $provider->getModels();
     return $this->render('index', ['models' => $models, 'pages' => $pages, 'theadArray' => $theadArray]);
 }

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

 /**
  * Возвращает кол-во элементов
  * @param array $where
  * @return int
  */
 public function GetCountItems($where = [])
 {
     $query = new Query();
     $query->select("id");
     $query->from($this->table);
     if ($where) {
         $query->where($where);
     }
     $result = $query->all();
     return count($result);
 }

 /**
  * @param array $filters
  * @param string $select
  * @return Query
  */
 private function getWhere($filters = [], $select = '*')
 {
     $query = new Query();
     $query->select($select)->from(static::tableName());
     if (isset($filters['M08_HAN_CD']) && $filters['M08_HAN_CD']) {
         $query->where('M08_HAN_CD=:han_cd', [':han_cd' => $filters['M08_HAN_CD']]);
     }
     if (isset($filters['M08_SS_CD']) && $filters['M08_SS_CD']) {
         $query->where('M08_SS_CD=:ss_cd', [':ss_cd' => $filters['M08_SS_CD']]);
     }
     if (isset($filters['M08_JYUG_CD']) && $filters['M08_JYUG_CD']) {
         $query->where('M08_JYUG_CD=:jy_cd', [':jy_cd' => $filters['M08_JYUG_CD']]);
     }
     if (isset($filters['offset']) && $filters['offset']) {
         $query->offset($filters['offset']);
     }
     if (isset($filters['limit']) && $filters['limit']) {
         $query->limit($filters['limit']);
     }
     return $query;
 }

 public function actionEdit($id)
 {
     if (Yii::$app->request->post()) {
         Ot::Update($id);
     }
     $apteka = Ur::find()->where(['id' => $id])->one();
     $regionId = Ur::getRegionId($id);
     $db = new Query();
     $db->from('preparats');
     $db->where(['is_active' => 1, 'region_id' => $regionId]);
     $preparats = $db->all();
     return $this->render('edit', ['preparats' => $preparats, 'id_apteka' => $id, 'apteka' => $apteka]);
 }

 public function testWhere()
 {
     $query = new Query();
     $query->where('id = :id', [':id' => 1]);
     $this->assertEquals('id = :id', $query->where);
     $this->assertEquals([':id' => 1], $query->params);
     $query->andWhere('name = :name', [':name' => 'something']);
     $this->assertEquals(['and', 'id = :id', 'name = :name'], $query->where);
     $this->assertEquals([':id' => 1, ':name' => 'something'], $query->params);
     $query->orWhere('age = :age', [':age' => '30']);
     $this->assertEquals(['or', ['and', 'id = :id', 'name = :name'], 'age = :age'], $query->where);
     $this->assertEquals([':id' => 1, ':name' => 'something', ':age' => '30'], $query->params);
 }

 public static function getRegionId($id)
 {
     $db = new Query();
     $db->from('ur_l');
     $db->select('region.id');
     $db->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
     $db->InnerJoin('region', 'region_ur_l.id_reg = region.id');
     $db->where(['=', 'ur_l.id', $id]);
     $data = $db->all();
     foreach ($data as $rid) {
         $data2[] = $rid[id];
     }
     return $data2;
 }