第二个分页计数查询 - Second counting query for pagination

Second counting query for pagination

本文关键字：查询分页第二个 | 更新日期: 2023-09-27

我有几个非常复杂的查询，对于每个查询，我都必须编写第二个查询计数结果。例如，在模型中:

$dql = "SELECT u FROM AcmeBundle:Users u LEFT JOIN AcmeBundle:Products p WITH u.id = p.id";

我必须创建一个重复的查询，像这样:

$countingQuery = "SELECT COUNT(u.id) FROM AcmeBundle:Users u LEFT JOIN AcmeBundle:Products p WITH u.id = p.id";

这样做的主要问题是，随着第一个查询的每次更改，我也必须更改第二个查询。

所以我想到了另一个主意:

$countingSelect = "SELECT COUNT(u.id)";
$noncountingSelect = "SELECT u";
$dql = " FROM AcmeBundle:Users u LEFT JOIN AcmeBundle:Products p WITH u.id = p.id";
return $this->getEntityManager()->createQuery($noncountingSelect . $dql)
->setHint('knp_paginator.count', $this->getEntityManager()->createQuery($countingSelect . $dql)->getSingleScalarResult());

它当然可以工作，但是对于较大的选择，解决方案似乎相当丑陋。

我该如何解决这个问题?

我相信Doctrine'ORM'Tools'Pagination'Paginator将满足您的要求，而不需要额外的复杂性。

$paginator = new Paginator($dql);
$paginator
    ->getQuery()
    ->setFirstResult($pageSize * ($currentPage - 1)) // set the offset
    ->setMaxResults($pageSize); // set the limit
$totalItems = count($paginator);
$pagesCount = ceil($totalItems / $paginator->getMaxResults());

代码摘自:http://showmethecode.es/php/doctrine2/doctrine2-paginator/

您可以像文档中解释的那样创建一个客户存储库，并通过像..这样的小编辑将您的查询添加到其中

use Doctrine'ORM'EntityRepository;
class ProductRepository extends EntityRepository
{
    public function findProducts()
    {
        return $this->findProductsOrCountProducts();
    }
    public function findCountProducts()
    {
        return $this->findProductsOrCountProducts(true);
    }
    private function findProductsOrCountProducts($count = false)
    {
        $queryBuilder = $this->createQueryBuilder('u');
        if ($count) {
            $queryBuilder->select('COUNT(u.id)');
        }
        $query = $queryBuilder
            ->leftJoin('AcmeBundle:Products', 'p', 'WITH', 'u.id = p.id')
            ->getQuery()
        ;
        if ($count) {
            return $query->getSingleScalarResult();
        } else {
            return $query->getResult();
        }
    }
}

然后你可以使用…

$repository = $this->getDoctrine()
    ->getRepository('AcmeBundle:Users');
// for products
$products = $repository->findProducts();
// for count
$countProducts = $repository->findCountProducts();

注意:

我知道这不是最好的做法，只是说，看看文档的客户存储库位，这里是YAML映射…

# src/Acme/StoreBundle/Resources/config/doctrine/Product.orm.yml
Acme'StoreBundle'Entity'Product:
    type: entity
    repositoryClass: Acme'StoreBundle'Entity'ProductRepository
    # ...