Веб-разработчики довольно часто сталкиваются с задачей отображения на странице данных в табличной форме с возможностью их сортировки, поиска/фильтрации, разбиения на страницы и удобной навигации по ним. Например, для меня это стало актуально при создании админки для одного сайта. Каждый разработчик решает эту задачу по-своему. Многие не сильно с этим заморачиваются и в результате получаются интерфейсы в стиле ранних версий phpMyAdmin, с полным обновлением страницы после любого клика мышью. Но сейчас уже не начало 2000-х, а значит, пора уже и админки переводить на AJAX и jQuery. Мне в этом очень помог замечательный плагин DataTables, который избавил меня от необходимости изобретать велосипед и писать тонны кода. С его помощью я смог в сжатые сроки придать админке современный вид. Теперь я хочу поделиться некоторым опытом, накопленным за время использования этого плагина.
В этой статье я расскажу как с помощью DataTables и его дополнения Column Filter организовать постраничный вывод больших объемов данных с поддержкой индивидуальной фильтрации по столбцам. В этом случае необходимо задействовать в плагине режим обработки данных на стороне сервера. На сайте плагина есть подробный пример реализации этого режима. Здесь же я рассмотрю практические аспекты применения дополнения Column Filter и особенности реализации обработки данных на стороне сервера в этом случае.
Для тех, кому не терпится посмотреть, как это все работает, вот ссылка на работающий пример, где выводится таблица пользователей гипотетического хабраподобного сайта:). Там же можно найти и исходники примера.
В отличие от самого плагина его дополнение Column Filter к сожалению не может похвастать подробной документацией. Множество его возможностей и особенностей почему-то нигде не описаны. Так, в примере настройки Column Filter не указана полная форма инициализации списка (ее можно найти только в исходниках дополнения), вместо нее используется краткая форма:
values: [ 'A', 'B', 'C' ]которая актуальна только для списков с текстовыми значениями опций, совпадающими с их метками. Это может и годится для простейших статических таблиц, но не подходит для использования с базами данных, где, как правило, значения таких столбцов хранятся отдельно (в другой таблице) и связываются с основной таблицей с помощью ключей. В этом случае следует использовать полную форму инициализации списка:
values: [ {value:1, label:"Текст 1"}, {value:2, label:"Текст 2"}, ... ]Кроме того, на сайте дополнения почему-то не указано, что в режиме обработки данных на стороне сервера ColumnFilter добавляет в Ajax-запросы к серверу свои дополнительные параметры, которые необходимо учитывать и обрабатывать соответствующим образом в скрипте обработки, иначе индивидуальная фильтрация не будет работать.
На основе примера с сайта DataTables я написал PHP-класс, реализующий обработку данных на стороне сервера, в котором содержатся изменения, необходимые для нормальной работы дополнения Column Filter.
Вот его исходный код:
<?php
abstract class DataTableBase
{
/*
* Вывод данных из таблицы в формате JSON:
* Параметры:
* $sTable [string] - имя таблицы (используется в запросе SELECT)
* $aCols [array of string] - массив столбцов для сортировки и фильтрации
* (optional:)
* $sIndexCol [string] - индексируемый столбец
* $aColFltFlags [array of bool] - массив флажков, определяющих,
* подлежит ли столбец общей фильтрации
* $sFetchCols [string] - список выбираемых столбцов
* $sTableXtra [string] - дополнение для блока FROM (для INNER JOIN)
* $sPreCond [string] - необходимое условие (используется в блоке WHERE)
* Возвращает:
* [array] - данные в формате JSON
* false - в случае ошибки
*/
function output($sTable, $aCols,
$sIndexCol='', $aColFltFlags=0, $sFetchCols='', $sTableXtra='', $sPreCond='')
{
// Проверка параметров:
if (!$sTable || !is_array($aCols) || !($n= count($aCols))) return '';
for ($i= 0; $i< $n && !$aCols[$i]; $i++) ;
if ($i>= $n) return '';
if (!$sIndexCol) {
$sIndexCol= $aCols[$i];
}
if (!is_array($aColFltFlags) || count($aCols) != $n) {
$aColFltFlags= array_fill(0, $n, false);
}
if (!$sFetchCols) {
$sFetchCols= '*';
}
/* Разбиение на страницы */
$sLimit = "";
if (isset($_REQUEST['iDisplayStart']) &&
isset($_REQUEST['iDisplayLength']) &&
($n= $_REQUEST['iDisplayLength']) != '-1') {
$sLimit = 'LIMIT '. $this->sqlEsc($_REQUEST['iDisplayStart']). ', '.
$this->sqlEsc($n);
}
/* блок ORDER */
$sOrder= '';
if (isset($_REQUEST['iSortCol_0']) && isset($_REQUEST['iSortingCols'])) {
for ($n= intval($_REQUEST['iSortingCols']), $i=0 ; $i < $n; $i++) {
if (isset($_REQUEST['iSortCol_'.$i]) &&
($s= $_REQUEST['iSortCol_'.$i])!='' &&
isset($_REQUEST['bSortable_'.($i_col= intval($s))]) &&
$_REQUEST['bSortable_'.$i_col] == 'true' &&
isset($aCols[$i_col])) {
$sOrder.= ", ". $aCols[$i_col]. " ".
((strtolower($_REQUEST['sSortDir_'.$i])=='desc')? 'DESC' : 'ASC');
}
}
if ($sOrder) $sOrder= 'ORDER BY '. substr($sOrder, 2);
}
/* Фильтрация столбцов */
$sRangeSep= '';
if (isset($_REQUEST['sRangeSeparator']) &&
strlen($s= $this->sqlEsc($_REQUEST['sRangeSeparator'])) == 1 &&
preg_match('/^[^^-/]$/', $s)) {
$sRangeSep= $s;
$val= '((?:d+|d{2,4}.d{2}.d{2}|))';
$sRangeRegex= '/^'.$val.'['.$sRangeSep.']'.$val.'$/';
}
/* Общая фильтрация */
$sWhere = $sPreCond? "WHERE $sPreCond": '';
if (isset($_REQUEST['sSearch']) && ($find= $_REQUEST['sSearch']) != '') {
$conds_or= '';
foreach ($aCols as $i=> $col) {
if ($col && $aColFltFlags[$i])
$conds_or.= " OR ". $col. " LIKE '%".$this->sqlEsc($find)."%'";
}
if ($conds_or)
$sWhere.= ($sWhere? " AND " : "WHERE "). '('. substr($conds_or, 4). ')';
}
/* Индивидуальная фильтрация */
$conds_and= '';
foreach ($aCols as $i=> $col) {
if ($col && isset($_REQUEST['bSearchable_'.$i]) &&
$_REQUEST['bSearchable_'.$i] == "true" &&
isset($_REQUEST['sSearch_'.$i]) &&
($find= $_REQUEST['sSearch_'.$i]) != '' && $find != $sRangeSep) {
$conds_and.= " AND ";
if (is_numeric($find))
$conds_and.= "$col = '$find'";
else if (preg_match('/^[d,]+$/', $find)) {
$arr= array();
foreach (explode(',', $find) as $v)
$arr[]= "$col = '$v'";
$conds_and.= '('. implode(" OR ", $arr). ')';
}
else if ($sRangeSep && preg_match($sRangeRegex, $find, $arr)) {
list(, $v1, $v2)= $arr;
$conds_and.=
(($v1!= '')? ("$col >= '$v1'") : '').
(($v2!= '')? ((($v1!= '')? ' AND ': ''). "$col <= '$v2'") : '');
}
else
$conds_and.= "$col LIKE '%".$this->sqlEsc($find)."%' ";
}
}
if ($conds_and)
$sWhere.= ($sWhere? " AND " : "WHERE "). substr($conds_and, 4);
/* SQL запросы */
/* Подсчет общего кол-ва строк в таблице */
$nTotal = $this->sqlCount("
SELECT COUNT($sIndexCol) FROM $sTable
");
/* Выборка данных для вывода */
$aData= $this->sqlData("
SELECT SQL_CALC_FOUND_ROWS
$sFetchCols
FROM $sTable$sTableXtra
$sWhere
$sOrder
$sLimit
");
/* Подсчет кол-ва отфильтрованных строк в таблице */
$nFiltered = $this->sqlCount("
SELECT FOUND_ROWS()
");
/* Выходной массив */
$aOutput = array(
"sEcho" => intval($_REQUEST['sEcho']),
"iTotalRecords" => $nTotal,
"iTotalDisplayRecords" => $nFiltered,
"aaData" => is_array($aData)? $this->buildData($aData): array()
);
return json_encode($aOutput);
}
/*
* Подготовка списка для вывода:
* Параметры:
* $aMap [array] - массив значений списка
* Возвращает:
* [array] - подготовленный список
* false - в случае ошибки
*/
static
function buildDataMap($aMap)
{
if (!is_array($aMap)) return false;
$arr= array();
foreach ($aMap as $id=> $title) {
$arr[]= array('value'=> $id, 'label'=> $title);
}
return $arr;
}
/*
* Вывод списка в формате JSON:
* Параметры:
* $sTable [string] - имя таблицы
* $sIdCol [string] - индексируемый столбец
* $sTitleCol [string] - текстовый столбец
* $sCond [string] - условие фильтрации
* Возвращает:
* [string] - список в формате JSON
* false - в случае ошибки
*/
function outputDataMap($sTable, $sIdCol, $sTitleCol, $sCond='')
{
return json_encode(self::buildDataMap($this->sqlDataMap(
$sTable, $sIdCol, $sTitleCol, $sCond
)));
}
/*
* ПЕРЕОПРЕДЕЛЯЕМЫЕ МЕТОДЫ:
*/
function sqlEsc($s)
{
return mysql_real_escape_string($s);
}
/*
* Подсчет кол-ва строк в таблице (SQL-запросом):
* Параметры:
* $sQuery [string] - строка запроса
* Возвращает:
* [number] - кол-во строк
* false - в случае ошибки
*/
protected
function sqlCount($sQuery)
{
if (!($h_res= mysql_query($sQuery))) return false;
return is_array($a_row= mysql_fetch_row($h_res))? $a_row[0]: false;
}
/*
* Выборка данных из таблицы (SQL-запросом):
* Параметры:
* $sQuery [string] - строка запроса
* Возвращает:
* [array] - массив данных
* false - в случае ошибки
*/
protected
function sqlData($sQuery)
{
if (!($h_res= mysql_query($sQuery)) || !mysql_num_rows($h_res)) return false;
$a_res= array();
while ($a_row= mysql_fetch_assoc($h_res)) {
if (count($a_row)) $a_res[]= $a_row;
}
mysql_free_result($h_res);
return count($a_res)? $a_res: false;
}
/*
* Выборка значений списка из БД (SQL-запросом):
* Параметры:
* $sTable [string] - имя таблицы
* $sIdCol [string] - индексируемый столбец
* $sTitleCol [string] - текстовый столбец
* $sCond [string] - условие фильтрации
* Возвращает:
* [array] - значения списка
* false - в случае ошибки
*/
protected
function sqlDataMap($sTable, $sIdCol, $sTitleCol, $sCond='')
{
$s_qry= "SELECT $sIdCol, $sTitleCol FROM $sTable".
($sCond? " WHERE $sCond": '');
if (!($h_res= mysql_query($s_qry)) || !mysql_num_rows($h_res)) return false;
$a_res= array();
while ($a_row= mysql_fetch_row($h_res)) {
if (count($a_row) == 2) $a_res[$a_row[0]]= $a_row[1];
}
mysql_free_result($h_res);
return count($a_res)? $a_res : false;
}
/*
* Подготовка данных для вывода (этот метод должен быть определен в наследнике):
* Параметры:
* $aData [array] - данные выбранные из БД (методом 'sqlData')
* Возвращает:
* [array] - подготовленные данные
*/
abstract protected
function buildData($aData);
}
?>
Данный класс поддерживает все типы фильтров для столбцов, используемые в Column Filter:
- обычный текст;
- числа и диапазоны чисел;
- даты и диапазоны дат;
- списки.
Следует отметить, что диапазоны дат в Column Filter реализуются с помощью Jquery UI, поэтому для их нормальной работы необходимо подключение этого плагина, а также соответствующих стилей и ресурсов. В основном по этой причине (мне не очень нравится этот громоздкий плагин) я и не стал использовать в моем примере фильтр с диапазоном дат, хотя в принципе это возможно – рассмотренный класс поддерживает обработку диапазонов дат.
На этом первая часть статьи заканчивается. Спасибо за внимание.
Во второй части я расскажу о том, как с помощью тех же самых инструментов (плюс немного шаманства) построить иерархию зависимых списков фильтрации и добавить к спискам возможность выбора нескольких значений.
P.S.: На странице примера установлено ограничение на количество одновременных Ajax-запросов к серверу. К сожалению мне пришлось это сделать, чтобы избежать возможных проблем с хабраэффектом (по этой же причине было сокращено количество данных). Если вы испытываете проблемы с отображением таблицы, просто обновите страницу.
Автор: xmeoff
