如何在其他查询中将格式化原则 2 与 QueryBuilder 格式查询重用

Question

我已经用原则查询生成器格式化了查询。这有点复杂，因为在这个查询中有一些我自己定义的学说功能和学说扩展。同样重要的是要提到，此查询使用过滤器需要一些预选值。问题是当我想计算该查询中的记录时。当然我可以写计数(结果)，但这对性能非常不利。

我尝试使用我自己的预定义查询作为源创建新的sql查询，但是没有注入参数的问题。

查询哪个格式以及我要计算的结果：

private function getCategoriesQuery(Filter $filter = null, Sort $sort = null, Pagination $pagination = null)
{
$qb = $this->createQueryBuilder('c')
->select('c.id as id')
->addSelect('c.title as title')
->addSelect('DATE_FORMAT(c.updationDate, '%Y-%m-%d %H:%i:%S') as updationDate')
->addSelect('DATE_FORMAT(c.creationDate, '%Y-%m-%d %H:%i:%S') as creationDate')
->addSelect('coalesce(sum(r.amount), 0.00) as total')
->addSelect('round(AveragePerMonth(coalesce(sum(r.amount), 0.00), c.creationDate, CURRENT_DATE()), 2) as averagePerMonth')
->leftJoin('c.records', 'r')
->groupBy('c.id')
->andWhere('c.user = :user')->setParameter('user', $this->user);
if ($filter) $this->applyFilters($filter, $qb);
if ($sort) $this->applySort($sort, $qb);
if ($pagination) $this->applyPagination($pagination, $qb);
return $qb->getQuery();
}

我试过了：

public function countUserCategories(Filter $filter = null)
{
$rsm = new ResultSetMapping();
$sql = $this->_em->createNativeQuery('select count(*) from ('.$this->getCategoriesQuery($filter)->getSQL().') as src', $rsm);
return $sql->getSingleScalarResult();
}

如果有可能将这些查询结合起来，并且有可能，我该如何做到这一点，我期待答案。谢谢

应用过滤方法：

private function applyFilters(Filter $filter, QueryBuilder $qb)
{
if ($filter->getCategories()) {
$qb->andWhere('c.id IN(:categories)')->setParameter('categories', $filter->getCategories());
}
if ($filter->getEndDate()) {
$qb->andWhere('r.date <= :endDate')->setParameter('endDate', $filter->getEndDate());
}
if ($filter->getStartDate()) {
$qb->andWhere('r.date >= :startDate')->setParameter('startDate', $filter->getStartDate());
}
if ($filter->getStartTotal() !== null) {
$qb->andHaving('total >= :startTotal')->setParameter('startTotal', $filter->getStartTotal());
}
if ($filter->getEndTotal() !== null) {
$qb->andHaving('total <= :endTotal')->setParameter('endTotal', $filter->getEndTotal());
}
if ($filter->getStartAveragePerMonth() !== null) {
$qb->andHaving('averagePerMonth >= :startAveragePerMonth')->setParameter('startAveragePerMonth', $filter->getStartAveragePerMonth());
}
if ($filter->getEndAveragePerMonth() !== null) {
$qb->andHaving('averagePerMonth <= :endAveragePerMonth')->setParameter('endAveragePerMonth', $filter->getEndAveragePerMonth());
}
}

解决此问题的建议方法，但返回响应：The query returned multiple rows. Change the query or use a different result function like getScalarResult().

public function countUserCategories(Filter $filter = null)
{
return (clone $this->getCategoriesQueryBuilder($filter))
->resetDQLPart('select')
->select('COUNT(c)')
->getQuery()
->getSingleScalarResult();
}

更新

我通过使用SQL查询生成器来完成这项工作

查询生成器创建

private function getCategoriesQueryBuilder(Filter $filter = null, Sort $sort = null, Pagination $pagination = null)
{
$qb = $this->_em->getConnection()->createQueryBuilder()
->select('c.id AS id')
->addSelect('c.title AS title')
->addSelect('DATE_FORMAT(c.updation_date, '%Y-%m-%d %H:%i:%S') AS updationDate')
->addSelect('DATE_FORMAT(c.creation_date, '%Y-%m-%d %H:%i:%S') AS creationDate')
->addSelect('COALESCE(SUM(r.amount), 0.00) AS total')
->addSelect('(SELECT ROUND(COALESCE(SUM(r.amount), 0.00) / COUNT(DISTINCT(DATE_FORMAT(calendar.date, '%Y-%m'))), 2)
FROM calendar as calendar
WHERE calendar.date BETWEEN DATE_FORMAT(c.creation_date, '%Y-%m-%d') AND DATE_FORMAT(CURRENT_DATE(), '%Y-%m-%d')) 
AS averagePerMonth')
->from('category', 'c')
->leftJoin('c', 'record', 'r', 'r.category_id = c.id')
->join('c', 'users', 'u', 'u.id = c.user_id')
->where('u.id = :userId')->setParameter('userId', $this->user->getId())
->groupBy('c.id');
if ($sort) $this->applySort($sort, $qb);
if ($pagination) $this->applyPagination($pagination, $qb);
if ($filter) $this->applyFilter($filter, $qb);
return $qb;
}

滤波器

private function applyFilter(Filter $filter, QueryBuilder $qb)
{
if ($filter->getCategories()) {
$qb->andWhere('c.id IN(:categories)')->setParameter('categories', $filter->getCategories());
}
if ($filter->getEndDate()) {
$qb->andWhere('r.date <= :endDate')->setParameter('endDate', $filter->getEndDate());
}
if ($filter->getStartDate()) {
$qb->andWhere('r.date >= :startDate')->setParameter('startDate', $filter->getStartDate());
}
if ($filter->getStartTotal() !== null) {
$qb->andHaving('total >= :startTotal')->setParameter('startTotal', $filter->getStartTotal());
}
if ($filter->getEndTotal() !== null) {
$qb->andHaving('total <= :endTotal')->setParameter('endTotal', $filter->getEndTotal());
}
if ($filter->getStartAveragePerMonth() !== null) {
$qb->andHaving('averagePerMonth >= :startAveragePerMonth')->setParameter('startAveragePerMonth', $filter->getStartAveragePerMonth());
}
if ($filter->getEndAveragePerMonth() !== null) {
$qb->andHaving('averagePerMonth <= :endAveragePerMonth')->setParameter('endAveragePerMonth', $filter->getEndAveragePerMonth());
}
}

计数

public function countUserCategories(Filter $filter = null)
{
$subQuery = $this->getCategoriesQueryBuilder($filter);
$params = $subQuery->getParameters();
return $this->_em->getConnection()->createQueryBuilder()
->select('COUNT(src.id)')
->from('(' . $subQuery . ')', 'src')
->setParameters($params)
->execute()
->fetchColumn(0);
}

获取

public function findUserCategories(Filter $filter = null, Sort $sort = null, Pagination $pagination = null)
{
return $this->getCategoriesQueryBuilder($filter, $sort, $pagination)->execute()->fetchAll();
}

Answer 1

可以使用原始查询作为子查询来获取计数。

public function countUserCategories(Filter $filter = null)
{
$querysub = $this->getCategoriesQuery($filter);
$query = $this->getDoctrine()->getConnection()->createQueryBuilder();
$query
->select('COUNT(*)')
->from('(' . $querysub->getSQL() . ')', 'ttt');
$i = 0;
foreach ($querysub->getParameters() as $parameter) {
$query->setParameter($i++, $parameter->getValue(), $parameter->getType());
}
$count = $query->execute()->fetchColumn(0);
return $count;
}

如果参数按照其在查询中的出现顺序设置，这将起作用。