/**
* @param Request $request
*
* @return Pagerfanta
*/
public function getFlagsPager(Request $request)
{
$results = $this->getAll($request);
$adapter = new ArrayAdapter($results);
$pager = new Pagerfanta($adapter);
$pageNumber = $request->query->get('page', 1);
$pager->setMaxPerPage(40);
try {
$pager = $pager->setCurrentPage($pageNumber);
} catch (\Exception $e) {
$adapter = new ArrayAdapter(array());
$pager = new OutOfRangePager($adapter);
$pager->setOriginalPage($pageNumber);
}
return $pager;
}
/**
* @param QueryBuilder $queryBuilder
* @param integer $pageNumber
*
* @return Pagerfanta
*/
protected function getPager($queryBuilder, $pageNumber = 1)
{
$adapter = new DoctrineORMAdapter($queryBuilder);
$pager = new Pagerfanta($adapter);
try {
$pager = $pager->setCurrentPage($pageNumber);
} catch (\Exception $e) {
$adapter = new ArrayAdapter(array());
$pager = new OutOfRangePager($adapter);
$pager->setOriginalPage($pageNumber);
}
return $pager;
}
/**
* @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;
}
/**
* @param string $name
* @param User $user
* @param int $pageNumber
*
* @return Pagerfanta
*/
public function getUsersWithCommonFollowers($name, User $user, $pageNumber = 1)
{
$sqlAllUsers = "SELECT\n users.id as user_id,\n users.full_name as full_name,\n users.avatar as avatar,\n 0 as common_followers_count\n FROM users\n WHERE users.id <> :user_id\n AND full_name LIKE :name";
$sqlCommonLeaders = "SELECT\n c2.leader_id as user_id,\n '' as full_name,\n '' as avatar,\n COUNT(c1.follower_id) as common_followers_count\n FROM connections c1\n INNER JOIN connections c2\n ON c1.follower_id = c2.follower_id\n AND c1.leader_id <> c2.leader_id\n WHERE c1.leader_id = :user_id\n AND c1.status = :connection_status\n GROUP BY c1.leader_id, c2.leader_id";
$sql = "SELECT\n results.user_id as id,\n results.full_name as full_name,\n results.avatar as avatar,\n SUM(results.common_followers_count) as common_followers_count\n FROM (%s UNION ALL %s) AS results\n GROUP BY id\n HAVING full_name LIKE :name\n ORDER BY common_followers_count DESC";
$sql = sprintf($sql, $sqlAllUsers, $sqlCommonLeaders);
$stmt = $this->getEntityManager()->getConnection()->prepare($sql);
$stmt->bindValue('connection_status', ConnectionStatus::APPROVED);
$stmt->bindValue('user_id', $user->getId());
$stmt->bindValue('name', '%' . $name . '%');
$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);
}
return $pager;
}
