thinkphp5配合datatable插件分页后端处理程序

thinkphp5配合datatable插件分页后端处理程序
第一版datatable.php v.1.0

  1 <?php2 u think\db;3 /**4 * datat什么是gable.php.5 */6 /**7 * datatable结合thinkphp5使用的分页后台处理程序.8 *9 * @author b.i.t.10 * @copyright copyright (c) 2018-2019, b.i.t.11 * @licen12 *13 * @e 初始化14 *15 * @version v.1.016 */17 class datatable18 {19 /**20 * 数据库对象21 *22 * @var objectc23 */24 public $_db;25 /**26 * 表名.27 *28 * @var string29 */30 protected $_table;31 /**32 * 表格重绘次数.33 *34 * @var int35 */36 protected $_draw;37 /**38 * 排序的是哪行.39 *40 * @var int41 */42 protected $_order_column; //排序的行43 /**44 * 排序方式45 * asc desc.46 *47 * @var string48 */49 protected $_order_dir; //排序方式 asc desc50 /**51 * 查询的数据.52 *53 * @var string54 */55 protected $_arch = ''; //查询的字符串56 /**57 * 查询开始的位置.58 *59 * @var int60 */61 protected $_start; //开始的位置62 /**63 * 每页显示的条目数.64 *65 * @var int66 */67 protected $_length; //查询的长度68 /**69 * 过滤后的条目数量.70 *71 * @var integer72 */73 protected $_recordsfiltered = 0; //过滤后的条目数量74 /**75 * 总的条目数量.76 *77 * @var integer78 */79 protected $_recordstotal = 0; //总的条目数量80 /**81 * 返回的数据 没用到.82 *83 * @var [type]84 */85 protected $_linux vi命令return; //没用到86 /*变速车*87 * 存放构造函数的第二个参数.88 *89 * @var [type]90 */91 protected $_info; //存放构造函数的第二个参数92 93 /**94 * 构造函数 处理datatables发送的数据，用户自定义条件，.95 *96 * @param array $datatableget datatable前台传递过来的数组97 * @param array $info 构造好的数组结构如下98 * example array( //表示 lect id as sum,id,urnmae 其中 sum用来统计数据的总条数99 * "lect"=array(100 * "id"=>"sum",101 * "0"=>'id',102 * "1"=>"urname",103 * )，104 * "order"=>array( //前台会发送过来根据哪一列排序 接收过来的值就是 键值，对应到数据表中的字段就是值，前台有几个列能够排序这里就需要有几个对应的键值队105 * "0"=>"id",106 * "2"=>"urname",107 * ),108 * "where"=>array( //and和or可以同时调用 但是or是用来做查询的 and则是初始数据的查询条件109 * "and"=>array( //表示会查询state=1 and level=2 and (a=1 or a=2) 的数据110 * "state"=>'1',111 * "level"=>"2",112 * "a"=>[1,2]113 * ),114 *115 * 注："or" 用户搜索的时候存在一个问题，参与搜索查询的列必须为同一类型比如id和name字段就不可以一起搜索会出现输入 116 * "qwer" sql为 (id like 0 or name like %qwer%)的情况117 *118 * "or"=>array("id","urname"), //用户查询的时候回根据这里的参数作为查询的列 例如 当arch=root时 就会查询 id like %root% or urname like %root%119 * "or2"=>[ //这里面的会用or连接 几乎很少用 例如下面的数组会变成: a=1 or a=2 or b=2120 * a=>[1,2],121 * b=>[2]122 * ]123 * ),124 * "join"=>array(125 * 'class'=>array('student.class_id','class.id'),126 * 'x'=>array('student.class_id','x.id')127 * )128 * )129 * @param string $db 数据库对象130 *131 * @return object this132 */133 public function __construct($datatableget, $info = array(), $table = '')134 {135 $this->_init($datatableget);136 $this->_table = $table;137 $th七夕出租搞笑图片is->_db = db::table($this->_table);138 $this->_info = $info;139 }140 141 /**142 * 初始化参数,检查数据格式.143 *144 * @param array $data datatables发送过来的数据145 */146 protected function _init($data)147 {148 if (ist($data)) {149 $this->_draw = ist($data['draw']) ? $data['draw'] : null;150 $this->_length = ist($data['length']) ? intval($data['length']) : null;151 $this->_start = ist($data['start']) ? intval($data['start']) : null;152 $this->_order_column = ist($data['order']['0']['column']) ? intval($data['order']['0']['column']) : null;153 $this->_order_dir = ist($data['order']['0']['dir']) ? $data['order']['0']['dir'] : null;154 $this->_arch =面面俱到的意思 ist($data['arch']['value']) ? $data['arch']['value'] : null;155 }156 }157 158 /**159 * 输出datatables需要的数据格式，还需要打成json的格式才行.160 *161 * @param bool $debug true 会输出一些调试信息 默认fal162 *163 * @return array atatables需要的数据格式164 */165 public function output($debug = fal)166 {167 $data = $this->_info;168 if (ist($data['lect']) && !empty($data['lect'])) {169 $lectsql = $this->_getlectsql($data['lect']);170 } el {171 $lectsql = '*';172 }173 $this->_db = $this->_db->field($lectsql);174 if (ist($data['join']) && !empty($data['join'])) {175 foreach ($data['join'] as $key => $value) {176 $this->_db = $this->_db->join($key,$value[0].' = '.$value[1]);177 }178 }179 if (ist($data['order']) && !empty($data['order'])) {180 $ordersql = $this->_getordersql($data['order']);181 $this->_db = $this->_db->order($ordersql);182 }183 if (ist($data['where']) && !empty($data['where'])) {184 $and = ist($data['where']['and']) && !empty($data['where']['and']) ? $data['where']['and'] : null;185 $or = ist($data['where']['or']) && !empty($data['where']['or']) ? $data['where']['or'] : null;186 $or2 = ist($data['where']['or2']) && !empty($data['where']['or2']) ? $data['where']['or2'] : null;187 if(!is_null($and)){188 foreach ($and as $key => $value) {189 if(is_array($value)){190 foreach ($value as $k => $v) {191 $this->_db = $this->_db->whereor($key,$v);192 }193 unt($and[$key]);194 }195 }196 $this->_db = $this->_db->where($and);197 }198 if(!is_null($or)){199 $or_val = '';200 for ($i=0; $i < count($or); $i++) { 201 $or_val .= $i == count($or)-1 ? $or[$i] : $or[$i].'|';202 }203 if(!is_null($this->_arch)&&$this->_arch!=''){204 $this->_db = $this->_db->where($or_val,'like','%'.$this->_arch.'%');205 }206 }207 if(!is_null($or2)){208 foreach ($or2 as $key => $value) {209 if(!is_array($value)) return fal;210 foreach ($value as $k => $v) {211 $this->_db = $this->_db->whereor($key,$v);212 }213 }214 }215 }216 if (ist($this->_start) && ist($this->_length)) {217 $this->_db = $this->_db->limit($this->_start,$this->_length);218 }219 if ($debug) {220 var_dump($this->_db->fetchsql(true)->lect());die;221 }222 $db1 = clone $this->_db;223 $info = $this->_db->lect();224 $this->_recordstotal = $db1->count();225 $this->_recordsfiltered = $this->_recordstotal;226 return array(227 'draw' => intval($this->_draw),228 'recordstotal' => intval($this->_recordstotal),229 'recordsfiltered' => intval($this->_recordsfiltered),230 'data' => $info,231 );232 }233 234 /**235 * 当前台需要排序的时候会根据需要排序的列对应的表中的值排序236 * 构建排序的sql.237 *238 * @param array $data 构造函数第二个参数order部分239 *240 * @return string 排序部分的sql语句241 */242 protected function _getordersql($data)243 {244 $sql = '';245 if (is_array($data)) {246 foreach ($data as $key => $value) {247 if ($key == $this->_order_column) {248 $sql = "{$value} ".$this->_order_dir;249 break;250 }251 }252 }253 return $sql;254 }255 256 /**257 * 构建lect部分的sql语句.258 *259 * @param array $data 构造函数第二个参数lect部分260 *261 * @return string lect部分的语句262 */263 protected function _getlectsql($data)264 {265 $list = array();266 foreach ($data as $key => $value) {267 if (is_numeric($key)) {268 array_push($list, $value);269 } el {270 array_push($list, $key.' as '.$value);271 }272 }273 if (!empty($list)) {274 $lectsql = implode(',', $list);275 } el {276 $lectsql = '*';277 }278 return $lectsql;279 }280 }

控制器类调用

 1 <?php2 3 namespace app\index\controller;4 5 u think\controller;6 u think\db;7 u datatable;8 9 class index extends controller10 {11 public function list()12 {13 return view('index/list');14 }15 16 public function list_ajax()17 {18 $get = input('get.');19 $where = [];20 $data['lect'] = ['think_ur.id'=>'t_id','think_ur.uname','think_ur.upwd','think_banji.banji_name','think_ur.status'];21 $data['order'] = ['0'=>'think_ur.id','1'=>'think_ur.uname','2'=>'think_ur.upwd','3'=>'think_banji.banji_name'];22 // $data['where']['and'] = ['think_ur.status'=>1,'think_ur.id'=>[1,2]];23 $data['where']['or'] = ['think_ur.uname','think_ur.upwd'];24 $data['join'] = [25 'think_banji'=>['think_ur.banji_id','think_banji.id'],26 ];27 $a = new datatable($get, $data, 'think_ur');28 return $a->output();29 }30 }