/** * @author DuongNQ * @param \Expert\Model\Expert\Subject $expertsub */ public function search($expertsub, $options) { $select = $this->getDbSql()->select(array('es' => self::TABLE_NAME)); if ($expertsub->getSubjectId()) { $select->where(['es.subjectId' => $expertsub->getSubjectId()]); } if ($expertsub->getOption('subjectIds')) { $select->where(['es.subjectId' => $expertsub->getOption('subjectIds')]); } $select->order(['es.id' => 'DESC']); $select->group('es.expertId'); // vdump($this->getDbSql()->buildSqlString($select));die; $paginator = $this->preparePaginator($select, $options, new Subject()); $userIds = array(); $users = array(); /** @var \Expert\Model\Expert\Subject $es */ foreach ($paginator as $es) { $userIds[] = $es->getExpertId(); } $subjects = $this->fetchAllSubject($expertsub->addOption('expertIds', $userIds)); if ($userIds) { $select = $this->getDbSql()->select(['u' => UserMapper::TABLE_NAME]); $select->where(['u.id' => $userIds]); $query = $this->getDbSql()->buildSqlString($select); $result = $this->getDbAdapter()->query($query, Adapter::QUERY_MODE_EXECUTE); if (count($result)) { $resultArray = $result->toArray(); foreach ($resultArray as $u) { $user = new User(); $users[$u['id']] = $user->exchangeArray($u); } } } /** @var \Expert\Model\Expert\Subject $expertsub */ foreach ($paginator->getCurrentModels() as $expertsub) { $userId = $expertsub->getExpertId(); $expertsub->addOption('subject', isset($subjects[$userId]) ? $subjects[$userId] : null); $expertsub->addOption('user', isset($users[$userId]) ? $users[$userId] : null); } return $paginator; }
/** * @author DuongNQ * @param \Expert\Model\Expert\Subject $exp */ public function featchAll($exp) { $select = $this->getDbSql()->select(array('es' => self::TABLE_NAME)); $select->columns(['subjectId']); $select->join(['e' => ExpertMapper::TABLE_NAME], 'e.id=es.expertId', array('id')); $select->join(['s' => \Subject\Model\SubjectMapper::TABLE_NAME], 's.id=es.subjectId', ['subjectName' => 'name']); $select->order(['e.id' => 'DESC']); if ($exp->getOption('subjectIds')) { $select->where(['subjectId' => $exp->getOption('subjectIds')]); } else { $select->where(['subjectId' => $exp->getSubjectId()]); } $query = $this->getDbSql()->buildSqlString($select); $results = $this->getDbAdapter()->query($query, Adapter::QUERY_MODE_EXECUTE); $subjects = []; if (count($results)) { $results = $results->toArray(); foreach ($results as $r) { $subjects[$r['id']][] = $r['subjectName']; $expertIds[] = $r['id']; } } unset($select); $select = $this->getDbSql()->select(array('e' => ExpertMapper::TABLE_NAME)); $select->columns(['id', 'description', 'rating', 'rate', 'extraContent']); $select->where(['e.id' => $expertIds]); $select->join(['u' => UserMapper::TABLE_NAME], 'e.userId = u.id', ['username', 'fullName']); $query = $this->getDbSql()->buildSqlString($select); $results = $this->getDbAdapter()->query($query, Adapter::QUERY_MODE_EXECUTE); if (count($results)) { $results = $results->toArray(); foreach ($results as $r) { if (isset($subjects[$r['id']])) { $r['subject'] = $subjects[$r['id']]; } $mentors[] = $r; } } return $mentors; }