Как добиться такого сложного поиска с помощью плагина jQuery datatable?

Question

Как добиться такого сложного поиска с помощью плагина jQuery datatable?

Я пытаюсь создать интерфейс с возможностью поиска, и мой сервер включает в себя три таблицы базы данных MySQL:

Tbl_ Страна

mysql> select * from tbl_country;
+--------------+---------------+
| country_code | country       |
+--------------+---------------+
| AFG          | AFGHANISTAN   |
| AUS          | AUSTRALIA     |
| CAN          | CANADA        |
| GBR          | GREAT BRITAIN |
| IND          | INDIA         |
| USA          | USA           |
+--------------+---------------+
6 rows in set (0.00 sec)

Tbl_стат

mysql> select * from tbl_state;
+----------+------------------+--------------+
| state_id | state            | country_code |
+----------+------------------+--------------+
|        1 | Maharashtra      | IND          |
|        2 | Delhi            | IND          |
|        3 | West Bengali     | IND          |
............
|       51 | Queensland       | AUS          |
+----------+------------------+--------------+
33 rows in set (0.00 sec)

Tbl_сити

mysql> select * from tbl_city;
+---------+----------+--------------------+
| city_id | state_id | city               |
+---------+----------+--------------------+
|       1 |        1 | Mumbai (Bombay)    |
|       2 |        1 | Nagpur             |
.......
|      84 |       37 | Edinburgh          |
|     122 |       44 | Cardiff            |
|     127 |       46 | Melbourne          |
|     130 |       48 | Perth              |
|     131 |       49 | Adelaide           |
|     132 |       50 | Canberra           |
|     133 |       51 | Brisbane           |
|     134 |       51 | Gold Coast         |
+---------+----------+--------------------+
54 rows in set (0.00 sec)

У меня есть схема базы данных MySQL и значения, как показано здесь в Скрипка SQL

Мой поиск должен получить единый интерфейс, с помощью которого я могу выполнять поиск по всей стране на основе любых данных о городе или штате.

Я пытаюсь использовать этот запрос который правильно выдает результат, который я хочу получить:

SELECT 
-- tbl_country
c.country_code, c.country,

-- tbl_state
CAST(GROUP_CONCAT(s.state SEPARATOR ',') AS CHAR) as state,
-- tbl_city
CAST(GROUP_CONCAT(ct.city SEPARATOR ',') AS CHAR) as city

-- tbl_papers
FROM tbl_country c
LEFT JOIN tbl_state s ON s.country_code = c.country_code
LEFT JOIN tbl_city ct ON ct.state_id = s.state_id

GROUP BY (c.country_code);

Я использую плагин Datatable, который является довольно мощным и простым в использовании, но всякий раз, когда я пытался использовать фильтр поиска, он теперь будет работать, поскольку серверный PHP-класс datatable пытается создать динамический запрос с условием where на основе столбцов привязки.

Запрос связывает данные, как показано здесь в первой строке, так как поиск не выполняется :

Динамический запрос выглядит так это:

SELECT    sql_calc_found_rows `c`.`country_code`,
          `c`.`country`,
          group_concat(`s`.`state` separator "," ) AS state,
          group_concat(`ct`.`city` separator ",")  AS city
FROM      `tbl_country`                            AS `c`
LEFT JOIN `tbl_state`                              AS `s`
ON        (
                    `s`.`country_code` = `c`.`country_code`)
LEFT JOIN `tbl_city` AS`ct`
ON        `ct`.`state_id` = `s`.`state_id`
WHERE     (
                    `c`.`country_code` LIKE :binding_0
          OR        `c`.`country` LIKE :binding_1
          OR        group_concat(`s`.`state` separator ",") LIKE :binding_2
          OR        group_concat(`ct`.`city` separator ",") LIKE :binding_3)
GROUP BY  `c`.`country_code`
ORDER BY  `c`.`country_code` ASC limit 0,
          10

, Что, очевидно, приводит к ошибке, как показано здесь:

В любом случае, я могу фильтровать/искать на основе данных о городе, штате и стране по вышеупомянутому сценарию, используя PHP и плагин datatable. Может быть, мне нужно изменить структуру запроса!

4

php mysql datatables

Author: Gyrocode.com, 2017-01-01

Source

1 answers

score 3 · Accepted Answer

Я предполагаю, что вы используете ssp.class.php для обработки ваших данных на стороне сервера.

Класс ssp.class.php не поддерживает сложные запросы, содержащие соединения и подзапросы, но есть обходной путь. Хитрость заключается в использовании подзапроса, как показано ниже в определении $table.

$table = <<<EOT
 (
    SELECT 
    c.country_code, 
    c.country,
    CAST(GROUP_CONCAT(s.state SEPARATOR ',') AS CHAR) as state,
    CAST(GROUP_CONCAT(ct.city SEPARATOR ',') AS CHAR) as city
    FROM tbl_country c
    LEFT JOIN tbl_state s ON s.country_code = c.country_code
    LEFT JOIN tbl_city ct ON ct.state_id = s.state_id
    GROUP BY (c.country_code)
 ) temp
EOT;

$primaryKey = 'id';

$columns = array(
   array( 'db' => 'country_code', 'dt' => 0 ),
   array( 'db' => 'country', 'dt' => 1 ),
   array( 'db' => 'state', 'dt' => 2 ),
   array( 'db' => 'city', 'dt' => 3 )
);

$sql_details = array(
   'user' => '',
   'pass' => '',
   'db'   => '',
   'host' => ''
);

require( 'ssp.class.php' );
echo json_encode(
   SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

Вам также необходимо отредактировать ssp.class.php и заменить все экземпляры FROM `$table` на FROM $table, чтобы удалить обратные ссылки.

Существует также github.com/emran/ssp репозиторий, содержащий расширенную ssp.class.php поддержку Присоединяется.

ССЫЛКИ

См. Таблицы данных jQuery: Используя WHERE, ПРИСОЕДИНЯЙТЕСЬ и ГРУППИРУЙТЕ с помощью ssp.class.php для получения дополнительной информации.