这样我就可以提取一些记录allready了。我已经创建了日期字段,现在我想按日期获取下一个和上一个记录。
工作人员:
$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();
它工作得很好。但这是对数据库的两个问题。我需要一个。我可以通过加入等来做到这一点,但当没有下一个或上一个时,我得到的只是一个空数组。
知道吗?
Alexandr的反应很接近。当查询id < 2 LIMIT 1
时,它将返回1
,但如果查询id < 5 LIMIT 1
,它也会返回1
。这是因为它返回1, 2, 3, 4
并获取第一个元素,即1
,而不是所需的4
。
只需添加ORDER BY id DESC
即可获得上一项。这将返回4, 3, 2, 1
,而LIMIT 1
将返回4
或前一个元素。
$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);
使用ORM实体库时,本机SQL的替代方法。
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();
}
}
结果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
(可通过$this->_em->createQuery($DQL)
选择使用而不是querybuilder)
结果数据集:
array(2) {
[0]=>
array(1) {
["id"]=>
string(4) "5869"
}
[1]=>
array(1) {
["id"]=>
string(4) "5871"
}
}
使用WHERE IN()
条件的另一个替代方案是使用SELECT
子查询,并可选地将总COUNT()
添加到基表。
这将允许您将结果值分配给关联的键,并取消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:
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
结果数据集:
array(3) {
["total"]=>
string(4) "63156"
["previous"]=>
string(4) "5869"
["next"]=>
string(4) "5871"
}
您可以使用本机查询:
/** @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();
在$users变量中,您将拥有以下数组:
[
['id' => 1]
['id' => 3]
]
此处提供更多详细信息http://doctrine-orm.readthedocs.org/en/latest/reference/native-sql.html