doctrine - get next and prev record

doctrine, mysql, php, symfony

Solution

Alexandr's response is close. When you query for `id < 2 LIMIT 1` it will return `1`, but if you query for `id < 5 LIMIT 1` this will also return `1`. That is because it returns `1, 2, 3, 4` and takes the first element, which is `1` rather than the needed `4`.

Just add `ORDER BY id DESC` to get the previous item. This will return `4, 3, 2, 1` and the `LIMIT 1` will return `4`, or the previous element.

$query = $em->createNativeQuery('SELECT id FROM users WHERE
        id = (SELECT id FROM users WHERE id > 2 LIMIT 1)
        OR
        id = (SELECT id FROM users WHERE id < 2 ORDER BY id DESC LIMIT 1)', $rsm);

Problem

just so i have some record allready fetched. I have date field 'created' and now I want to get next and prev record by date. Got it working by: ``` $qb = $this->createQueryBuilder('a'); $next = $qb->expr()->gt('a.created', ':date'); $prev = $qb->expr()->lt('a.created', ':date'); $prev = $qb->select('partial a.{id,title,created}') ->where($prev) ->setParameter('date', $date) ->orderBy('a.created', 'DESC') ->setMaxResults(1) ->getQuery() ->getArrayResult(); $next = $qb->select('partial a.{id,title,created}') ->where($next) ->setParameter('date', $date) ->orderBy('a.created', 'DESC') ->setMaxResults(1) ->getQuery() ->getArrayResult(); ``` it working very well. But this is 2 question to database. I need one. I can do this by just join etc., but when there is no next or no prev I got just an empty array. any idea?

Original source