doctrine QueryBuilder -- Пример сложного запроса с подзапросом. expr->orX()

Primary tabs

melisa's picture
Submitted by melisa on Tue, 06/26/2018 - 17:15

Forums:

Задача

Написать сложный sql-запрос для поиска значений в таблице sale по нескольким фильтрам.

Сложность 1: не обязательно все фильтры переданы (могут быть null).
Сложность 2: несколько фильтров строгие (условия будут дополнять друг друга, необходимо найти пересечение результатов), а несколько - нестрогие (необходимо найти объединение результатов поиска по этим фильтрам). В итоге мы должны найти пересечение результатов поиска по первой группе фильтров и по второй.

Решение

Разберём сложные моменты по отдельности.

  1. Как проверить, какие фильтры передавать, а какие нет?
    Очень просто. Проверку делаем на уровне PHP. Цепочку запросов QueryBuilder-а можно прерывать и соответсвенно дополнять в процессе. Разобъём наш запрос: 
        // Создаём отдельно QueryBuilder
    $qb = $this->getEntityManager()
        ->createQueryBuilder();
    
    // Пишем начало запроса и помещаем в переменную для дальнейшего вызова
    $query = $qb->select('s')
        ->from(Sale::class, 's');

    // проверяем, передано ли условие и дополняем запрос
       if (!empty($rejectionFilter)) {
           $query->leftJoin(Rejection::class, 'r', 'with', "r.sale = s.id ")
                ->andWhere('r.sale is null');
        }

        if (!empty($dateFrom)) {
            $query->andWhere("s.date > '{$filterValue->format('Y-m-d H:i:s')}'");
        }

        if (!empty($dateTo)) {
            $query->andWhere("s.date < '{$filterValue->format('Y-m-d H:i:s')}'");
        }
  2. Архитектура запроса.
    несколько фильтров строгие (условия будут дополнять друг друга, необходимо найти пересечение результатов), а несколько - нестрогие (необходимо найти объединение результатов поиска по этим фильтрам). В итоге мы должны найти пересечение результатов поиска по первой группе фильтров и по второй

    Нам необходимо построить запрос типа 

    SELECT ... WHERE условие AND условие AND (WHERE условие OR условие)

    Подзапросы с помощью QueryBuilder-а делает метод expr(). Из документации:

    $qb->expr()->orX() // добавит нестрогое сравнение с результатом подзапроса
$qb->expr()->andX() // добавит строгое сравнение с результатом подзапроса

    и чтобы добавить условия в подзапрос, снова "разбиваем" цепочку и добавляем каждое отдельно:

        // продолжаем формировать наш запрос

    // Создаём отдельный подзапрос
    $orX = $qb->expr()->orX();
                
    // Добавляем в него условия
    if (!empty($sum)) {
        $orX->add("s.sum = $sum");
    }
        
    if (!empty($shopCashierName)) {
        $orX->add("sc.fullName = '$shopCashierName'");
    }
    
    // Присоединяем подзапрос к основному запросу
    $query->andWhere($orX);

    // И получаем результат
    return $query->orderBy('s.id', 'ASC')
        ->getQuery()
        ->getResult();

Параметры вместо переменных

Лучше для более безопасного запроса вместо конкатенации переменных с запросом добавлять их в виде параметров:

// параметр выглядит так
    ->andWhere('u.id = ?1')
// или так
    ->andWhere('u.id = :identifier')

//...

// а так устанавливаем значения параметрам  
   ->orderBy('u.name', 'ASC')
   ->setParameter(1, 100);
   ->setParameter('identifier', $identifier);

Так можно добавить параметр в подзапрос.

Итог

  • Для добавления "кусков" запроса разбиваем его на части и внедряем php между ними
  • Для комбинирования более простых запросов в один сложный используем подзапросы

Источники

Key Words for FKN + antitotal forum (CS VSU):

  • Log in to post comments
  • 4615 reads