doctrine - get next and prev record
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);
An alternative approach to native SQL, when using an 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 use instead of querybuilder via $this->_em->createQuery($DQL)
)
Resulting Dataset:
array(2) {
[0]=>
array(1) {
["id"]=>
string(4) "5869"
}
[1]=>
array(1) {
["id"]=>
string(4) "5871"
}
}
Another alternative to using the WHERE IN()
condition is to use SELECT
subqueries, and optionally add a total COUNT()
to the base table.
This will allow you to assign the resulting value to an associated key and do away with the ORDER BY
clause.
$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();
Result DQL:
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
Resulting Dataset:
array(3) {
["total"]=>
string(4) "63156"
["previous"]=>
string(4) "5869"
["next"]=>
string(4) "5871"
}
You can make it with native 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 $users variable you will have following array:
[
['id' => 1]
['id' => 3]
]
More details here http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html