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!

`$previousUserEntity = $userEntity->previous();` This would require to inject the entity manager in your entity, this is not recommended. If you want to do it, you'll find solutions here: http://stackoverflow.com/a/15556566/2257664 http://stackoverflow.com/q/14684745/2257664 — A.L, May 02 '16 at 18:31
Indeed, i'm not able to find it any where so i must accept your answer as i used it instead of mine :) thanks, — Muzafar Ali, May 02 '16 at 18:45
You're welcome, like it's written in the answers I linked, it's cleaner to separate entities and database logic. Thanks for the correction of my answer! — A.L, May 02 '16 at 20:05

score 6 · Answer 1 · answered May 02 '16 at 13:16

6

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();
}

answered May 02 '16 at 13:16

Denis Alimov

2,861
1
18
38

Thanks for the response Denis, i'm looking for any OO solution, may be through doctrine2? – Muzafar Ali May 02 '16 at 14:21
2

@MuzafarAli what do you mean by OO solution? this solution uses dql, so it is through doctrine2 – Denis Alimov May 02 '16 at 15:06
He probably means that he wants to access the next and prev objects using a method of an entity instance, but I don't think it's possible – Paul ALBERT Dec 19 '21 at 12:20

score 4 · Accepted Answer · edited May 02 '16 at 18:50

4

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:

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.

edited May 02 '16 at 18:50

Muzafar Ali

1,362
1
12
18

answered May 02 '16 at 13:18

A.L

10,259
10
67
98

Thanks A.L, as per my 1st comment, i'm looking for any OO solution through Doctrine2.. – Muzafar Ali May 02 '16 at 14:23
@MuzafarAli I'm sorry I don't understand your question, do you mean you don't want to use a repository? – A.L May 02 '16 at 15:07
nope, i was speaking about object oriented approach.. for example in ONE-TO-MANY i can do like this "$groupEntity->getUsers()->first()", in this way i'm able to get first record.. – Muzafar Ali May 02 '16 at 17:09
@MuzafarAli I'm sorry but I don't understand. Can you please add an example of the expected code in your question? – A.L May 02 '16 at 17:27
Great answer. You are a great hero if you can implement this vanilla MySQL solution as well, to get both 'neighbors' in a single query: http://stackoverflow.com/a/15992856/1405981 – Stephan Vierkant May 02 '16 at 17:48
@StephanVierkant this should be possible by modifying a little bit the code provided in the [answer from Denis Alimov](http://stackoverflow.com/a/36983412/2257664). – A.L May 02 '16 at 18:26

How to get previous and next entity through entity manager

2 Answers2