Еще один пример сложных SQL-запросов

Fi1osof
13 нояб. 2015 г., 6:07

Еще один пример сложных SQL-запросов

Вчера на modx.pro был найден довольно интересный вопрос. Пока я писал там ответ, понял, что его имеет смысл вынести в отдельный топик, так как он получился довольно объемный и раскрывает несколько тонких моментов. Крайне советую к изучению практикующим xPDO.
Топикстартер абсолютно прав:
у меня возникает ощущение, что я пытаюсь найти строки в которых значение ps_type одновременно равно и «Подвесной», и «Административный», что, закономерно, ни к чему не приводит.
Да, нельзя сделать выбоку с условием, которое требует разных значений от одной и той же строки. Но и условие OR тоже нельзя использовать, так как это приведет к выборке в том числе и тех товаров, у которых имеется только “Административный”, но нет других необходимых значений.
Предложенный Сергеем запрос годится, но только на выборку одного типа товаров, в данном случае «Подвесной, Административный». Но сразу выбрать еще и «Административный, Промышленный, Подвесной» он не позволит. (На самом деле в данном случае конечно же позволит, ибо вхождение “Подвесной, Административный” имеется и в “Административный, Промышленный, Подвесной”, но очевидно, что топикстартер просто не удачный пример привел, и что надо искать товары, у которых значения не пересекаются). Придется выполнять столько выборок, сколько типов товаров найти надо будет.
Если говорить о реализации запроса именно с этой структурой данных, сначала придется сгруппировать эти данные, а потом уже по ним выполнить поиск, а потом еще и подзапрос выполнить. А вот подзапросы на уровне xPDO в принципе только через хаки выполняются.
Вот попробуйте в консоли выполнить этот код:
$q = $modx->newQuery('msProduct'); $alias = $q->getAlias(); $q->select(array( "{$alias}.*", )); $q2 = $modx->newQuery('msProductOption'); $alias2 = $q2->getAlias(); $q2->select(array( "product_id", "group_concat(value) as `values`", )); $q2->groupby('product_id'); $q2->prepare(); $sql2 = $q2->toSQL(); // print "<br />" . $sql2; $q->query['where'][] = new xPDOQueryCondition( array( 'sql' => "EXISTS ( SELECT NULL FROM ({$sql2}) as t WHERE t.product_id = {$alias}.id AND ( (FIND_IN_SET('Административный', `values`) AND FIND_IN_SET('Подвесной', `values`)) OR (FIND_IN_SET('Административный', `values`) AND FIND_IN_SET('Промышленный', `values`) AND FIND_IN_SET('Подвесной', `values`)) ) )" ) ); $s = $q->prepare(); $sql = $q->toSQL(); print "<br />" . $sql . "\n"; // $s = $modx->prepare($sql); $s->execute(); print_r($s->errorInfo()); while($row = $s->fetch(PDO::FETCH_ASSOC)){ print_r($row); }


Этот код как раз выполняет то, вам нужно. На выходе будет получен SQL типа такого:
SELECT msProduct.* FROM `modx_site_content` AS `msProduct` WHERE EXISTS ( SELECT NULL FROM (SELECT `product_id`, group_concat(value) as `values` FROM `modx_ms2_product_options` AS `msProductOption` GROUP BY product_id ) as t WHERE t.product_id = msProduct.id AND ( (FIND_IN_SET('Административный', `values`) AND FIND_IN_SET('Подвесной', `values`)) OR (FIND_IN_SET('Административный', `values`) AND FIND_IN_SET('Промышленный', `values`) AND FIND_IN_SET('Подвесной', `values`)) ) )

И если он найдет нужные товары, то ниже выведет их в результат.
К сожалению, упростить этот запрос с текущей структурой вряд ли получится существенно, и не знаю можно ли такое выполнить через msProducts (только если в него можно передавать объект xPDOQuery или чистый SQL). Вариантов спасения видится два:
1. Использовать TV «выпадающий список множественный выбор». Он в БД складывает значения вида Значение1||Значение2||Значение3… Тогда можно будет передать в вызов условие типа такого:
print '<pre>'; $q = $modx->newQuery('msProduct'); $alias = $q->getAlias(); $q->select(array( "{$alias}.*", )); $tv_id = 1; $q->innerJoin('modTemplateVarResource', 'ps_type', "ps_type.tmplvarid = {$tv_id} AND ps_type.contentid = msProduct.id AND ( (FIND_IN_SET('Значение1', replace('||', ',', ps_type.value)) AND (FIND_IN_SET('Значение2', replace('||', ',', ps_type.value)))) OR (FIND_IN_SET('Значение1', replace('||', ',', ps_type.value)) AND (FIND_IN_SET('Значение2', replace('||', ',', ps_type.value)) AND FIND_IN_SET('Значение3', replace('||', ',', ps_type.value)))) )"); $s = $q->prepare(); $sql = $q->toSQL(); print "<br />" . $sql . "\n"; $s->execute(); print_r($s->errorInfo()); while($row = $s->fetch(PDO::FETCH_ASSOC)){ print_r($row); }
Вот этот вариант уже вполне годится, чтобы передать параметром в вызов msProducts.
2. Менее удобно, но тоже вполне подходящий вариант: делать выборку id-шников подходящих товаров на уровне чистого SQL-запроса (выдернув необходимое из примера выше) и эти id-шники уже передавать в вызов msProducts. Так получится гораздо меньше чистого SQL-ля, который все-таки сложнее в обслуживании, чем запросы на базе xPDO.
UPD: По умолчанию длина строки, формируемой методом GROUP_CONCAT(), ограничена 1024 символами. Из-за этого может резаться результирующее значение. Чтобы увеличить на лету это значение, перед выполняемым запросом делаем так:
$s = $this->modx->prepare('SET SESSION group_concat_max_len = 1000000;'); $s->execute();
Искал по запросам в документации, толком не нашел, или искал криво что ли. Хорошо написал, понятно.
Такое в документации не описано. А FIND_IN_SET() вообще в списке разрешенных методов не числится.

Добавить комментарий