Sets the GROUP BY part of the query.
public groupBy ( string | array | yii\db\Expression $columns ) | ||
$columns | string | array | yii\db\Expression | the columns to be grouped by. Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). The method will automatically quote the column names unless a column contains some parenthesis (which means the column contains a DB expression). Note that if your group-by is an expression containing commas, you should always use an array to represent the group-by information. Otherwise, the method will not be able to correctly determine the group-by columns. Since version 2.0.7, an [[Expression]] object can be passed to specify the GROUP BY part explicitly in plain SQL. |
/** * 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 testGroup() { $query = new Query(); $query->groupBy('team'); $this->assertEquals(['team'], $query->groupBy); $query->addGroupBy('company'); $this->assertEquals(['team', 'company'], $query->groupBy); $query->addGroupBy('age'); $this->assertEquals(['team', 'company', 'age'], $query->groupBy); }
public function getPOLineRelatedQuery($params = [], $modelline = [], $groupBy = null) { $query = new Query(); if ($params['partner_id']) { $partner_id = $params['partner_id']; } else { $partner_id = '0'; } if ($modelline) { if ($modelline['product_id']) { $product = $modelline['product_id']; } else { $product = '0'; } } else { $product = $params['product_id']; } if ($params['state']) { $state = $params['state']; } else { $state = '0'; } if ($params['date_order']) { $dattefrom = $params['date_order']; $dateto = $params['duedate']; } else { $dattefrom = '0'; $dateto = '0'; } // if($params['pricelist']){ // if(is_array($params['pricelist'])){ // $pricelist=implode(",", $params['pricelist']); // }else{ // $pricelist=$params['pricelist']; // } // }else{ // $pricelist='0'; // } if ($groupBy) { if (is_array($params['pricelist'])) { $pricelist = implode(",", $params['pricelist']); } else { $pricelist = $params['pricelist']; } } if ($groupBy) { if ($groupBy == 'partner') { $query->select(['CONCAT("pol"."partner_id", \'/\',"pid"."id",\'/\',\'' . $product . '\',\'/\',\'' . $state . '\',\'/\',\'' . $dattefrom . '\',\'/\',\'' . $dateto . '\') as id, rp.name as partner, SUM(pol.price_unit*pol.product_qty) as total, pid.name as pricelist']); } } else { $query->select(' pol.id as id, pol.partner_id as partner_id, po.date_order as date_order, po.name as no_po, pol.name as pol_desc, rp.name as partner, pol.product_id as product_id, pp.name_template as product, pol.price_unit as price_unit, pol.state as state, pol.product_qty as product_qty, pu.name as uom, pid.name as pricelist, (pol.product_qty*pol.price_unit) as total, '); } $query->from('purchase_order_line as pol')->join('LEFT JOIN', 'purchase_order as po', 'po.id=pol.order_id')->join('LEFT JOIN', 'product_pricelist as pid', 'pid.id=po.pricelist_id')->join('LEFT JOIN', 'product_product as pp', 'pp.id=pol.product_id')->join('LEFT JOIN', 'product_uom as pu', 'pu.id=pol.product_uom')->join('LEFT JOIN', 'res_partner as rp', 'rp.id=pol.partner_id'); if ($groupBy) { if ($groupBy == 'partner') { $query->groupBy(['pol.partner_id', 'rp.name', 'pid.id']); $query->orderBy('rp.name ASC'); } } if (isset($params['partner_id']) && $params['partner_id']) { if ($params['partner_id'] != '0') { $query->andWhere(['pol.partner_id' => explode(',', $params['partner_id'])]); } } if (isset($modelline['name']) && $modelline['name']) { $query->andWhere(['ilike', 'pol.name', $modelline['name']]); // die(); } if (isset($modelline['product_id']) && $modelline['product_id']) { if ($modelline['product_id'] != '0') { $query->andWhere(['pol.product_id' => explode(',', $modelline['product_id'])]); } } if (isset($params['state']) && $params['state']) { if ($params['state'] == "purchased") { $cekstate = 'confirmed, approved, done'; if ($params['state'] != '0') { $query->andWhere(['pol.state' => explode(',', $cekstate)]); } } else { if ($params['state'] != '0') { $to_state = []; $exps = explode(',', urldecode($params['state'])); foreach ($exps as $exp) { if ($exp == 'purchased') { $to_state[] = 'confirmed'; $to_state[] = 'approved'; $to_state[] = 'done'; } else { $to_state[] = $exp; } } $query->andWhere(['pol.state' => $to_state]); } } } else { $query->andWhere(['in', 'pol.state', ['confirmed', 'approved', 'done']]); } if (isset($params['date_order']) && $params['date_order']) { if ($params['date_order'] != '0') { $query->andWhere(['>=', 'po.date_order', $params['date_order']]); $query->andWhere(['<=', 'po.date_order', $params['duedate']]); } } if (isset($params['pricelist'])) { $query->andWhere(['po.pricelist_id' => $params['pricelist']]); } if (!$groupBy) { $query->addOrderBy(['po.date_order' => SORT_DESC]); } return $query; }
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]; }
public function actionTable() { if (\Yii::$app->user->identity->status == 1) { $users = Users::find()->where(['status' => 2])->orderBy('username')->all(); } else { $users = Users::find()->where(['id' => \Yii::$app->user->identity->id])->orderBy('username')->all(); } foreach ($users as $user) { $id = $user['id']; $db = new Query(); $db->from(Ur::tableName()); $db->select(['COUNT(*) AS count', 'id_reg', 'region.name']); $db->where(['=', 'ur_l.regional_id', $id]); $db->andWhere(['=', 'ur_l.plat', '1']); $db->innerJoin('region_ur_l', "region_ur_l.id_ur = ur_l.id"); $db->innerJoin('region', "region.id = region_ur_l.id_reg"); $db->groupBy('region_ur_l.id_reg'); $db->orderBy('region.name ASC'); $ur_region_array[$id] = $db->all(); $db = new Query(); $db->from(Apteki::tableName()); $db->select(['COUNT(*) AS count', 'region.id', 'region.name']); $db->where(['=', 'apteki.regional_id', $id]); $db->innerJoin('region', "region.id = apteki.region_id"); $db->groupBy('region.id'); $db->orderBy('region.name ASC'); $apteki_region_array[$id] = $db->all(); } return $this->render('table', compact(['ur_region_array', 'apteki_region_array', 'users'])); }
protected function getClusteringMarkers($zoom, $lat_max, $lat_min, $lng_min, $lng_max) { $number = 0; $multiplyNumber = 1 / 500 * pow(2.4, $zoom); $query = new Query(); $query->from('{{%adver}}')->select(["COUNT(*) AS [[adver_count]]", "AVG(latitude) AS [[lat]]", "AVG(longitude) AS [[lng]]", "FORMAT([[latitude]] * {$multiplyNumber} , {$number}) AS [[g_lt]]", "FORMAT([[longitude]] * {$multiplyNumber} , {$number}) AS [[g_ln]]"]); $query->andWhere(['between', '[[latitude]]', $lat_min, $lat_max]); $query->andWhere(['between', '[[longitude]]', $lng_min, $lng_max]); $query->WHERE(['[[status]]' => self::STATUS_ACTIVE, 'lang' => ['*', Yii::$app->language]]); // adjust the query by adding the filters $query->andFilterWhere(['[[category_id]]' => $this->category_id]); $query->andFilterWhere(['[[country_id]]' => $this->country_id]); $query->andFilterWhere(['[[province_id]]' => $this->province_id]); $query->andFilterWhere(['[[city_id]]' => $this->city_id]); $query->andFilterWhere(['like', '[[title]]', $this->title]); $query->andFilterWhere(['like', '[[address]]', $this->address]); $query->groupBy(['[[g_lt]]', '[[g_ln]]']); return $this->mergeBubbles($zoom, $query->all()); }
public function getSOLineRelatedQuery($params = [], $groupBy = null) { $query = new Query(); if ($params['productcategory']) { if (is_array($params['productcategory'])) { $category = implode(",", $params['productcategory']); } else { $category = $params['productcategory']; } } else { $category = '0'; } if ($params['partner']) { $partner = $params['partner']; } else { $partner = '0'; } if ($params['product']) { $product = $params['product']; } else { $product = '0'; } if ($params['state']) { $state = $params['state']; } else { $state = '0'; } if ($params['date_from']) { $dattefrom = $params['date_from']; $dateto = $params['date_to']; } else { $dattefrom = '0'; $dateto = '0'; } if ($params['pricelist']) { if (is_array($params['pricelist'])) { $pricelist = implode(",", $params['pricelist']); } else { $pricelist = $params['pricelist']; } } else { $pricelist = '0'; } // jika group if ($groupBy) { $query->select(['CONCAT("pc"."id", \'/\', "pid"."id", \'/\', "pc"."name", \'/\', "pid"."name",\'/\',\'' . $category . '\',\'/\',\'' . $partner . '\',\'/\',\'' . $product . '\',\'/\',\'' . $pricelist . '\',\'/\',\'' . $state . '\',\'/\',\'' . $dattefrom . '\',\'/\',\'' . $dateto . '\') as id, pc.name as category, SUM(sol.price_unit*sol.product_uom_qty) as total, pid.name as pricelist']); } else { $query->select(' sol.id as id, so.partner_id as partner_id, so.date_order as date_order, so.name as no_po, rp.name as partner, sol.product_id as product_id, pp.name as product, sol.price_unit as price_unit, sol.state as state, pc.name as category, pid.name as pricelist, sol.product_uom_qty as qty, sol.name as product_desc, (sol.product_uom_qty*sol.price_unit) as total, so.name as so_no '); } $query->from('sale_order_line as sol')->join('LEFT JOIN', 'sale_order as so', 'so.id=sol.order_id')->join('LEFT JOIN', 'product_template as pp', 'pp.id=sol.product_id')->join('LEFT JOIN', 'res_partner as rp', 'rp.id=so.partner_id')->join('LEFT JOIN', 'product_category as pc', 'pc.id=pp.categ_id')->join('LEFT JOIN', 'product_pricelist as pid', 'pid.id=so.pricelist_id'); if ($groupBy) { $query->groupBy(['pc.id', 'pid.id']); } if (isset($params['partner']) && $params['partner']) { if ($params['partner'] != '0') { $query->andWhere(['so.partner_id' => explode(',', $params['partner'])]); } } if (isset($params['productcategory']) && $params['productcategory']) { if ($params['productcategory'] != '0') { if (is_array($params['productcategory'])) { $query->andWhere(['pp.categ_id' => explode(',', implode(",", $params['productcategory']))]); } else { $query->andWhere(['pp.categ_id' => explode(',', $params['productcategory'])]); } } } if (isset($params['product']) && $params['product']) { if ($params['product'] != '0') { $query->andWhere(['sol.product_id' => explode(',', $params['product'])]); } } if (isset($params['pricelist']) && $params['pricelist']) { if ($params['pricelist'] != '0') { if (is_array($params['pricelist'])) { $query->andWhere(['so.pricelist_id' => explode(',', implode(",", $params['pricelist']))]); } else { $query->andWhere(['so.pricelist_id' => explode(',', $params['pricelist'])]); } } } // if(isset($params['state']) && $params['state']){ // if($params['state']!='0') // { // $query->andWhere(['sol.state'=>explode(',',$params['state'])]); // } // } if (isset($params['state']) && $params['state']) { if ($params['state'] == "order") { $cekstate = 'confirmed, approved, done'; if ($params['state'] != '0') { $query->andWhere(['sol.state' => explode(',', $cekstate)]); } } else { if ($params['state'] != '0') { $query->andWhere(['sol.state' => explode(',', $params['state'])]); } } } else { $query->andWhere(['in', 'sol.state', ['confirmed', 'approved', 'done']]); } if (isset($params['date_from']) && $params['date_from']) { if ($params['date_from'] != '0') { $query->andWhere(['>=', 'so.date_order', $params['date_from']]); $query->andWhere(['<=', 'so.date_order', $params['date_to']]); } } $query->andWhere(['not', ['sol.product_id' => null]]); if (!$groupBy) { $query->addOrderBy(['so.date_order' => SORT_DESC]); } return $query; }
private static function saveErrorLogMonth($str_time, $appname_list) { $format_cur_time = date("Y-m-d H:i:s", time()); $cur_time_year = date("Y"); $cur_time_month = date("m"); $str_time_year = date("Y", $str_time); $str_time_month = date("m", $str_time); $diff_month = ($cur_time_year - $str_time_year) * 12 + ($cur_time_month - $str_time_month); $end_time = strtotime('+1 month', $str_time); $format_str_time = date("Y-m-d H:i:s", $str_time); $format_end_time = date("Y-m-d H:i:s", $end_time); for ($i = 0; $i < $diff_month; $i++) { //统计错误日志的数量 $error_query = new Query(); $error_query->select("count(id) as total,ApplicationId")->from("ErrorLog")->where(["in", "ApplicationId", $appname_list]); $error_query->andWhere("AddDate>=:str_time", array(":str_time" => $format_str_time)); $error_query->andWhere("AddDate<:end_time", array(":end_time" => $format_end_time)); $error_query->groupBy("ApplicationId"); $error_count_list = $error_query->all(); $log_arr = []; $month = strtotime($format_str_time); foreach ($error_count_list as $key => $value) { //保存数据在ErrorLog_month $log_arr[] = [$value['ApplicationId'], $value['total'], date("Ym", $month), $format_cur_time]; } if (!empty($log_arr)) { $command = \Yii::$app->db->createCommand(); $command->batchInsert(ErrorLogMonth::tableName(), ['ApplicationId', 'Number', 'Month', 'Updatetime'], $log_arr); $command->execute(); } $format_str_time = $format_end_time; $end_time = strtotime('+1 month', $end_time); $format_end_time = date("Y-m-d H:i:s", $end_time); } }