/**
* @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;
}
