Appends a LEFT OUTER JOIN part to the query.
public leftJoin ( string | array $table, string | array $on = '', array $params = [] ) | ||
$table | string | array | the table to be joined. Use a string to represent the name of the table to be joined. The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). The method will automatically quote the table name unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a [[Query]] object representing the sub-query while the corresponding key represents the alias for the sub-query. |
$on | string | array | the join condition that should appear in the ON part. Please refer to [[join()]] on how to specify this parameter. |
$params | array | the parameters (name => value) to be bound to the query |
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; }