Как комбинировать параметры в нативных запроса?

Question

[Игорь]

Коллеги, вопрос наиглупейший!

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

Что я хочу получить?
Количество компаний, в соответствии с заданными параметрами

SQL

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

EXPLAIN 

SELECT         COUNT(o.id) 
FROM           organizations o 
LEFT JOIN      organizations_categories oc ON o.id = oc.organization_id AND oc.category_id = 9
WHERE o.id     IN (
      SELECT   f.organization_id 
      FROM     features f 
      WHERE    f.name = (
                     SELECT   f2.name 
                     FROM     features f2 
                     WHERE    f2.id = 5685427
                     ) 
                 ) 
AND  o.city_id = 5128 AND oc.category_id = 9

Таблицы, которые участвуют в запросе
----------------------------------------------------------
- organizations: rows count 5 000 000
- organizations_categorie: rows count 7 000 000
- features: rows count ~7 000 000
----------------------------------------------------------

Привожу пример:
(Заготовка)

0 - означает параметр не передан и не должен учавствовать в запросе.

/**
     * @param string $q
     * @param int $city_id
     * @param int $category_id
     * @param int $feature_id
     * @return void
     * @throws NonUniqueResultException
     */
    public function getCount($q = "", int $city_id = 0, int $category_id = 0, int $feature_id = 0)
    {
        $rsm = new ResultSetMapping;
        $rsm->addScalarResult('COUNT(o.id)', 'total');

        $query = $this->getEntityManager()->createNativeQuery("
            SELECT         COUNT(o.id) 
            FROM           organizations o 
            LEFT JOIN      organizations_categories oc ON o.id = oc.organization_id AND oc.category_id = 9
            WHERE o.id     IN (
                SELECT          f.organization_id 
                FROM            features f 
                WHERE           f.name = (
                    SELECT          f2.name 
                    FROM            features f2 
                    WHERE           f2.id = 5685427
                                ) 
                            ) 
            AND  o.city_id = 5128 AND oc.category_id = 9
        ", $rsm);


        return $query->getSingleScalarResult();
    }

А может есть вариант с помощью билдера?
Да, я пробовал, но он запрос собирает по своим правилам и зачастую эти запросы могут выполнять по 5 минут.

Да, я могу наставить костылей, все будет работать.
Хочу красиво.

Поделитесь практикой.

Спасибо!

Comments

[Сергей c0re]

а чем на нативном SQL не красиво? )

[Игорь]

Сергей, Я имею в виду, как красиво играть условиями выборки на нативном SQL без кишкокода))

В Doctrine orm есть инструмент "фильтры" но так скудно описаны, что кажется это не о то что я ищу.

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

[Сергей c0re]

Игорь, а sprintf не красиво? )

как-то так...

$sql = "
            SELECT         COUNT(o.id) 
            FROM           organizations o 
            LEFT JOIN      organizations_categories oc ON o.id = oc.organization_id
            WHERE o.id     IN (
                SELECT          f.organization_id 
                FROM            features f 
                WHERE           f.name = (
                    SELECT          f2.name 
                    FROM            features f2 
                    WHERE           f2.id = %d
                                ) 
                            ) 
            AND  o.city_id = %d
            AND oc.category_id = %d
";
$sql = sprintf($sql, $features_id, $city_id, $category_id);

$query = $this->getEntityManager()->createNativeQuery($sql, $rsm);

[Игорь]

Сергей, Не, не в этом вопрос.
Обратите внимание на параметры.

Они по дефолту выставлены.

Если все параметры по дефолту, то

SELECT COUNT(o.id)  FROM  organizations o  LEFT JOIN organizations_categories oc ON o.id = oc.organization_id

Если $category_id положительный то

SELECT COUNT(o.id)  FROM  organizations o  
LEFT JOIN organizations_categories oc ON o.id = oc.organization_id AND oc.category_id = 9
WHERE oc.category_id = 9

И так далее...

16 вариантов комбинаций это не шутки...

[Игорь]

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

[Сергей c0re]

Игорь, зачем писать
oc.category_id = 9 в условие объединения и в where одновременно?
либо там , либо там
если вы вставляете и там и там это все равно что в where , имхо эффекта от left join не будет.

[Игорь]

Сергей, Ок.
Я думал что это как то облегчит судьбу JOIN
Но это касается правильности запроса.

Наверняка есть инструмент, который позволит легко играть условиями, я ищу его.

[Игорь]

Сергей,

oc.category_id = 9 в условие объединения и в where одновременно?

Да, действительно, эффект один и тот же.

[Сергей c0re]

готовых решений не знаю, но можно сделать свое - функцию или метод класса, которая будет обрабатывать sql
sql будет вида, ну примерно

...
...
    WHERE 1=1
          ?  AND  o.city_id = $1 ?
          ?  AND oc.category_id = $2 ?

регуляркой это дело соответственно включать или вырезать
если параметр есть, то включать в запрос, если нет - вырезать.
т.е. в функцию передаем такой sql и параметры, она возвращает отформатированный sql

как-то так.

[Игорь]

Спасибо, попробую на досуге.