Doctrine - Get Next and Previous Entry

Question

so I have some kind of allready entry. I have a "created" date field, and now I want to get the next and previous record by date.

Got a job:

$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 works very well. But these are 2 questions to the database. I need it. I can do this by simply connecting, etc., but when there is no next or not, I only get an empty array.

any idea?

Answer 1

Alexander's answer is close. When you request id < 2 LIMIT 1 , it will return 1 , but if you request for id < 5 LIMIT 1 , it will also return 1 . This is because it returns 1, 2, 3, 4 and takes the first element, which is 1 , not the required 4 .

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

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

Answer 2

An alternative approach to native SQL using the ORM entity repository.

 namespace EntityNamespace; use Doctrine\ORM\EntityRepository; class MyEntityRepository extends EntityRepository { /** * @param int $id * @return array|int[] */ public function filterNextPrevious($id) { $expr = $this->_em->getExpressionBuilder(); $qbNext = $this->createQueryBuilder('a') ->select(['MIN(a.id)']) ->where($expr->gt('a.id', ':id')); $qbPrevious = $this->createQueryBuilder('b') ->select(['MAX(b.id)']) ->where($expr->lt('b.id', ':id')); $query = $this->createQueryBuilder('m') ->select(['m.id']) ->where($expr->orX( $expr->eq('m.id', '(' . $qbNext->getDQL() . ')'), $expr->eq('m.id', '(' . $qbPrevious->getDQL() . ')') )) ->setParameter('id', $id) ->addOrderBy('m.id', 'ASC') ->getQuery(); //optionally enable caching //$query->useQueryCache(true)->useResultCache(true, 3600); return $query->getScalarResult(); } } 

Resulting DQL:

 SELECT m.id FROM EntityNamespace\Entity m WHERE m.id = ( SELECT MIN(a.id) FROM EntityNamespace\Entity a WHERE a.id > :id ) OR m.id = ( SELECT MAX(b.id) FROM EntityNamespace\Entity b WHERE b.id < :id ) ORDER BY m.id ASC 

(optionally, instead of using the query builder, use $this->_em->createQuery($DQL) )

Result Dataset:

 array(2) { [0]=> array(1) { ["id"]=> string(4) "5869" } [1]=> array(1) { ["id"]=> string(4) "5871" } } 

---

Another alternative to using the WHERE IN() is to use SELECT subqueries and, if necessary, add a common COUNT() to the base table.

This will allow you to assign the resulting value to the associated key and end the ORDER BY .

 $expr = $this->_em->getExpressionBuilder(); $next = $this->createQueryBuilder('a') ->select($expr->min('a.id')) ->where($expr->gt('a.id', ':id')); $previous = $this->createQueryBuilder('b') ->select($expr->max('b.id')) ->where($expr->lt('b.id', ':id')); $query = $this->createQueryBuilder('o') ->select('COUNT(o.id) as total') ->addSelect('(' . $previous->getDQL() . ') as previous') ->addSelect('(' . $next->getDQL() . ') as next') ->setParameter('id', $id) ->getQuery(); /* optionally enable caching * $query->useQueryCache(true)->useResultCache(true, 3600); */ return $query->getSingleResult(); 

DQL result:

 SELECT COUNT(o.id) as total, ( SELECT MAX(b.id) FROM EntityNamespace\Entity b WHERE b.id < :id ) as previous, ( SELECT MIN(a.id) FROM EntityNamespace\Entity a WHERE a.id > :id ) as next FROM EntityNamespace\Entity o 

Result Dataset:

 array(3) { ["total"]=> string(4) "63156" ["previous"]=> string(4) "5869" ["next"]=> string(4) "5871" } 

Answer 3

You can do this with your own queries:

 /** @var EntityManager $em */ $em = $this->getDoctrine()->getManager(); $rsm = new ResultSetMapping(); $rsm->addScalarResult('id', 'id'); $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 LIMIT 1)', $rsm); $users = $query->execute(); 

in the $ users variable you will have the following array:

 [ ['id' => 1] ['id' => 3] ] 

More details here http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html