DataTable服务端处理工作原理及Node.js+Express场景下多表搜索实现问题咨询

现有代码的问题修正 #

你的现有实现有几处不符合DataTables服务端处理规范的地方，先调整这些基础问题：

前端问题 #

• 表头定义了3个<th>，但columns只配置了2个字段，会出现列映射报错，调整为对应数量即可；另外全局搜索不需要额外加多余配置，简化后示例：

<table id="exampleTable" class="table table-hover">
<thead>
    <tr>
    <th>Type</th>
    <th>Version</th>
    </tr>
</thead>
</table>
<script>
$(document).ready(function () {
let example = $('#exampleTable').DataTable({
    "paging": true,
    "searching": true,
    "serverSide": true,
    "ajax": {
      "url": "/user/example",
      "type": "GET"
    },
    "columns": [
      {'data': 'DeviceType'},
      {'data': 'Version'}
    ],
    "lengthChange": true
});
});
</script>

后端问题 #

• 缺少对draw参数的正确处理：draw是DataTables发起请求时自带的序号，必须原封不动返回，否则会出现请求时序错乱、数据加载异常的问题，你现有代码里的count未定义，直接取req.query.draw即可。
• recordsFiltered参数赋值错误：该字段是经过搜索过滤后的总数据量，无搜索时才等于recordsTotal，有搜索时需要返回匹配搜索条件的总条数，你现在直接赋值为总数据量会导致搜索后分页条数显示错误。
• 分页查询未传入搜索关键词，所以搜索功能不会生效。

多表关联搜索的实现方案 #

你需要调整数据库查询逻辑，分为两步处理：

1. 新增带搜索条件的总条数查询，用来给recordsFiltered赋值
2. 修改分页查询逻辑，拼接搜索条件，返回匹配的当前页数据

后端代码修改示例 #

router.get('/example', async function(req, res, next) {
  const deviceType = req.session.data.deviceType;
  const pageLength = parseInt(req.query.length) || 10;
  const start = parseInt(req.query.start) || 0;
  const searchValue = req.query.search.value || '';
  const draw = parseInt(req.query.draw) || 1;

  // 无过滤的总数据量
  const totalDeviceCount = await dbHelper.getTotalDeviceNumber(deviceType);
  // 同时查询过滤后的总数据量和当前页数据，传入搜索关键词
  const [filteredCount, deviceList] = await Promise.all([
    dbHelper.getFilteredDeviceCount(deviceType, searchValue),
    dbHelper.getDeviceListWithPaging(pageLength, start, deviceType, searchValue)
  ]);
  
  let data = {
    "draw": draw,
    "recordsTotal": totalDeviceCount,
    "recordsFiltered": filteredCount,
    "data": deviceList
  };
  res.send(data);
});

数据库查询逻辑示例（以原生SQL为例） #

假设你的数据是从devices表和device_versions表关联查询得到，需要搜索DeviceType和Version两个字段，用参数化查询避免SQL注入：

// 查过滤后的总条数
async function getFilteredDeviceCount(deviceType, searchValue) {
  const sql = `
    SELECT COUNT(*) as count 
    FROM devices d 
    LEFT JOIN device_versions dv ON d.version_id = dv.id
    WHERE d.device_type = ? 
    AND (d.device_type LIKE ? OR dv.version_number LIKE ?)
  `;
  const params = [deviceType, `%${searchValue}%`, `%${searchValue}%`];
  const result = await query(sql, params);
  return result[0].count;
}

// 查过滤后的分页数据
async function getDeviceListWithPaging(pageLength, start, deviceType, searchValue) {
  const sql = `
    SELECT d.device_type as DeviceType, dv.version_number as Version
    FROM devices d 
    LEFT JOIN device_versions dv ON d.version_id = dv.id
    WHERE d.device_type = ? 
    AND (d.device_type LIKE ? OR dv.version_number LIKE ?)
    LIMIT ? OFFSET ?
  `;
  const params = [deviceType, `%${searchValue}%`, `%${searchValue}%`, pageLength, start];
  return await query(sql, params);
}

注意事项 #

• 所有涉及用户输入的查询都要用参数化查询，不要直接拼接SQL语句，避免SQL注入风险
• 如果需要支持单独列搜索，可以解析req.query.columns下对应列的搜索参数，添加到WHERE条件中即可
• 记得转换参数类型：start、length、draw都是字符串类型，要转成数字再使用，避免查询报错

内容的提问来源于stack exchange，提问作者aylin