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

doctrine - get next and prev record(教义 - 获取下一个和上一个记录)
本文介绍了教义 - 获取下一个和上一个记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

这篇关于教义 - 获取下一个和上一个记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)
SQL/MySQL: split a quantity value into multiple rows by date(SQL/MySQL:按日期将数量值拆分为多行)