Блог

Я отформатировал запрос с помощью doctrine query builder. Это довольно сложно, потому что в этом запросе есть некоторые мои собственные определенные функции doctrine и расширения doctrine. Также очень важно упомянуть, что для этого запроса с использованием фильтра требуются некоторые предварительно выбранные значения. Проблема в том, что я хочу подсчитать записи из этого запроса. Конечно, я могу написать количество (результаты), но это очень плохо сказывается на производительности.

Я пытаюсь создать новый 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();
}
 

Я ожидал ответа, если есть возможность объединить эти запросы и, если есть возможность, как я могу это сделать. Спасибо

Метод applyFilter:

 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();
}
 

UPDATE

I make this work by using SQL query builder

Query builder creation

 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();
}
 

Вы можете использовать исходный запрос в качестве подзапроса для получения count.

 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;
}
 

Это будет работать, если параметры заданы в порядке их появления в запросе.