教义 - 获取下一个和上一个记录

2022-01-16 00:00:00 next mysql symfony doctrine

只是这样我已经获取了一些记录.我有创建"日期字段，现在我想按日期获取下一个和上一个记录.

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.

得到它的工作:

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

它工作得很好.但这是数据库的 2 个问题.我需要一个.我可以通过加入等来做到这一点，但是当没有下一个或没有上一个时，我得到的只是一个空数组.

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.

有什么想法吗?

推荐答案

Alexandr 的回应很接近.当您查询 id <2 LIMIT 1 它将返回 1，但如果您查询 id <5 LIMIT 1 这也将返回 1.那是因为它返回 1, 2, 3, 4 并取第一个元素，即 1 而不是所需的 4.

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.

只需添加 ORDER BY id DESC 即可获取上一项.这将返回 4, 3, 2, 1 并且 LIMIT 1 将返回 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);

相关文章