PHP CQuery::select方法代码示例

 public function actionTypeAhead($lookup)
 {
     $result = array();
     // комиссии по защите дипломов. показываем только комиссии этого года
     $query = new CQuery();
     $query->select("distinct(comm.id) as id, comm.title as name")->from(TABLE_SAB_COMMISSIONS . " as comm")->condition("comm.title like '%" . $lookup . "%' and year_id=" . CUtils::getCurrentYear()->getId())->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $comm = new CSABCommission(new CActiveRecord($item));
         $value = $comm->title;
         if (!is_null($comm->manager)) {
             $value .= " " . $comm->manager->getName();
         }
         if (!is_null($comm->secretar)) {
             $value .= " (" . $comm->secretar->getName() . ")";
         }
         $diplom = CStaffManager::getDiplom(CRequest::getInt("diplom_id"));
         if (!is_null($diplom)) {
             $cnt = 0;
             foreach ($comm->diploms->getItems() as $d) {
                 if (strtotime($diplom->date_act) == strtotime($d->date_act)) {
                     $cnt++;
                 }
             }
             $value .= " " . $cnt;
         }
         $result[$comm->getId()] = $value;
     }
     return $result;
 }

 public function actionIndex()
 {
     $set = new CRecordSet();
     $set->setPageSize(10);
     $query = new CQuery();
     $query->select("news.*")->from(TABLE_NEWS . " as news")->order("news.id desc");
     $set->setQuery($query);
     $news = new CArrayList();
     foreach ($set->getPaginated()->getItems() as $ar) {
         $newsItem = new CNewsItem($ar);
         $news->add($newsItem->getId(), $newsItem);
     }
     //проверка доступности виджета вконтакте
     /*$check_url = @get_headers('http://vk.com/js/api/openapi.js');
       $cache_vk_id = "vk_access";
       if (is_null(CApp::getApp()->cache->get($cache_vk_id))) {
       	$vk = strpos($check_url[0],'200');
       	CApp::getApp()->cache->set($cache_vk_id, $vk);
       }
       $vk_access = CApp::getApp()->cache->get($cache_vk_id);
       $this->setData("vk_access", $vk_access);*/
     $this->setData("news", $news);
     $this->setData("paginator", $set->getPaginator());
     $this->renderView("_news/public.index.tpl");
 }

 public function execute($contextObject)
 {
     $result = 0;
     if (!is_null($contextObject->terms)) {
         $terms = array();
         foreach ($contextObject->terms->getItems() as $term) {
             $terms[] = $term->number;
         }
     }
     $termSectionsData = new CArrayList();
     foreach ($contextObject->terms->getItems() as $term) {
         $query = new CQuery();
         $query->select("sum(if(term.alias = 'lecture', l.value, 0)) as lecture")->from(TABLE_WORK_PLAN_CONTENT_SECTIONS . " as section")->innerJoin(TABLE_WORK_PLAN_CONTENT_LOADS . " as l", "l.section_id = section.id")->innerJoin(TABLE_TAXONOMY_TERMS . " as term", "term.id = l.load_type_id")->leftJoin(TABLE_WORK_PLAN_SELFEDUCATION . " as selfedu", "selfedu.load_id = l.id")->group("l.section_id")->condition("l.term_id = " . $term->getId());
         $items = $query->execute();
         if ($items->getCount() > 0) {
             $termSectionsData->add($term->getId(), $items);
         }
     }
     foreach ($termSectionsData->getItems() as $termId => $termData) {
         if (CBaseManager::getWorkPlanTerm($termId)->number == $terms[0]) {
             $result = 0;
             foreach ($termData as $row) {
                 $result += $row["lecture"];
             }
         }
     }
     return $result;
 }

 public function execute($contextObject)
 {
     $result = array();
     $terms = array();
     $terms[] = "term.name";
     $termIds = array();
     foreach ($contextObject->terms->getItems() as $term) {
         $termIds[] = $term->getId();
         $terms[] = "sum(if(l.term_id = " . $term->getId() . ", l.value, 0)) as t_" . $term->getId();
     }
     if (count($termIds) > 0) {
         $terms[] = "sum(if(l.term_id in (" . join(", ", $termIds) . "), l.value, 0)) as t_sum";
     }
     $query = new CQuery();
     $query->select(join(", ", $terms))->from(TABLE_WORK_PLAN_CONTENT_LOADS . " as l")->innerJoin(TABLE_TAXONOMY_TERMS . " as term", "term.id = l.load_type_id")->innerJoin(TABLE_WORK_PLAN_CONTENT_SECTIONS . " as section", "l.section_id = section.id")->innerJoin(TABLE_WORK_PLAN_CONTENT_CATEGORIES . " as category", "section.category_id = category.id")->condition("category.plan_id = " . $contextObject->getId())->group("l.load_type_id")->order("term.name");
     $objects = $query->execute();
     foreach ($objects->getItems() as $key => $value) {
         $arr = array_values($value);
         $dataRow = array();
         for ($i = 0; $i <= count($value) - 1; $i++) {
             $dataRow[$i] = $arr[$i];
         }
         $result[] = $dataRow;
     }
     return $result;
 }

 public function execute($contextObject)
 {
     $values = array();
     $terms = array();
     foreach ($contextObject->terms->getItems() as $term) {
         $terms[] = "sum(if(l.term_id = " . $term->getId() . ", l.value, 0)) as t_" . $term->getId();
     }
     $query = new CQuery();
     $query->select(join(", ", $terms))->from(TABLE_WORK_PLAN_CONTENT_LOADS . " as l")->innerJoin(TABLE_TAXONOMY_TERMS . " as term", "term.id = l.load_type_id")->innerJoin(TABLE_WORK_PLAN_CONTENT_SECTIONS . " as section", "l.section_id = section.id")->innerJoin(TABLE_WORK_PLAN_CONTENT_CATEGORIES . " as category", "section.category_id = category.id")->condition("category.plan_id = " . $contextObject->getId())->group("l.load_type_id")->order("term.name");
     $objects = $query->execute();
     $auditorTotal = 0;
     foreach ($objects->getItems() as $key => $value) {
         $arr = array_values($value);
         $dataRow = array();
         for ($i = 0; $i <= count($value) - 1; $i++) {
             $dataRow[$i] = $arr[$i];
         }
         foreach ($dataRow as $i) {
             $auditorTotal += $i;
         }
     }
     $selfWorkValueOfLoad = 0;
     foreach ($contextObject->corriculumDiscipline->labors->getItems() as $labor) {
         if ($labor->type->getAlias() == "self_work") {
             $selfWorkValueOfLoad = $labor->value;
         }
     }
     return $auditorTotal + $selfWorkValueOfLoad;
 }

 public function actionGetViewData()
 {
     $result = array();
     $query = new CQuery();
     $query->select("term.id as id, term.name as name")->from(TABLE_WORK_PLAN_COMPETENTIONS . " as competention")->innerJoin(TABLE_TAXONOMY_TERMS . " as term", "term.id = competention.competention_id")->condition("competention.plan_id=" . CRequest::getInt("plan_id") . " AND competention.type=0")->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionGetViewData()
 {
     $result = array();
     $query = new CQuery();
     $query->select("distinct(stat.id) as id, stat.name as name")->from(TABLE_QUESTION_STATUS . " as stat");
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionTypeAhead($lookup)
 {
     $result = array();
     $query = new CQuery();
     $query->select("opinion.id as id, opinion.name as name")->from(TABLE_PROTOCOL_OPINIONS . " as opinion")->condition("opinion.name like '%" . $lookup . "%'")->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionGetViewData()
 {
     $result = array();
     $query = new CQuery();
     $query->select("taxonomy.id as id, taxonomy.name as name")->from(TABLE_TAXONOMY . " as taxonomy");
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionGetViewData()
 {
     $result = array();
     // выбор сотрудников
     $query = new CQuery();
     $query->select("term.id as id, term.number as name")->from(TABLE_WORK_PLAN_TERMS . " as term")->condition("plan_id=" . CRequest::getInt("plan_id"))->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionTypeAhead($lookup)
 {
     $result = array();
     // выбор пользователей
     $query = new CQuery();
     $query->select("user.id as id, user.fio as name")->from(TABLE_USERS . " as user")->condition("user.fio like '%" . $lookup . "%'")->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionGetViewData()
 {
     $result = array();
     // выбор сотрудников
     $query = new CQuery();
     $query->select("category.id as id, category.title as name")->from(TABLE_WORK_PLAN_CONTENT_CATEGORIES . " as category")->condition("plan_id=" . CRequest::getInt("plan_id"))->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionGetViewData()
 {
     // $result = array();
     // выбор активных отчетов
     $query = new CQuery();
     $query->select("report.id as id, report.title as name")->from(TABLE_REPORTS . " as report");
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionTypeAhead($lookup)
 {
     $result = array();
     // выбор студентов
     $query = new CQuery();
     $query->select("distinct(student.id) as id, student.fio as name")->from(TABLE_STUDENTS . " as student")->condition("student.fio like '%" . $lookup . "%'")->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }

 public function actionTypeAhead($lookup)
 {
     $result = array();
     // выбор студенческих групп
     $query = new CQuery();
     $query->select("distinct(gr.id) as id, gr.name as name")->from(TABLE_STUDENT_GROUPS . " as gr")->condition("gr.name like '%" . $lookup . "%'")->limit(0, 10);
     foreach ($query->execute()->getItems() as $item) {
         $result[$item["id"]] = $item["name"];
     }
     return $result;
 }