Codeigniter 3 "No Table Used"当我尝试重用数据库时

Question

我正在尝试制作可重复使用的数据表实例

我的数据表类：

class Datatables extends CI_Model {
protected $columnOrder;
protected $columnSearch;
protected $query;
public function __construct($columnOrder,$columnSearch,$query)
{
parent::__construct();
$this->columnOrder = $columnOrder;
$this->columnSearch = $columnSearch;
$this->query = $query;
}
/**
* Generate db query
*
* @return object
*/
private function getDatatablesQuery()
{
$i = 0;
foreach ($this->columnSearch as $item) {
if(@$_POST['search']['value']) {
if($i===0) {
$this->query->group_start();
$this->query->like($item, $_POST['search']['value']);
} else {
$this->query->or_like($item, $_POST['search']['value']);
}
if(count($this->columnSearch) - 1 == $i)
$this->query->group_end();
}
$i++;
}

if(isset($_POST['order'])) {
$this->query->order_by($this->columnOrder[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
}  else if(isset($this->order)) {
$order = $this->order;
$$this->query->order_by(key($order), $order[key($order)]);
}
}
/**
* Generate db result
*
* @return integer
*/
public function getDatatables() 
{
$this->getDatatablesQuery();

if(@$_POST['length'] != -1) $this->query->limit(@$_POST['length'], @$_POST['start']);

$query = $this->query->get();

return $query->result();
}
/**
* Count filtered rows
*
* @return integer
*/
public function countFiltered() 
{
$query = $this->query->get();

return $query->num_rows;
}
/**
* Count all rows
*
* @return integer
*/
public function countAll() 
{
return $this->query->count_all_results();
}
}

我的FmrTableClass

<?php defined('BASEPATH') OR exit('No direct script access alowed');
require 'application/libraries/Datatables/Datatables.php';
class FmrTable {
protected $select;
protected $columnOrder;
protected $columnSearch;
protected $ci;
public function __construct() 
{
$this->select = 'fmrs.id as id,sections.name as section,users.username as user,fmr_no,fmrs.status';
$this->columnOrder = ['id','section','user','fmr_no','status'];
$this->columnSearch = ['section','user','fmr_no','status'];
$this->ci = get_instance();
}
public function get()
{
$query = $this->ci->db
->select($this->select)
->from('fmrs')
->join('sections as sections', 'fmrs.section_id = sections.id', 'LEFT')
->join('users as users', 'fmrs.user_id = users.id', 'LEFT');
$query->where('section_id',$this->ci->session->userdata('section-fmr'));
}

$datatable = new Datatables($this->columnOrder,$this->columnSearch,$query);
return [
'list' => $datatable->getDatatables(),
'countAll' => $datatable->countAll(),
'countFiltered' => $datatable->countFiltered()
];
}
}

这总是抛出一个数据库错误，显示错误编号：1096未使用任何表

这来自countFiltered((方法，当我尝试在没有get((的情况下转储$query时，它返回了正确的对象实例，但如果我这样做，则num_rows属性将永远不可用，但当我添加get((方法时，它将返回1096错误号

如何解决这个问题？

Answer 1

对->get()的调用将重置查询生成器。因此，当您第二次调用->get()(在countFiltered中(时，表名和查询的其余部分已被清除，这就是为什么会出现错误的原因。

解决方案是使用查询生成器缓存。这允许您缓存部分查询(在start_cache和stop_cache之间(并多次执行：https://www.codeigniter.com/userguide3/database/query_builder.html?highlight=start_cache#query-生成器缓存

之后使用flush_cache清除缓存，这样缓存的查询部分就不会干扰同一请求中的后续查询：

FmrTable

public function get()
{
$this->ci->db->start_cache();
$query = $this->ci->db
->select($this->select)
->from('fmrs')
->join('sections as sections', 'fmrs.section_id = sections.id', 'LEFT')
->join('users as users', 'fmrs.user_id = users.id', 'LEFT');
$query->where('section_id',$this->ci->session->userdata('section-fmr'));
//}
$this->ci->db->stop_cache();

$datatable = new Datatables($this->columnOrder,$this->columnSearch,$query);
$result = [
'list' => $datatable->getDatatables(),
'countAll' => $datatable->countAll(),
'countFiltered' => $datatable->countFiltered()
];
$this->ci->db->flush_cache();
return $result;
}

这里可能使用num_rows()而不是num_rows，num_rows给了我一个NULL而不是计数：

数据表

/**
* Count filtered rows
*
* @return integer
*/
public function countFiltered() 
{
$query = $this->query->get();

return $query->num_rows();
}