PHP CQuery::leftJoin方法代码示例

 public function actionIndex()
 {
     $set = new CRecordSet();
     $query = new CQuery();
     $query->select("gr.*")->from(TABLE_GRANTS . " as gr")->order("gr.id desc");
     if (CSession::getCurrentUser()->getLevelForCurrentTask() == ACCESS_LEVEL_READ_OWN_ONLY || CSession::getCurrentUser()->getLevelForCurrentTask() == ACCESS_LEVEL_WRITE_OWN_ONLY) {
         $query->leftJoin(TABLE_GRANT_MEMBERS . " as m", "m.grant_id = gr.id");
         $query->condition("gr.author_id=" . CSession::getCurrentPerson()->getId() . " OR m.person_id=" . CSession::getCurrentPerson()->getId() . " or gr.manager_id=" . CSession::getCurrentUser()->getId());
     }
     $set->setQuery($query);
     $grants = new CArrayList();
     foreach ($set->getPaginated()->getItems() as $ar) {
         $grant = new CGrant($ar);
         $grants->add($grant->getId(), $grant);
     }
     $this->setData("grants", $grants);
     $this->setData("paginator", $set->getPaginator());
     $this->renderView("_grants/grant/index.tpl");
 }

 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);
//.........这里部分代码省略.........

 /**
  * Количество записей в таблице
  *
  * @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;
         }
     }
 }