dataTable 服务端分页请求

来源：互联网发布：mac 开机文件夹问号编辑：程序博客网时间：2024/06/17 08:01

使用datatables之前一直是数据源形式，一次性请求完数据然后进行渲染，但是由于本次项目数据较多首次加载体验效果不好，故领导让改成服务端模式请求。

框架：thinphp3.2

前端部分：

renderTable: function() {
var _this = this;
_this.data.table = $('#data_table_secondBox').DataTable({
language: Base.oLanguage,
lengthChange: false,
searching: false,
ordering: false,
processing: true,
serverSide: true,
ajax: {
"url":"{:U('Sale/reflux')}",
type: 'post',

// 定义条件数据
data: {
'do': 'selData',
'channel':_this.data.channel,
'media':_this.data.media,
'day':_this.data.day,
'ad_place':_this.data.ad_place,
'activity_name':_this.data.activity_name,
'initial_status':_this.data.initial_status,// 客户属性
'current_status':_this.data.current_status,// 卡级别
'action_type' :_this.data.action_type,// 行为
'keywords': _this.data.keywords,
'page': _this.data.page
},
},
//data: _this.data.tagList,
destroy: true,
columns: [
{data: null},
{data: "channel"},
{data: 'activity_name'},
{data: "initial_status"},
{data: "current_status"},
{data: "phone"},
{data: "mem_num"},
{data: "action_type"},
{data: "money "},
{data: "duration"},
//{data: "action_time"},
],

// 自定义每一列的数据格式
columnDefs: [
{
targets: 0,
render: function(data, type, row, meta) {
return '<p style="width:50px">' + (meta.row + 1) + '</p>';
}
},
{
targets: 1,
render: function(data, type, row, meta) {
var str = '<input type="hidden" class="data-channel" value="' + row["channel"] + '">';
str += '<input type="hidden" class="data-media" value="' + row['media'] + '">';
str += '<input type="hidden" class="data-place" value="' + row['ad_place'] + '">';
str += '<input type="hidden" class="data-activity_name" value="' + row['activity_name'] + '">';
str += '<input type="hidden" class="data-day" value="' + row['activity_day'] + '">';
str += '<input type="hidden" class="data-action_type" value="' + row['action_type'] + '">';
str += '<input type="hidden" class="data-mem_num" value="' + row['mem_num'] + '">';
str += '<input type="hidden" class="data-current_status" value="' + row['current_status'] + '">';
str += '<input type="hidden" class="data-initial_status" value="' + row['initial_status'] + '">';
return '<p class="data-channel" style="width:50px">' + row['activity_name'] + '</p>' + str;
}
},
{
targets: 2,
render: function(data, type, row, meta) {
return '<p style="width:110px">' + row['channel'] + '</p>';
}
},
{
targets: 3,
render: function(data, type, row, meta) {
return '<p style="width:115px">' + row['initial_status'] + '</p>';
}
},
{
targets: 4,
render: function(data, type, row, meta) {
return '<p style="width:115px">' + row['current_status'] + '</p>';
}
},
{
targets: 5,
render: function(data, type, row, meta) {
return '<p style="width:100px">' + row['phone'] + '</p>';
}
},
{
targets: 6,
render: function(data, type, row, meta) {
return '<p style="width:100px">' + row['mem_num'] + '</p>';
}
},
{
targets: 7,
render: function(data, type, row, meta) {
return '<p style="width:60px">' + row['action_type'] + '</p>';
}
},
{
targets: 8,
render: function(data, type, row, meta) {
return '<p style="width:60px">' + row['money'] + '</p>';
}
},
{
targets: 9,
render: function(data, type, row, meta) {
return '<p style="width:80px">' + row['duration'] + '</p>';
}
}
],
"order": [[1, 'asc']]
});
}

控制器端代码：

public function reflux()
{
$this->title = '回流数据';
$do = I('do');

if ($do == 'selData') {

$where = [];
$channel = I('post.channel');
$ad_place = I('post.ad_place');
$activity_name = I('post.activity_name');
$media = I('post.media');
$activity_day = I('post.day');
$keywords = trim(I('post.keywords'));
$initial_status = I('post.initial_status');
$current_status = I('post.current_status');
$action_type = I('post.action_type');

// 关键字
if (!empty($keywords)) {
$where['_string'] = ' (mem_num like "%' . $keywords . '%") OR ( phone like "%' . $keywords . '%") ';
}
// 渠道
$where['channel'] = $channel;

// 广告位
$where['ad_place'] = $ad_place;
// 活动
$where['activity_name'] = $activity_name;
// 媒体
$where['media'] = $media;
// 投放日期
$where['activity_day'] = $activity_day;
// 分页
$data['start'] = I('post.start');
$data['draw'] = I('post.draw');
//$where['start_page'] = $start_page;

// 初级会员级别
if (!empty($initial_status)) {
$where['initial_status'] = $initial_status;
}
// 卡级别
if (!empty($current_status)) {
$where['current_status'] = $current_status;
}
// 行为
if (!empty($action_type)) {
if($action_type == 'to_member'){
$where['current_status'] = ['like','%卡%'];
$where['_string'] = ' (initial_status like "%-%") OR ( initial_status = "") ';
}else{
$where['action_type'] = $action_type;
}

}

$data = D('AdMonitor')->getBackDataAll($where,$keywords,$data);
$this->ajaxReturn($data);
}
}

model 端代码

public function getBackDataAll($where, $keywords, $data)
{
$draw = $data['draw'];
$recordsTotal = $this->count();
$data = $this->field('id,activity_name,media,channel,ad_place,activity_day,phone,mem_num,initial_status,current_status,action_type,shop,round(sum(money),2) as money,round(sum(duration),2) as duration,action_time')->where($where)->group('mem_num,action_type,current_status,initial_status')->limit($data['start'], 10)->select();
$sql = $this->getLastSql();

$countSql = $this->field('id,activity_name,media,channel,ad_place,activity_day,phone,mem_num,initial_status,current_status,action_type,shop,round(sum(money),2) as money,round(sum(duration),2) as duration,action_time')->where($where)->group('mem_num,action_type,current_status,initial_status')->buildSql();
$recordsFiltered = $this->table("{$countSql} as t")->count();
// 此处是下载进行的拼接url
$url = U('Sale/getExportData', ['keywords' => $keywords, 'channel' => $where['channel'], 'ad_place' => $where['ad_place'], 'media' => $where['media'], 'initial_status' => $where['initial_status'], 'current_status' => $where['current_status'], 'action_type' => $where['action_type'], 'activity_day' => $where['activity_day'], 'activity_name' => $where['activity_name']]);
$sqlLogFile = 'sql_refux.txt';
if ('on' == C('SQL_LOG_RECORDER')) {
file_put_contents($sqlLogFile, '');
file_put_contents($sqlLogFile, '回流数据：' . $sql . "\n\n", FILE_APPEND);
}
$list = array(
"draw" => intval($draw),
'sql' => $sql,
"recordsTotal" => intval($recordsTotal),
"recordsFiltered" => intval($recordsFiltered),
"data" => $data?$data:'',
);
return $list;
}

阅读全文

0 0