PHP CQuery::innerJoin方法代码示例

 public function actionIndex()
 {
     $set = new CRecordSet(false);
     $query = new CQuery();
     $query->select("activity.*")->from(TABLE_STUDENTS_ACTIVITY . " as activity");
     // сортировки по столбцам
     if (CRequest::getString("order") == "") {
         $query->order("activity.id desc");
     } elseif (CRequest::getString("order") == "date_act") {
         if (CRequest::getString("direction") == "") {
             $query->order("activity.date_act desc");
         } else {
             $query->order("activity.id " . CRequest::getString("direction"));
         }
     } elseif (CRequest::getString("order") == "subject_id") {
         $query->leftJoin(TABLE_DISCIPLINES . " as discipline", "activity.subject_id = discipline.id");
         if (CRequest::getString("direction") == "") {
             $query->order("discipline.name asc");
         } else {
             $query->order("discipline.name " . CRequest::getString("direction"));
         }
     } elseif (CRequest::getString("order") == "kadri_id") {
         $query->leftJoin(TABLE_PERSON . " as person", "activity.kadri_id = person.id");
         if (CRequest::getString("direction") == "") {
             $query->order("person.fio asc");
         } else {
             $query->order("person.fio " . CRequest::getString("direction"));
         }
     } elseif (CRequest::getString("order") == "student_id") {
         $query->leftJoin(TABLE_STUDENTS . " as student_f", "student_f.id = activity.student_id");
         if (CRequest::getString("direction") == "") {
             $query->order("student_f.fio asc");
         } else {
             $query->order("student_f.fio " . CRequest::getString("direction"));
         }
     } else {
         $query = new CQuery();
         $query->select("activity.*")->from(TABLE_STUDENTS_ACTIVITY)->order("activity.id desc");
     }
     // запросы для получения списка групп и списка преподавателей
     $personQuery = new CQuery();
     $personQuery->select("distinct(person.id) as id, person.fio as name")->from(TABLE_STUDENTS_ACTIVITY . " as activity")->innerJoin(TABLE_PERSON . " as person", "activity.kadri_id = person.id")->order("person.fio asc");
     $groupQuery = new CQuery();
     $groupQuery->select("distinct(st_group.id) as id, st_group.name as name")->from(TABLE_STUDENTS_ACTIVITY . " as activity")->innerJoin(TABLE_STUDENTS . " as student", "student.id = activity.student_id")->innerJoin(TABLE_STUDENT_GROUPS . " as st_group", "st_group.id = student.group_id")->order("st_group.name asc");
     $disciplineQuery = new CQuery();
     $disciplineQuery->select("distinct(subject.id) as id, subject.name as name")->from(TABLE_STUDENTS_ACTIVITY . " as activity")->innerJoin(TABLE_DISCIPLINES . " as subject", "activity.subject_id = subject.id")->order("subject.name asc");
     // фильтры
     $selectedPerson = null;
     $selectedGroup = null;
     $selectedDiscipline = null;
     $selectedStudent = null;
     $selectedControl = null;
     if (CRequest::getString("filter") !== "") {
         $filters = explode("_", CRequest::getString("filter"));
         foreach ($filters as $filter) {
             $f = explode(":", $filter);
             if (count($f) > 1) {
                 $key = $f[0];
                 $value = $f[1];
                 if ($key == "person") {
                     if ($value != 0) {
                         $selectedPerson = $value;
                         $query->condition("kadri_id=" . $value);
                     }
                 } elseif ($key == "group") {
                     if ($value != 0) {
                         $selectedGroup = $value;
                         $query->innerJoin(TABLE_STUDENTS . " as student", "activity.student_id = student.id");
                         $query->innerJoin(TABLE_STUDENT_GROUPS . " as st_group", "student.group_id = st_group.id AND st_group.id=" . $value);
                     }
                 } elseif ($key == "discipline") {
                     if ($value != 0) {
                         $selectedDiscipline = $value;
                         $query->innerJoin(TABLE_DISCIPLINES . " as subject", "subject.id = activity.subject_id AND subject.id=" . $value);
                     }
                 } elseif ($key == "student") {
                     if ($value != 0) {
                         $selectedStudent = CStaffManager::getStudent($value);
                         $query->innerJoin(TABLE_STUDENTS . " as student", "activity.student_id = student.id AND student.id=" . $value);
                     }
                 } elseif ($key == "control") {
                     if ($value != 0) {
                         $selectedControl = CTaxonomyManager::getControlType($value);
                         $query->innerJoin(TABLE_STUDENTS_CONTROL_TYPES . " as control", "activity.study_act_id = control.id AND control.id=" . $value);
                     }
                 }
             }
         }
         /**
          * Дополняем фильтры по преподавателям, группам и дисциплинам
          */
         if (!is_null($selectedPerson)) {
             $groupQuery->innerJoin(TABLE_PERSON . " as person", "person.id = activity.kadri_id AND person.id=" . $selectedPerson);
             $disciplineQuery->innerJoin(TABLE_PERSON . " as person", "person.id = activity.kadri_id AND person.id=" . $selectedPerson);
         }
         if (!is_null($selectedGroup)) {
             $personQuery->innerJoin(TABLE_STUDENTS . " as student", "student.id = activity.student_id");
             $personQuery->innerJoin(TABLE_STUDENT_GROUPS . " as st_group", "st_group.id = student.group_id and st_group.id=" . $selectedGroup);
             $disciplineQuery->innerJoin(TABLE_STUDENTS . " as student", "student.id = activity.student_id");
             $disciplineQuery->innerJoin(TABLE_STUDENT_GROUPS . " as st_group", "st_group.id = student.group_id and st_group.id=" . $selectedGroup);
//.........这里部分代码省略.........

 public function actionSearch()
 {
     $res = array();
     $term = CRequest::getString("query");
     /**
      * Поиск по комиссии
      */
     $query = new CQuery();
     $query->select("distinct(com.id) as id, com.name as title")->from(TABLE_DIPLOM_PREVIEW_COMISSIONS . " as com")->condition("com.name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("field" => "com.id", "value" => $item["id"], "label" => $item["title"], "class" => "CDiplomPreviewCommission");
     }
     /**
      * Поиск по секретарю
      */
     $query = new CQuery();
     $query->select("distinct(com.secretary_id) as id, person.fio as title");
     $query->innerJoin(TABLE_PERSON . " as person", "com.secretary_id = person.id")->from(TABLE_DIPLOM_PREVIEW_COMISSIONS . " as com")->condition("person.fio like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("field" => "secretary_id", "value" => $item["id"], "label" => $item["title"], "class" => "CDiplomPreviewCommission");
     }
     /**
      * Поиск по примечанию
      */
     $query = new CQuery();
     $query->select("distinct(com.id) as id, com.comment as title")->from(TABLE_DIPLOM_PREVIEW_COMISSIONS . " as com")->condition("com.comment like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("field" => "com.id", "value" => $item["id"], "label" => $item["title"], "class" => "CDiplomPreviewCommission");
     }
     /**
      * Поиск по дате создания комиссии
      */
     /*$query = new CQuery();
      	$query->select("distinct(com.id) as id, com.date_act as title")
      	->from(TABLE_DIPLOM_PREVIEW_COMISSIONS." as com")
      	->condition("com.date_act like '%".$term."%'")
      	->limit(0, 5);
      	foreach ($query->execute()->getItems() as $item) {
      		$res[] = array(
      				"field" => "com.id",
      				"value" => $item["id"],
      				"label" => $item["title"],
      				"class" => "CDiplomPreviewCommission"
      		);
      	}*/
     echo json_encode($res);
 }

 public function actionSearch()
 {
     $res = array();
     $term = CRequest::getString("query");
     /**
      * Поиск по ФИО студента
      */
     $query = new CQuery();
     $query->select("distinct(preview.student_id) as id, student.fio as name");
     $query->innerJoin(TABLE_STUDENTS . " as student", "preview.student_id=student.id")->from(TABLE_DIPLOM_PREVIEWS . " as preview")->condition("student.fio like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 1);
     }
     /**
      * Поиск по теме ВКР
      */
     $query = new CQuery();
     $query->select("distinct(preview.student_id) as id, diplom.dipl_name as name");
     $query->innerJoin(TABLE_DIPLOMS . " as diplom", "diplom.student_id = preview.student_id")->from(TABLE_DIPLOM_PREVIEWS . " as preview")->condition("diplom.dipl_name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 2);
     }
     /**
      * Поиск по рецензенту
      */
     $query = new CQuery();
     $query->select("distinct(preview.student_id) as id, diplom.recenz as name");
     $query->innerJoin(TABLE_DIPLOMS . " as diplom", "diplom.student_id = preview.student_id")->from(TABLE_DIPLOM_PREVIEWS . " as preview")->condition("diplom.recenz like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 3);
     }
     /**
      * Поиск по рецензенту по id
      */
     $query = new CQuery();
     $query->select("distinct(preview.student_id) as id, person.fio as name");
     $query->innerJoin(TABLE_DIPLOMS . " as diplom", "diplom.student_id = preview.student_id");
     $query->innerJoin(TABLE_PERSON . " as person", "diplom.recenz_id = person.id")->from(TABLE_DIPLOM_PREVIEWS . " as preview")->condition("person.fio like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 4);
     }
     /**
      * Поиск по комментарию
      */
     $query = new CQuery();
     $query->select("distinct(preview.id) as id, preview.comment as name")->from(TABLE_DIPLOM_PREVIEWS . " as preview")->condition("preview.comment like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 5);
     }
     echo json_encode($res);
 }

 public function actionSearch()
 {
     $res = array();
     $term = CRequest::getString("query");
     /**
      * Сначала поищем по названию публикации
      */
     $query = new CQuery();
     $query->select("distinct(pub.id) as id, pub.name as title")->from(TABLE_PUBLICATIONS . " as pub")->condition("pub.name like '%" . $term . "%'")->limit(0, 5);
     if (CSession::getCurrentUser()->getLevelForCurrentTask() == ACCESS_LEVEL_READ_OWN_ONLY or CSession::getCurrentUser()->getLevelForCurrentTask() == ACCESS_LEVEL_WRITE_OWN_ONLY) {
         $query->innerJoin(TABLE_PUBLICATION_BY_PERSONS . " as p", "p.izdan_id = pub.id");
         $query->condition("p.kadri_id=" . CSession::getCurrentPerson()->getId());
     }
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("field" => "t.id", "value" => $item["id"], "label" => $item["title"], "class" => "CPublication");
     }
     echo json_encode($res);
 }

 /**
  * Количество записей в таблице
  *
  * @return int
  */
 public function getItemsCount()
 {
     if ($this->_manualAdded) {
         return $this->getCount();
     } else {
         $query = new CQuery();
         if (mb_strpos(mb_strtolower($this->getQuery()->getFields()), "distinct") === false) {
             $query->select("count(" . $this->getTableAlias() . ".id) as cnt");
         } else {
             $query->select("count(distinct " . $this->getTableAlias() . ".id) as cnt");
         }
         $query->from($this->getQuery()->getTable())->condition($this->getQuery()->getCondition());
         foreach ($this->getQuery()->getInnerJoins()->getItems() as $key => $value) {
             $query->innerJoin($key, $value);
         }
         foreach ($this->getQuery()->getLeftJoins() as $key => $value) {
             $query->leftJoin($key, $value);
         }
         $arr = $query->execute();
         if ($arr->getCount() > 0) {
             $item = $arr->getFirstItem();
             return $item["cnt"];
         } else {
             return 0;
         }
     }
 }

 public function actionSearch()
 {
     $term = CRequest::getString("query");
     $res = array();
     /**
      * Поиск по теме диплома
      */
     $query = new CQuery();
     $query->select("distinct(diplom.id) as id, diplom.dipl_name as name")->from(TABLE_DIPLOMS . " as diplom")->condition("diplom.dipl_name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 1);
     }
     /**
      * Поиск по ФИО студента
      */
     $query = new CQuery();
     $query->select("distinct(diplom.student_id) as id, student.fio as name");
     $query->innerJoin(TABLE_STUDENTS . " as student", "diplom.student_id=student.id")->from(TABLE_DIPLOMS . " as diplom")->condition("student.fio like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 2);
     }
     /**
      * Поиск по степени утверждения диплома
      */
     $query = new CQuery();
     $query->select("distinct(diplom.diplom_confirm) as id, confirm.name as name");
     $query->innerJoin(TABLE_DIPLOM_CONFIRMATIONS . " as confirm", "diplom.diplom_confirm = confirm.id")->from(TABLE_DIPLOMS . " as diplom")->condition("confirm.name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 3);
     }
     /**
      * Поиск по месту практики
      */
     $query = new CQuery();
     $query->select("distinct(diplom.id) as id, diplom.pract_place as name")->from(TABLE_DIPLOMS . " as diplom")->condition("diplom.pract_place like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 4);
     }
     /**
      * Поиск по месту практики по id
      */
     $query = new CQuery();
     $query->select("distinct(diplom.pract_place_id) as id, pract.name as name");
     $query->innerJoin(TABLE_PRACTICE_PLACES . " as pract", "diplom.pract_place_id = pract.id")->from(TABLE_DIPLOMS . " as diplom")->condition("pract.name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 5);
     }
     /**
      * Поиск по руководителю
      */
     $query = new CQuery();
     $query->select("distinct(diplom.kadri_id) as id, person.fio as name")->from(TABLE_DIPLOMS . " as diplom")->innerJoin(TABLE_PERSON . " as person", "diplom.kadri_id = person.id")->condition("person.fio like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 6);
     }
     /**
      * Поиск по группе
      */
     $query = new CQuery();
     $query->select("distinct(student.group_id) as id, st_group.name as name");
     $query->innerJoin(TABLE_STUDENTS . " as student", "diplom.student_id=student.id");
     $query->innerJoin(TABLE_STUDENT_GROUPS . " as st_group", "student.group_id = st_group.id")->from(TABLE_DIPLOMS . " as diplom")->condition("st_group.name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 7);
     }
     /**
      * Поиск по ин.яз.
      */
     $query = new CQuery();
     $query->select("distinct(diplom.foreign_lang) as id, lang.name as name");
     $query->innerJoin(TABLE_LANGUAGES . " as lang", "diplom.foreign_lang=lang.id")->from(TABLE_DIPLOMS . " as diplom")->condition("lang.name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 8);
     }
     /**
      * Поиск по рецензенту
      */
     $query = new CQuery();
     $query->select("distinct(diplom.recenz_id) as id, prepod.fio as name");
     $query->innerJoin(TABLE_PERSON . " as prepod", "diplom.recenz_id = prepod.id")->from(TABLE_DIPLOMS . " as diplom")->condition("prepod.fio like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 9);
     }
     /**
      * Поиск по оценке
      */
     $query = new CQuery();
     $query->select("distinct(diplom.study_mark) as id, mark.name as name");
     $query->innerJoin(TABLE_MARKS . " as mark", "diplom.study_mark = mark.id")->from(TABLE_DIPLOMS . " as diplom")->condition("mark.name like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 10);
     }
     /**
      * Поиск по комментарию
      */
     $query = new CQuery();
     $query->select("distinct(diplom.id) as id, diplom.comment as name")->from(TABLE_DIPLOMS . " as diplom")->condition("diplom.comment like '%" . $term . "%'")->limit(0, 5);
     foreach ($query->execute()->getItems() as $item) {
         $res[] = array("label" => $item["name"], "value" => $item["name"], "object_id" => $item["id"], "type" => 11);
     }
//.........这里部分代码省略.........