public static function getPopularVideos() { $query = new Query(); $query->select('videos.*,(SELECT COUNT(*) FROM videos_comments WHERE videos.id = videos_comments.video_id) as commentsCount'); $query->from('videos'); $query->orderBy('commentsCount DESC'); $query->limit('4'); return $query->all(); }
public static function getPopularPosts() { $query = new Query(); $query->select('article.*,(SELECT COUNT(*) FROM article_comments WHERE article.id = article_comments.article_id) as commentsCount'); $query->from('article'); $query->orderBy('commentsCount DESC'); $query->limit('4'); return $query->all(); }
public static function getMostActive() { $query = new Query(); $query->select('profile.*,(SELECT COUNT(*) FROM discussion_post_reply WHERE profile.user_id = discussion_post_reply.user_id) as repliesCount'); $query->from('profile'); $query->orderBy('repliesCount DESC'); $query->limit('4'); return $query->all(); }
public function getHistory($chatId, $limit = 10) { $query = new Query(); $query->select(['user_id', 'username', 'message', 'timestamp', 'avatar_16', 'avatar_32'])->from(self::tableName())->where(['chat_id' => $chatId]); $query->orderBy(['timestamp' => SORT_DESC]); if ($limit) { $query->limit($limit); } return $query->all(); }
/** * Returns list of users' full names * @param int $limit records count * @return array list of users' names */ public static function getUserList($limit = null) { $query = new Query(); $query->select(['id', 'text' => "CONCAT(`u`.`first_name`,' ', `u`.`last_name`)"])->from(['u' => 'User']); if (isset($limit)) { $query->limit($limit); } $query->each(); $command = $query->createCommand(); return $command->queryAll(); }
public function getTopPsychologists($amount = null) { $query = new Query(); $query->select('profile.*')->from('profile')->join('join', 'psychologist_top', 'psychologist_top.psychologist_id = profile.user_id')->orderBy('profile.id'); if ($amount) { $query->limit($amount); return $psychologistsTopDataProvider = new ActiveDataProvider(['query' => $query]); //return $query->all(); } return $psychologistsTopDataProvider = new ActiveDataProvider(['query' => $query]); }
private function getWhere($filters = [], $select = '*') { $query = new Query(); $query->select($select)->from(static::tableName()); if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } return $query; }
/** * Получает записи с базы данных * @param string $where * @param string $column */ public function GetItems($columns = [], $where = [], $limit = 0) { $query = new Query(); $query->select($columns); $query->from($this->table); if ($where) { $query->where($where); } if ($limit) { $query->limit($limit); } $result = $query->all(); return $result; }
private function getWhere($filters = [], $select = '*') { $query = new Query(); $query->select($select)->from(static::tableName()); if (isset($filters['D05_DEN_NO']) && $filters['D05_DEN_NO']) { $query->andwhere('D05_DEN_NO=:den_no', [':den_no' => $filters['D05_DEN_NO']]); } if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } return $query; }
/** * @return array * @throws yii\web\NotFoundHttpException */ public function run() { if (!isset($_GET[$this->query_variable])) { throw new yii\web\NotFoundHttpException(); } $modelName = $this->modelName; Yii::$app->response->format = yii\web\Response::FORMAT_JSON; $query = new yii\db\Query(); $query->select($this->json_attributes)->from($modelName::tableName()); $search_query = $_GET[$this->query_variable]; foreach ($this->search_attributes as $attribute) { $query->orWhere(['like', $attribute, $search_query]); } $suggest = $query->limit($this->limit)->all(); return $suggest; }
private function getWhere($filters = [], $select = '*') { $query = new Query(); $query->select($select)->from(static::tableName()); if (count($filters)) { foreach ($filters as $field => $val) { if ($field != 'offset' && $field != 'limit') { $query->andwhere($field . ' = ' . $val); } } } //$query->where('status=:status', [':status' => $status]); if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } return $query; }
/** * @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; }
/** * 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]); }
private function getWhere($filters = [], $select = '*') { $query = new Query(); $query->select($select)->from(static::tableName()); //$query->where('status=:status', [':status' => $status]); if (isset($filters['D01_KAIIN_CD']) && $filters['D01_KAIIN_CD']) { $query->andwhere('D01_KAIIN_CD = ' . $filters['D01_KAIIN_CD']); } if (isset($filters['D01_CUST_NO']) && $filters['D01_CUST_NO']) { $query->andwhere('D01_CUST_NO = ' . $filters['D01_CUST_NO']); } if (isset($filters['D01_KAKE_CARD_NO']) && $filters['D01_KAKE_CARD_NO']) { $query->where(['D01_KAKE_CARD_NO' => $filters['D01_KAKE_CARD_NO']]); } if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } return $query; }
public static function getList($page = 1, $rows = 10, $condition = '', $conditionParams = []) { $return = []; $query = new Query(); $query->select('COUNT(1)'); $query->from(self::tableName() . ' t0'); $query->where($condition, $conditionParams); $result['total'] = $query->scalar(); $query->select(['t0.id', 't0.username', 't0.email', 't1.firstname', 't1.lastname']); $query->join('LEFT JOIN', 'user_profile t1', 't0.id = t1.user_id'); $query->offset($page * $rows - $rows); $query->limit($rows); $result['rows'] = $query->All(); foreach ($result['rows'] as $k => $v) { $myRoles = Yii::$app->authManager->getRolesByUser($v['id']); if (count($myRoles)) { foreach ($myRoles as $kk => $vv) { $result['rows'][$k]['role'] = $kk; break; } } } return $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['M05_COM_CD_IN']) && $filters['M05_COM_CD_IN'] != '') { $query->andwhere(['IN', 'M05_COM_CD', $filters['M05_COM_CD_IN']]); } if (isset($filters['M05_COM_CD']) && $filters['M05_COM_CD'] != '') { $query->andwhere(['like', 'M05_COM_CD', $filters['M05_COM_CD']]); } if (isset($filters['M05_COM_NAMEN']) && $filters['M05_COM_NAMEN'] != '') { $query->andwhere(['like', 'M05_COM_NAMEN', $filters['M05_COM_NAMEN']]); } if (isset($filters['M05_NST_CD']) && $filters['M05_NST_CD'] != '') { $query->andwhere(['like', 'M05_NST_CD', $filters['M05_NST_CD']]); } if (isset($filters['in']) && count($filters['in'])) { $query->andwhere(['IN', 'M05_KIND_DM_NO', $filters['in']]); } if (isset($filters['not_in']) && count($filters['not_in'])) { $query->andwhere('M05_KIND_DM_NO NOT IN (' . implode(',', $filters['not_in']) . ') OR M05_KIND_DM_NO IS NULL'); } if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } return $query; }
public function testLimitOffset() { $query = new Query(); $query->limit(10)->offset(5); $this->assertEquals(10, $query->limit); $this->assertEquals(5, $query->offset); }
private function getWhereSearch($filters) { $query = new Query(); $query->select(["SDP_TD03_DENPYO.*, TO_CHAR(D03_UPD_DATE, 'YYYY/mm/DD') as CHAR_D03_UPD_DATE,\n SDP_TD01_CUSTOMER.D01_CUST_NAMEN, SDP_TD01_CUSTOMER.D01_CUST_NAMEK, SDP_TD01_CUSTOMER.D01_NOTE"])->from(static::tableName())->leftJoin('SDP_TD01_CUSTOMER', 'SDP_TD03_DENPYO.D03_CUST_NO = SDP_TD01_CUSTOMER.D01_CUST_NO')->orderBy('D03_DEN_NO'); if (isset($filters['status']) && $filters['status'] != '') { if ($filters['status'] == 1) { $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]); $query->andWhere(['>', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]); } if ($filters['status'] == 2) { $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 1]); } if ($filters['status'] == 0) { $query->andwhere('SDP_TD03_DENPYO.D03_STATUS=:status', [':status' => 0]); $query->andWhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', date('Ymd')]); } } if (isset($filters['car']) && $filters['car']) { $query->andwhere(['like', 'SDP_TD03_DENPYO.D03_CAR_NO', $filters['car']]); } if (isset($filters['job']) && $filters['job'] != '') { $denpyo_sagyo = new Sdptd04denpyosagyo(); $data = $denpyo_sagyo->getData(['D04_SAGYO_NO' => $filters['job']]); $arr_den_no = [-1]; foreach ($data as $k => $v) { $arr_den_no[] = $v['D04_DEN_NO']; } $query->andwhere(['in', 'SDP_TD03_DENPYO.D03_DEN_NO', $arr_den_no]); } if (isset($filters['start_time']) && $filters['start_time']) { $query->andwhere(['>=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['start_time']]); } if (isset($filters['end_time']) && $filters['end_time']) { $query->andwhere(['<=', 'SDP_TD03_DENPYO.D03_SEKOU_YMD', $filters['end_time']]); } if (isset($filters['detail_no'])) { $query->andwhere('SDP_TD03_DENPYO.D03_DEN_NO=:den_no', [':den_no' => $filters['detail_no']]); } if (isset($filters['m50_ss_cd'])) { $query->andwhere('SDP_TD03_DENPYO.D03_SS_CD =:m50_ss_cd', [':m50_ss_cd' => $filters['m50_ss_cd']]); } if (isset($filters['offset']) && $filters['offset']) { $query->offset($filters['offset']); } if (isset($filters['limit']) && $filters['limit']) { $query->limit($filters['limit']); } return $query; }
public static function getWarning($orderBy, $limit, $conditions = []) { $query = new Query(); $query->select('w.*, s.name AS station_name, a.name AS area_name, c.name AS center_name')->from('warning w')->leftJoin('station s', 's.id = w.station_id')->innerJoin('area a', 's.area_id = a.id')->innerJoin('center c', 'c.id = s.center_id')->where([]); if (!empty($conditions)) { $oneCondition = 0; foreach ($conditions as $con) { $query->andWhere($con); } } $query->orderBy($orderBy); if ($limit > 0) { $query->limit($limit); } $warnings = $query->all(); if (!empty($warnings)) { foreach ($warnings as $k => $w) { $warnings[$k]['pics'] = self::findPicture($w['id']); } } return $warnings; }
public static function buildContentQuery($tableName, $other = [], $where = null) { $query = new Query(); if (isset($other['fields'])) { $query->select($other['fields']); } if (empty($tableName)) { // todo $tableName = 'model_news'; } $query->from($tableName); if ($where !== null) { $query->andWhere($where); } if (isset($other['where'])) { $query->andWhere($other['where']); } if (isset($other['att1']) && is_integer($other['att1'])) { $query->andWhere(['att1' => $other['att1']]); } if (isset($other['att2']) && is_integer($other['att2'])) { $query->andWhere(['att2' => $other['att2']]); } if (isset($other['att3']) && is_integer($other['att3'])) { $query->andWhere(['att3' => $other['att3']]); } if (isset($other['flag'])) { $flagValue = CommonUtility::getFlagValue($other['flag']); if ($flagValue > 0) { $query->andWhere('flag&' . $flagValue . '>0'); } } if (isset($other['is_pic']) && $other['is_pic']) { $query->andWhere(['is_pic' => 1]); } if (isset($other['order'])) { $query->orderBy($other['order']); } else { $query->orderBy('publish_time desc'); } if (isset($other['offset'])) { $query->offset(intval($other['offset'])); } else { $query->offset(0); } if (isset($other['limit'])) { $query->limit(intval($other['limit'])); } else { $query->limit(10); } return $query; }
public function ur_l() { $db = new Query(); if ($_POST['search']['value'] != "") { $db->where(['like', 'name', $_POST['search']['value']]); $db->orWhere(['like', 'address', $_POST['search']['value']]); $db->orWhere(['like', 'inn', $_POST['search']['value']]); $db->orWhere(['like', 'wabc', $_POST['search']['value']]); } if (is_numeric($_POST['region'])) { $db->InnerJoin('region_ur_l', 'id_ur = ur_l.id'); $db->andWhere(['=', 'id_reg', $_POST['region']]); } if ($_POST['pharmopeka']) { $db->andWhere(['=', 'ur_l.farmopeka', '1']); } if ($_POST['order'][0]['column']) { $column_order = $_POST['order'][0]['column']; $column = $_POST['columns'][$column_order]['data']; $db->orderBy($_POST['columns'][$column_order]['data'] . " " . $_POST['order'][0]['dir']); } if (Yii::$app->user->identity->status == 2) { //Регионалы $db->andFilterWhere(['=', 'ur_l.regional_id', Yii::$app->user->identity->id]); } if (Yii::$app->user->identity->status == 3) { //Провизоры $db->andFilterWhere(['=', 'ur_l.pi_id', Yii::$app->user->identity->id]); } $db->from('ur_l'); $db->select(['ur_l.name', 'ur_l.id', 'ur_l.inn', 'ur_l.address', 'ur_l.wabc']); $db->limit(10); $db->offset(intval(Yii::$app->request->post('start'))); $data = $db->all(); $count = $db->count(); for ($i = 0; $i < count($data); $i++) { $data[$i]['name'] = "<a href=\"/ur/update?id=" . $data[$i][id] . "\">" . $data[$i][name] . "</a>"; $data[$i]['id'] = "<a href=\"/ur/update?id=" . $data[$i][id] . "\">" . $data[$i][id] . "</a>"; } $array = array('draw' => intval(Yii::$app->request->post('draw')), 'recordsTotal' => $count, 'recordsFiltered' => $count, 'data' => $data); return $array; }