Datatables + PHP：多表格的服务器端处理

Question

如何让Datatables的“服务器端处理”脚本与自定义查询一起使用？我需要从多个表中选择列，并使Datatables呈现它们。Datatables.net的PHP服务器端处理（SSP）概述在这里：https://datatables.net/examples/server_side/simple.html。我发现了这个SO问题，但原帖作者从未提供他的解决方案。我声望不够，无法要求他提供更多细节。这是我的未使用Datatable's SSP的原始SQL。

SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName
FROM tbl_houses, tbl_residents
WHERE tbl_houses.houseID = tbl_residents.residentID

/* 
* # Equivalent query using JOIN suggested by @KumarRakesh
* # Note: JOIN ... ON is a synonym for INNER JOIN ... ON
* # Using JOIN conforms to syntax spec'd by ANSI-92 https://dev59.com/jHNA5IYBdhLWcg3wmfO5#894855
*
* SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName 
* FROM tbl_houses 
* JOIN tbl_residents ON tbl_houses.houseID = tbl_residents.residentID
*/

我该如何使用SSP让Datatables运行以上查询？

似乎server_processing.php仅接受一个表并且不支持自定义过滤（例如，WHERE子句）。

// DB table to use
$table = 'datatables_demo';

// Table's primary key
$primaryKey = 'id';

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( 'ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

然而，ssp.class.php 支持使用WHERE过滤。我认为我需要修改ssp.class.php，以强制使用我的WHERE子句。

更新

找到了解决方案。有空时会发布。

- Christian

---

你在这种情况下使用了联接查询吗？ - Kumar Rakesh

您也可以使用以下代码实现...SELECT tbl_house.style，tbl_house.roomCount，tbl_residents.firstName，tbl_residents.lastName FROM tbl_house JOIN tbl_resident ON tbl_house.houseID = tbl_resident.residentID。 - Kumar Rakesh

@KumarRakesh 我正在尝试弄清楚如何将自定义SQL查询嵌入Datatables服务器端处理（SSP）脚本。我的问题不是如何构建SQL查询，而是如何在服务器端将其纳入Datatables的SSP“框架”。 - Christian

我认为我需要硬编码我的自定义过滤器（即WHERE子句）到“sss.class.php”文件中。如果成功，我会尝试并更新。 - Christian

我开发了一个基于PDO适配器的服务器端数据表类，自从6年前以来，我在几乎所有项目中都使用它，一直没有出现任何问题。在看到你的问题后，我将其托管在Github上，如果有帮助的话，这是链接 https://github.com/oromedialab/datatables ，如有任何问题，请随时提问，我会尽力回答，它支持任何连接、分组等操作。 - Ibrahim Azhar Armar

@IbrahimAzharArmar 感谢您抽出时间发布自定义内容！但是，我担心它依赖于 Datatables 版本在1.10+之前。从1.x到1.10版的API有很大的变化。 - Christian

Answer 1

解决方案

ssp.class.php类不支持联接和子查询，但有一个解决方法。诀窍是在$table定义中使用子查询，如下所示。将table替换为您实际的表名。

$table = <<<EOT
 (
    SELECT 
      a.id, 
      a.name, 
      a.father_id, 
      b.name AS father_name
    FROM table a
    LEFT JOIN table b ON a.father_id = b.id
 ) temp
EOT;

$primaryKey = 'id';

$columns = array(
   array( 'db' => 'id',          'dt' => 0 ),
   array( 'db' => 'name',        'dt' => 1 ),
   array( 'db' => 'father_id',   'dt' => 2 ),
   array( 'db' => 'father_name', '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以删除反引号。

确保所有列名都是唯一的，否则使用AS指定别名。

注意事项

还有一个github.com/emran/ssp仓库，其中包含支持JOIN的增强版ssp.class.php。

链接

请查看jQuery DataTables：使用WHERE、JOIN和GROUP BY与ssp.class.php获取更多信息。

- Gyrocode.com

Answer 2

简而言之： 我最后使用了一个原始Datatables ssp.class.php的修改版本，该版本称为由Emran Ul Hadi实现的ssp.php： https://github.com/emran/ssp

他的修改接受JOIN，WHERE，GROUP BY和列别名。尽管这个文件已经一年多没有更新了，但它仍然适用于DataTables 1.12.x。我对他的版本进行了一些修改，增强了其稳健性并改进了文档，提供了更清晰的示例。

等我有更多时间时会在这里发布我的修改/更新。最终，我希望提交请求以将我的更新合并到他的存储库中。

- Christian

---

你尝试过修改它以支持“UNION ALL”吗？ - Milson

@Milson 一直想将草稿放到 Github 上，并向 Emran 发起 pull-request。我的修改主要是简化调用 ssp 函数/过程的语法和文档说明的清晰度。添加 UNION 功能应该很容易。查看 Emran 进行的打开 JOIN 的更改可以了解如何实现其他 SQL 函数的上下文信息。 - Christian

Answer 3

看起来 DataTables 的脚本确实不适用于您的特定用例。但是有一种方法可以允许自定义 where 子句，从阅读 ssp.class.php#complex 的源代码来看，我认为通过使用 WHERE 方法，这种配置应该适用于您。在这里，JOIN 方法将无法工作。

长话短说：将您的 server_processing.php 编辑为以下内容：

<?php
// DB table to use
$table = 'tbl_houses, tbl_residents';

// First table's primary key
$primaryKey = 'tbl_houses.id';

$columns = [
    [ 'db' => 'tbl_houses.style'],
    [ 'db' => 'bl_houses.roomCount'],
    [ 'db' => 'tbl_residents.firstName'],
    [ 'db' => 'tbl_residents.lastName']

);

// connection details
$sql_details = [

];

$whereAll = 'tbl_houses.houseID = tbl_residents.residentID';

require( 'ssp.class.php' );

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns , null, $whereAll);
);

complex 方法接受自定义的 WHERE 子句。但棘手的是使用了2个表格。脚本似乎没有为此设计。我查看了它如何构建最终的 SQL 查询，发现您可以在配置中使用 table_name.field_name 表示法，以及 $table 和 $primaryKey 变量的 table_name, table_name 表示法。

如前所述，DataTables 脚本不支持使用2个表格。我不知道 DataTables 的所有功能是否都能与之正常工作。

- Jojo

---

感谢您的深入探讨！我已经实施了您的建议，您是正确的！Datatables ssp.class.php默认情况下不支持多个表格。也就是说，$table = 'tbl_houses, tbl_residents'会产生一个“基本表或视图未找到：1146”错误。但是，我已经找到了解决方案，并将在有空闲时间时发布。 - Christian

Answer 4

我通过在mysql phpmyadmin中创建视图表来解决了这个问题。

在phpmyadmin中运行您的SQL脚本，然后单击创建视图

输入图像描述

- Ahmed Fahmy

Answer 5

很抱歉晚了，但我通过构建自己的服务器端文件解决了问题，这样我就可以定制查询和JSON输出。

文件服务器端自定义：

 public function produtosEstoque($arr)
    {
              
        //COLUNAS
        $column = array('prodNome', 'prodPreco', 'prodQtdEst', 'categDescricao');


        //SQL
        $query2 = "SELECT a.prodID, a.prodNome, a.prodMedida, a.prodPrecoAnt, a.prodPreco, a.prodCategoriaID, a.prodOferta,a.prodQtdEst, a.prodQtdMEst, b.categDescricao FROM produtos a INNER JOIN categoria b ON a.prodCategoriaID = b.categID WHERE a.prodEstatusID = 6 ";

        //SEARCH
        if ($arr['search']['value']) {
            $query2 .= "AND prodNome LIKE '%".$arr['search']['value']."%' ";
        }

        //ORDER
        if (isset($arr['order'])) {
            $query2 .= 'ORDER BY ' . $column[$arr['order']['0']['column']] . ' ' . $arr['order']['0']['dir'] . ' ';
        } else {
            $query2 .= 'ORDER BY a.prodID DESC ';
        }

        
        //LIMIT
        if ($arr["length"] != -1) {
            $query3 = 'LIMIT ' . $arr['start'] . ', ' . $arr['length'];
        }
        
        
        try {
            
            //TOTAL DE REGISTROS NA TABELA
            $query1 = "SELECT * FROM produtos WHERE prodEstatusID = 6";
            $stm1 = $this->pdo->prepare($query1);
            $stm1->execute();
            $contReg = $stm1->rowCount($stm1);   
            
            
            $stm = $this->pdo->prepare($query2);
            $stm->execute();
            $number_filter_row = $stm->rowCount($stm);


            $stm = $this->pdo->prepare($query2 . $query3);
            $stm->execute();
            $list = $stm->fetchAll(PDO::FETCH_OBJ);


            $data = [];
            foreach ($list as $row) {                                  
                $data[] = array('prod_nome' => $row->prodNome . ", " . $row->prodMedida, 'prod_preco' => "R$ ".$this->convInReal($row->prodPreco)." ".$this->formatProdOff($row->prodOferta), 'prod_estoque' => $row->prodQtdEst . " - M: " . $row->prodQtdMEst, 'prod_categoria' => $row->categDescricao);                                
            }

            $dat = array('draw' => intval($arr["draw"]), 
                  'recordsTotal' => $contReg, 
                  'recordsFiltered' => $number_filter_row, 
                  'data' => $data
                  );
            
            return json_encode($dat, JSON_UNESCAPED_UNICODE);

        } catch (PDOException $erro) {
            $data = array('msgEr' => 'ERR_002_EI', 'erroLine' => $erro->getLine());
            return json_encode($data);
        }
    }

- José Damião

Answer 6

我对原Datatables ssp.class.php的分支：https://github.com/emayskiy/Datatables-SSP-MultiTables

您可以使用表和列别名，使用where子句，使用[LEFT, INNER, RIGHT, CROSS] JOIN与表连接。 例如server_processing.php：

<?php 
    //For one table:
    //$table = "main_table_name";
    //Or for multitable queries: 
    $table = array(
        array('table'=>'main_table_name',  'as'=>'mt'),    
        array('table'=>'join1_table_name', 'as'=>'jt1', 'join_type'=>'INNER', 'join_on'=>'mt.field  = jt1.field'),
        array('table'=>'join2_table_name', 'as'=>'jt2', 'join_type'=>'LEFT',  'join_on'=>'mt.field1 = jt2.field')
    ); 
    //Columns definition with alias
    $columns = array(
        array( 'db' => 'mt.field1',  'dt' => 0 ),
        array( 'db' => 'mt.field2',  'dt' => 1 ),    
        array( 'db' => 'mt.name',    'as'=>'field3', 'dt' => 2 ),    
        array( 'db' => 'jt1.field1', 'as'=>'field4', 'dt' => 3 ),
        array( 'db' => 'jt2.field5', 'as'=>'field5', 'dt' => 4 )       
    );
    $primaryKey = 'mt.id'; //Primary key, for check records count
    $sql_details = array(
        'user' => $db_user,
        'pass' => $db_password,
        'db'   => $db_name,
        'host' => $db_host
    );
    $where = "mt.field1 > 10"; //You SQL where condition

    require('ssp.class.php' ); //File from github.com/emayskiy/Datatables-SSP-MultiTables
    echo json_encode(
        // All params same as in original class SSP
        SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns , '', $where)
    );

?>

- emayskiy