PHP Query::leftJoin方法代码示例

 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 static function getFullname()
 {
     $db = new Query();
     $db->from('apteki');
     $db->select(['ur_l.name', 'ur_l.id as id2', 'apteki.address', 'apteki.id']);
     $db->leftJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
     $data = $db->all();
     return $data;
 }

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

 /**
  * Lists all UrQuestions models.
  * @return mixed
  */
 public function actionIndex()
 {
     //топ аптек
     $db = new Query();
     $db->from('ur_questions');
     $db->select(['COUNT(*) AS count', 'ur_l_id', 'ur_l.name']);
     $db->leftJoin('ur_l', "ur_l.id=ur_questions.`ur_l_id`");
     $db->groupBy('ur_l_id');
     $db->orderBy('count DESC');
     $db->limit(5);
     $top = $db->all();
     $query = (new \yii\db\Query())->from('ur_questions');
     $query->select('username,ur_questions.id,ur_questions.created_at,question,ur_l.name,date_ansver');
     $query->leftJoin('ur_l', 'ur_questions.ur_l_id = ur_l.id');
     $query->leftJoin('users', 'ur_questions.user_id = users.id');
     if (\Yii::$app->user->identity->status != 1) {
         // $query->where(['ur_questions.user_id'=>\Yii::$app->user->identity->id]);
     }
     $query->orderBy(['ur_questions.id' => SORT_DESC]);
     $dataProvider = new ActiveDataProvider(['key' => 'id', 'query' => $query->from('ur_questions')]);
     return $this->render('index', ['dataProvider' => $dataProvider, 'top' => $top]);
 }

 public function ListAptekiAnsver()
 {
     $query = new Query();
     $query->select(['GROUP_CONCAT(DISTINCT(region.name)) as rname', 'COUNT(preparats_ansver.id) AS count', 'ur_l.name AS uname', 'ur_l.id']);
     $query->from('ur_l');
     $query->InnerJoin('region_ur_l', 'region_ur_l.id_ur = ur_l.id');
     $query->InnerJoin('region', 'region_ur_l.id_reg = region.id');
     $query->leftJoin('preparats_ansver', 'ur_l.id = preparats_ansver.id_apteka');
     if (Yii::$app->user->identity->status == 2) {
         //Регионалы
         $query->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]);
     }
     if (Yii::$app->user->identity->status == 3) {
         //Провизоры
         $query->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]);
     }
     $query->andFilterWhere(['=', 'farmopeka', '1']);
     $query->groupBy('ur_l.id');
     $query->orderBy('rname', 'ur_l.name');
     // $query->InnerJoin('apteki', 'preparats_ansver.id_o=preparats_ansver.id');
     return $query->all();
 }

 public function actionUsers()
 {
     $_currUser = Yii::$app->user->isGuest ? null : User::findOne(['id' => Yii::$app->user->id]);
     if (!$_currUser || 0 === $_currUser->is_admin) {
         return $this->goHome();
     }
     if ($_currUser && $_currUser->is_admin && Yii::$app->request->get('user_id') && 'markAdmin' === Yii::$app->request->get('action')) {
         $tarUser = User::findOne(['id' => intval(Yii::$app->request->get('user_id'))]);
         if ($tarUser) {
             $tarUser->is_admin = 1;
             $tarUser->save();
         }
     }
     $users = [];
     $currPage = Yii::$app->request->get('page', 0);
     $filteredAddress = Yii::$app->request->get('address', null);
     $queryUserCount = new Query();
     $queryUserCount->select('*')->from('users u');
     $queryUser = new Query();
     $queryUser->select('u.id as id, u.first_name as fname, u.last_name as lname, u.is_admin as is_admin, ' . 'u.photo as photo, u.is_complete as is_complete, avg(r.rate) as rate')->from('users u')->leftJoin('rates r', 'r.rated_id = u.id')->groupBy('id, fname, lname, is_admin, photo, is_complete')->orderBy('rate desc')->offset(intval(Yii::$app->params['driversPerPageLimit'] * $currPage))->limit(Yii::$app->params['driversPerPageLimit']);
     if ($filteredAddress) {
         $queryUser->leftJoin('rel_user_address rua', 'rua.user_id = u.id');
         $queryUser->leftJoin('addresses adr', 'adr.id = rua.address_id');
         $queryUser->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]);
         $queryUserCount->leftJoin('rel_user_address rua', 'rua.user_id = u.id');
         $queryUserCount->leftJoin('addresses adr', 'adr.id = rua.address_id');
         $queryUserCount->where('(u.user_type = :utype) AND (adr.address LIKE :address)', [':utype' => User::$_TYPE_CUSTOMER, ':address' => "%{$filteredAddress}%"]);
     } else {
         $queryUser->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]);
         $queryUserCount->where('u.user_type = :utype', [':utype' => User::$_TYPE_CUSTOMER]);
     }
     $totalCount = $queryUserCount->count();
     $context = ['is_guest' => Yii::$app->user->isGuest, 'curr_user_id' => Yii::$app->user->isGuest ? null : Yii::$app->user->id, 'is_admin' => $_currUser ? 1 === $_currUser->is_admin : false, 'is_drivers' => 0, 'total_pages' => ceil($totalCount / Yii::$app->params['driversPerPageLimit']), 'total_count' => $totalCount, 'current_page' => $currPage, 'filtered_address' => $filteredAddress, 'address_start' => Yii::$app->request->get('address_start', null), 'address_dest' => Yii::$app->request->get('address_dest', null), 'time_start' => Yii::$app->request->get('time_start', null), 'message' => Yii::$app->request->get('message', null)];
     $rows = $queryUser->all();
     foreach ($rows as $row) {
         $users[] = ['id' => $row['id'], 'first_name' => $row['fname'], 'last_name' => $row['lname'], 'is_admin' => $row['is_admin'], 'rating' => sprintf("%.2f", $row['rate']), 'photo' => $row['photo'], 'is_complete' => $row['is_complete']];
     }
     return $this->render('drivers', ['drivers' => $users, 'context' => $context]);
 }

 public function search_tip_id($params)
 {
     $query = new Query();
     $query->from('t_tovar t');
     $options = [];
     $joptions = isset($params['options']) ? $params['options'] : [];
     //        var_dump($joptions);die;
     if (is_array($joptions)) {
         foreach ($joptions as $key => $value) {
             if (!empty($key)) {
                 $options[$key] = $value;
             }
         }
     } else {
         $options = json_decode($joptions);
     }
     $n = 1;
     //        var_dump($joptions);die;
     foreach ($options as $id => $value) {
         $name = 't' . $n;
         $fname = $id;
         //            $query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=' . $id)
         $query->leftJoin('t_value ' . $name, 't.id=' . $name . '.tovar_id and ' . $name . '.param_id=:' . $name, [':' . $name => $id])->addSelect([$fname => $name . '.value_char']);
         if (!empty($value)) {
             $p = ':value_' . $n;
             $param = [$p => $value];
             //                var_dump($param,$name . '.value_char='.$p);die;
             $query->andWhere($name . '.value_char=' . $p, $param);
             //                $n=$n+1;
         }
         $n = $n + 1;
     }
     $query->leftJoin('t_price p', 'p.id_tovar=t.id and p.id_store=:store_id', [':store_id' => $params['store_id']])->addSelect('p.price,p.id_store,p.count,t.id,t.name,t.tip_id,t.category_id');
     //        $query->limit($params['page_size'])->offset(($params['page'] - 1) * $params['page_size']);
     $query->andWhere($params['where']);
     $query->orderBy($params['orderby']);
     //        var_dump($query);die;
     $dataProvider = new ActiveDataProvider(['query' => $query]);
     //        $dataProvider = new ArrayDataProvider(['allModels' => $query->all()]);
     $dataProvider->pagination->page = $params['page'] - 1;
     $dataProvider->pagination->pageSize = $params['page_size'];
     //        var_dump($dataProvider->models);die;
     //var_dump($dataProvider);die;
     return $dataProvider;
 }

 /**
  * update sensor status with input status
  * - update temperature
  * - update humidity
  * - update security mode
  * - update all sensor equipment
  */
 public function updateSensor()
 {
     $inputBin = Convert::powOf2($this->request['input_status']);
     // get all sensor status of this station
     $query = new Query();
     $query->select('s.binary_pos, s.type, st.id, st.sensor_id, st.value');
     $query->from('sensor_status st');
     $query->leftJoin('sensor s', 'st.sensor_id = s.id');
     $query->where('station_id = ' . $this->request['id']);
     $sensors = $query->all();
     if (!empty($sensors)) {
         foreach ($sensors as $sensor) {
             $vs = in_array($sensor['binary_pos'], $inputBin) ? 1 : 0;
             if ($sensor['type'] == Sensor::TYPE_CONFIGURE) {
                 Yii::$app->db->createCommand()->update('sensor_status', ['value' => $vs], ['id' => $sensor['id']])->execute();
             } else {
                 if ($sensor['type'] == Sensor::TYPE_VALUE) {
                     $value = '';
                     // if this is security mode
                     if ($sensor['sensor_id'] == Sensor::ID_SECURITY) {
                         // security mode handler
                         if ($this->request['message'] == self::MSG_ARMING) {
                             $value = 1;
                         }
                         if ($this->request['message'] == self::MSG_DISARM) {
                             $value = 0;
                         }
                         // compare with handler status
                         if (isset($this->handler['security']['status']) && $value != $this->handler['security']['status']) {
                             $value = $this->handler['security']['status'];
                             $this->sendBack = true;
                         }
                         // if security has been turn off, create an alarm
                         if ($sensor['value'] != $value) {
                             if ($value == 0) {
                                 // create turn off security mode alarm
                                 $message = 'Tat bao dong';
                             } elseif ($value == 1) {
                                 // create turn on security mode alarm
                                 $message = 'Bat bao dong';
                             }
                             $this->alarm($message);
                         }
                     }
                     // if this is temperature
                     if ($sensor['sensor_id'] == Sensor::ID_TEMPERATURE) {
                         $value = $this->request['temp'];
                     }
                     // if this is humidity
                     if ($sensor['sensor_id'] == Sensor::ID_HUMIDITY) {
                         $value = $this->request['humi'];
                     }
                     Yii::$app->db->createCommand()->update('sensor_status', ['value' => $value], ['id' => $sensor['id']])->execute();
                 }
             }
         }
     }
 }

 public static function find($module, $params = [], $page = 30)
 {
     $relation = ['country' => ['translations'], 'city' => ['country', 'country.translations', 'airports', 'airports.translations', 'translations'], 'direction' => ['iata', 'from', 'from.translations', 'from.country', 'from.country.translations', 'to', 'to.translations', 'to.country', 'to.country.translations'], 'airport' => ['translations', 'country', 'country.translations', 'city', 'city.translations'], 'airline' => []];
     if (!empty($params['relation'])) {
         $relation = array_merge($relation, $params['relation']);
         unset($params['relation']);
     }
     $model = 'common\\models\\Received' . ucfirst($module);
     if (!class_exists($model)) {
         throw new ErrorException('no module > ' . $module);
     }
     $query = new Query();
     $query->select(["{$module}.id"])->from([$module => "received_{$module}"]);
     if ($module == 'direction') {
         $query->addSelect(["{$module}.popularity"]);
         $query->orderBy(['direction.popularity' => SORT_DESC]);
         $query->groupBy(['direction.city_to_code', 'direction.city_from_code']);
     }
     foreach ($params as $key => $value) {
         $postfix = '';
         if ($key == 'from' || $key == 'to') {
             $postfix = "_{$key}";
             $key = 'city';
         }
         if (is_array($value) && $value) {
             $query->leftJoin([$key . $postfix => "received_{$key}"], "{$module}.{$key}{$postfix}_code = {$key}{$postfix}.code");
             foreach ($value as $_key => $_value) {
                 if (is_array($_value) && $_value) {
                     $query->leftJoin([$_key . $postfix => "received_{$_key}"], "{$key}{$postfix}.{$_key}_code = {$_key}{$postfix}.code");
                     foreach ($_value as $__key => $__value) {
                         $query->andFilterWhere(["{$_key}{$postfix}.{$__key}" => $__value]);
                     }
                 } else {
                     $query->andFilterWhere(["{$key}{$postfix}.{$_key}" => $_value]);
                 }
             }
         } else {
             $query->andFilterWhere(["{$module}.{$key}{$postfix}" => $value]);
         }
     }
     $total = $query->count();
     $id = $query->column();
     if ($page > 1) {
         if (!empty($_GET['page'])) {
             $_page = $_GET['page'];
         } else {
             $_page = 0;
         }
         $id = array_slice($id, $_page > 1 ? ($_page - 1) * $page : 0, $page);
         //            VarDumper::dump($id);
     }
     /** @var $model \yii\db\ActiveRecord */
     $query = $model::find()->where(['id' => $id])->asArray();
     if ($module == 'direction') {
         $query->orderBy(['received_direction.popularity' => SORT_DESC]);
     }
     $query->with($relation[$module]);
     if ($page == 1) {
         return $query->all();
     }
     $pagination = new Pagination(['totalCount' => $total, 'pageSize' => $page, 'pageSizeParam' => false]);
     if ($page == 1) {
         $page = 0;
     }
     $query->offset(0)->limit($page);
     return [$query->all(), $pagination];
 }

 /**
  * @return array
  */
 public function apteki_excel()
 {
     $db = new Query();
     if (Yii::$app->user->identity->status == 2) {
         //Регионалы
         $db->andFilterWhere(['=', 'apteki.regional_id', Yii::$app->user->identity->id]);
     }
     if (Yii::$app->user->identity->status == 3) {
         //Провизоры
         $db->andFilterWhere(['=', 'apteki.pi_id', Yii::$app->user->identity->id]);
     }
     if ($_POST[pharmopeka]) {
         $db->andWhere(['=', 'apteki.farmopeka', '1']);
     }
     $db->from('apteki');
     $db->select(['ur_l.name', 'sb_site_users.password_simple', 'sb_site_users.su_login', 'ur_l.id as id2', 'apteki.address', 'apteki.id', 'region.name as rname', 'apteki.tip', 'apteki.contact_phone', 'apteki.contact_face', 'apteki.contact_face_dolj', 'apteki.contact_mail', 'apteki.farmopeka', 'apteki.site']);
     $db->leftJoin('ur_l', 'apteki.ur_l_id = ur_l.id');
     $db->leftJoin('region', 'apteki.region_id = region.id');
     $db->LeftJoin('sb_site_users', 'sb_site_users.apteki_id = apteki.id');
     $data = $db->all();
     $count = $db->count();
     for ($i = 0; $i < count($data); $i++) {
         if ($data[$i]['farmopeka']) {
             $data[$i]['farmopeka'] = "Да";
         } else {
             $data[$i]['farmopeka'] = "Нет";
         }
     }
     $array = array('draw' => intval(Yii::$app->request->post('draw')), 'recordsTotal' => $count, 'recordsFiltered' => $count, 'data' => $data);
     return $data;
 }