/** * @param Request $request * @param User $user * * @throws \Doctrine\DBAL\DBALException * * @return ArrayAdapter */ public function getAllExperiencedBy(Request $request, User $user) { $userId = $request->query->get('user'); $pageNumber = $request->query->get('page', 1); $sql = ' SELECT DISTINCT geonames.id, ascii_name as asciiName, lat, lng, country, admin1, population, timezone, ascii_name as formattedName, currency_symbol as currencySymbol, currency_code as currencyCode FROM geonames RIGHT JOIN reviews ON geonames.id = reviews.geoname_id WHERE reviews.user_id = :user_id OR geonames.id = :geonames_id ORDER BY geonames.population DESC'; $stmt = $this->getEntityManager()->getConnection()->prepare($sql); $stmt->bindValue('user_id', $userId); $stmt->bindValue('geonames_id', $user->getId()); $stmt->execute(); $results = $stmt->fetchAll(); $adapter = new ArrayAdapter($results); $pager = new Pagerfanta($adapter); try { $pager = $pager->setCurrentPage($pageNumber); } catch (\Exception $e) { $adapter = new ArrayAdapter(array()); $pager = new OutOfRangePager($adapter); $pager->setOriginalPage($pageNumber); } $pager->setMaxPerPage(20); return $pager; }