How to get previous and next entity through entity manager

Question

This is how i'm able to fetch an user entity using it's id:

$userEntity = $em->getRepository('ModelBundle:User')->find($userId);

Is there a way to get previous and next entity based on this $userEntity?

For example: i fetched $userEntity and it's id is 100, now i want fetch previous entity irrespective to its id 99, 80, 70 whatever it is! same with case with next entity..

I can already fetch next and previous using some PHP hack, however i'm looking for better approach..

Edited: Just to clarify i'm looking for object oriented approach, something like below:

$userEntity = $em->getRepository('ModelBundle:User')->find($userId);
$previousUserEntity = $userEntity->previous(); //example
$previousUserEntity = $userEntity->next(); //example

I know userEntity does't contain any method like previous() and next(), it's just an example, if anything like that exist which can help directly get next and previous entities in object oriented fashion!

Answer 1

Add this funstion to your repository class

public function previous(User $user)
{
    return $this->getEntityManager()
        ->createQuery(
            'SELECT u
            FROM ModelBundle:User u
            WHERE u.id = (SELECT MAX(us.id) FROM ModelBundle:User us WHERE us.id < :id )'
        )
        ->setParameter(':id', $user->getId())
        ->getOneOrNullResult();
}

public function next(User $user)
{
    return $this->getEntityManager()
        ->createQuery(
            'SELECT u
            FROM ModelBundle:User u
            WHERE u.id = (SELECT MIN(us.id) FROM ModelBundle:User us WHERE us.id > :id )'
        )
        ->setParameter(':id', $user->getId())
        ->getOneOrNullResult();
}

Answer 2

It's possible to get the previous and next records by searching for all the records with a value greater or less than a given value, sort them and then take only the first or last result.

Let's say we have these ids:

70
80
99
100

In order to get the user before 99, we want the last user which id is less than 99. Here is the code for adding this in a custom repository:

public function getPreviousUser($userId)
{
    $qb = $this->createQueryBuilder('u')
        ->select('u')

        // Filter users.
        ->where('u.id < :userId')
        ->setParameter(':userId', $userId)

        // Order by id.
        ->orderBy('u.id', 'DESC')

        // Get the first record.
        ->setFirstResult(0)
        ->setMaxResults(1)
    ;

    $result = $qb->getQuery()->getOneOrNullResult();
}

And in the controller:

$em->getRepository('ModelBundle:User')->getPreviousUser($userId);

This will return the record with id 80.

---

In order to get the user after 99, we want the first user which id is greater than 99:

public function getNextUser($userId)
{
    $qb = $this->createQueryBuilder('u')
        ->select('u')

        ->where('u.id > :userId')
        ->setParameter(':userId', $userId)

        ->orderBy('u.id', 'ASC')

        ->setFirstResult(0)
        ->setMaxResults(1)
    ;

    $result = $qb->getQuery()->getOneOrNullResult();
}

And in the controller:

$em->getRepository('ModelBundle:User')->getNextUser($userId);

This will return the record with id 100.