Автоматическое составление сложных запросов в mapper`ах
Этот пост — совет/подсказка разработчикам, которые трудятся над модулями во имя развития проекта :)
Суть проблемы.
Работая над модулем «Объявления» я столкнулся с необходимостью сделать достаточно сложный запрос к базе данных. Сложный не в плане SQL, а в том, что мы заранее не знаем каким он получиться. Такая ситуация возникла при создании пользовательского фильтра.
Технически задача заключается в следующем — есть три таблицы: объявления, категории объявлений — отсюда мы получаем название категории, пользователи — отсюда мы «дергаем» логин пользователя. Необходимо реализовать выборку по следующим условиям:
— категория или массив категорий (дочерние),
— ID пользователя или логин,
— статус (опубликовано или нет — для администратора),
— актуально объявление или нет,
— минимальная и/или максимальная цена,
— есть привилегии или нет,
Учесть при этом составную сортировку (по одному или двум полям), а также не забывать, что результат фильтрации нужно показывать с разбивкой на страницы. Сложности в это все добавляет то, что логин пользователя тянется из другой таблицы, поэтому необходимо использовать HAVING, а не WHERE.
Решение проблемы.
В аналогичных ситуациях в движке используется метод buildFilter(), который «строит» условие WHERE. Пример можно глянуть в Topic.mapper.class.php. Такое решение меня немного не устраивает — мне нужно собирать по кускам не только WHERE, но и HAVING, ORDER, LIMIT.
Поэтому для решения своей задачи я воспользовался механизмом макроподстановок библиотеки DbSimple. Как это работает? При написании SQL запроса вы берете условие в {}-скобки:
А при подставке данных указываете альтернативу:
Если $aFilter['categoryId'] окажется не пустым, то в запрос попадет:
А если пустым, то константа DBSIMPLE_SKIP укажет на то, что условие в скобках должно быть проигнорировано. Останется просто:
На мой взгляд, очень удобная система с понятным и лаконичным синтаксисом. Как пример, окончательный вариант моей функции:
Надеюсь, кому то такой этот совет поможет в разработке.
Суть проблемы.
Работая над модулем «Объявления» я столкнулся с необходимостью сделать достаточно сложный запрос к базе данных. Сложный не в плане SQL, а в том, что мы заранее не знаем каким он получиться. Такая ситуация возникла при создании пользовательского фильтра.
Технически задача заключается в следующем — есть три таблицы: объявления, категории объявлений — отсюда мы получаем название категории, пользователи — отсюда мы «дергаем» логин пользователя. Необходимо реализовать выборку по следующим условиям:
— категория или массив категорий (дочерние),
— ID пользователя или логин,
— статус (опубликовано или нет — для администратора),
— актуально объявление или нет,
— минимальная и/или максимальная цена,
— есть привилегии или нет,
Учесть при этом составную сортировку (по одному или двум полям), а также не забывать, что результат фильтрации нужно показывать с разбивкой на страницы. Сложности в это все добавляет то, что логин пользователя тянется из другой таблицы, поэтому необходимо использовать HAVING, а не WHERE.
Решение проблемы.
В аналогичных ситуациях в движке используется метод buildFilter(), который «строит» условие WHERE. Пример можно глянуть в Topic.mapper.class.php. Такое решение меня немного не устраивает — мне нужно собирать по кускам не только WHERE, но и HAVING, ORDER, LIMIT.
Поэтому для решения своей задачи я воспользовался механизмом макроподстановок библиотеки DbSimple. Как это работает? При написании SQL запроса вы берете условие в {}-скобки:
WHERE
1 = 1
{ AND t.category_id IN (?a) }
А при подставке данных указываете альтернативу:
(!empty($aFilter['categoryId']) ? (array)$aFilter['categoryId'] : DBSIMPLE_SKIP)
Если $aFilter['categoryId'] окажется не пустым, то в запрос попадет:
WHERE 1 = 1 AND t.category_id IN (1,2,...тут ваш массив)
А если пустым, то константа DBSIMPLE_SKIP укажет на то, что условие в скобках должно быть проигнорировано. Останется просто:
WHERE 1=1
На мой взгляд, очень удобная система с понятным и лаконичным синтаксисом. Как пример, окончательный вариант моей функции:
/**
* Строит запрос по массиву переданных параметров
* $aFilter = array(
* 'categoryId' => int|array
* 'status' => string|array
* 'isActual' => bool
* 'privilage' => int|array
* 'min_price' => float
* 'max_price' => float
* 'order' => string
* 'userId' => int|array
* 'user_login' => string
* );
*
* @param array $aFilter
* @param int $iCurrPage
* @param int $iPerPage
* @return array
*/
public function GetTickets($aFilter,$iCurrPage,$iPerPage) {
$sql = "SELECT
t.*,
u.user_login as user_login,
c.name as category_name,
(t.opendate+t.timelife > NOW()) as isActual
FROM
".DB_TABLE_TICKET." as t
LEFT JOIN
".DB_TABLE_USER." as u ON t.user_id = u.user_id
LEFT JOIN
".DB_TABLE_CATEGORY." as c ON c.category_id = t.category_id
WHERE
1 = 1
{ AND t.category_id IN (?a) }
{ AND t.status IN (?a) }
{ AND t.user_id = ?d }
{ AND t.opendate+t.timelife > NOW() AND 1=?d }
{ AND t.price > ?d }
{ AND t.price < ?d }
{ AND t.privilage IN (?a) }
{ AND ((LOWER(t.title) REGEXP ?) OR (LOWER(t.text) REGEXP ?)) }
HAVING
1 = 1
{ AND u.user_login = ? }
ORDER BY
t.privilage DESC,
t.". $aFilter['sort'] ." ". $aFilter['order'] ."
LIMIT
?d, ?d
";
$aReturn=array();
if ($aRows=$this->oDb->select(
$sql,
(!empty($aFilter['categoryId']) ? (array)$aFilter['categoryId'] : DBSIMPLE_SKIP),
(!empty($aFilter['status']) ? (array)$aFilter['status'] : DBSIMPLE_SKIP),
(!empty($aFilter['user_id']) ? $aFilter['userId'] : DBSIMPLE_SKIP),
(!empty($aFilter['isActual']) ? 1 : DBSIMPLE_SKIP),
(!empty($aFilter['min_price']) ? $aFilter['min_price'] : DBSIMPLE_SKIP),
(!empty($aFilter['max_price']) ? $aFilter['max_price'] : DBSIMPLE_SKIP),
(!empty($aFilter['privilage']) ? $aFilter['privilage'] : DBSIMPLE_SKIP),
(!empty($aFilter['keywords']) ? $aFilter['keywords'] : DBSIMPLE_SKIP),
(!empty($aFilter['keywords']) ? $aFilter['keywords'] : DBSIMPLE_SKIP),
(!empty($aFilter['user_login']) ? $aFilter['user_login'] : DBSIMPLE_SKIP),
($iCurrPage-1)*$iPerPage,
$iPerPage
))
{
foreach ($aRows as $aRow) {
$aReturn[]=new TicketEntity_Ticket($aRow);
}
}
return $aReturn;
}
Надеюсь, кому то такой этот совет поможет в разработке.
6 комментариев
Вы шо?
на счет ЛС, там эта конструкция используется дважды, поэтому чтоб не дублировать был создан метод buildFilter