Codeigniter Datatables服务器端中的列不明确

Question

我正在开发一个有多个服务器端数据表的系统，但当我尝试对列进行排序时，我遇到了2个联接的问题。

当尝试对列进行排序时，我收到以下消息：

Query error: Column 'notes' in order clause is ambiguous - Invalid query: SELECT *
FROM `tbl_project`
LEFT JOIN `tbl_client` ON `tbl_project`.`client_id`=`tbl_client`.`client_id`
LEFT JOIN `tbl_account_details` ON `tbl_project`.`created_by` = `tbl_account_details`.`user_id`
LEFT JOIN `tbl_notes` ON `tbl_project`.`notes` = `tbl_notes`.`notes_id`
WHERE `tbl_project`.`client_id` = '100'
ORDER BY `notes` DESC
LIMIT 10

这是我查询的代码：

$id = $this->input->post("client_id");
$client_details = get_row('tbl_client', array('client_id' => $id));
$draw = intval($this->input->post("draw"));
$start = intval($this->input->post("start"));
$length = intval($this->input->post("length"));
$order = $this->input->post("order");
$search= $this->input->post("search");
$search = $search['value'];
$col = 0;
$dir = "";
if(!empty($order))
{
foreach($order as $o)
{
$col = $o['column'];
$dir= $o['dir'];
}
}
if($dir != "desc" && $dir != "desc")
{
$dir = "desc";
}
$valid_columns = array(
0=>'project_id',
1=>'client',
2=>'fullname',            
3=>'notes',
4=>'origen',
5=>'end_date',
6=>'project_status',
7=>'action',
);
if(!isset($valid_columns[$col]))
{
$order = null;
}
else
{
$order = $valid_columns[$col];
}
if($order !=null)
{
$this->db->order_by($order, $dir);
}

$searchQuery = "";
if($search != ''){
$searchQuery = " (tbl_project.project_id like'%".$search."%' OR tbl_project.end_date like'%".$search."%' OR tbl_project.project_status like'%".$search."%' OR tbl_notes.notes like'%".$search."%' OR tbl_notes.eco like'%".$search."%' OR tbl_account_details.origen like'%".$search."%' OR tbl_client.name like'%".$search."%') ";
}  
$this->db->select('*');
$this->db->from('tbl_project');
$this->db->join('tbl_client', 'tbl_project.client_id=tbl_client.client_id','left');
$this->db->join('tbl_account_details', 'tbl_project.created_by = tbl_account_details.user_id','left');
$this->db->join('tbl_notes', 'tbl_project.notes = tbl_notes.notes_id','left');
$this->db->where('tbl_project.client_id', $client_details->client_id);
if($searchQuery != '')
$this->db->where($searchQuery);
$this->db->limit($length,$start);
$cita = $this->db->get()->result();

由于某些原因，ORDER BY未设置为tbl_notes.notes

关于如何解决这个问题有什么建议吗？

提前感谢

编辑：我添加了更多的代码，所以过程的可见性更强

Answer 1

由于您的列名不是唯一的，它存在于多个表中，因此会发生错误。

将搜索列的表名附加到查询中以使其唯一：

例如这一行：

$this->db->order_by('my_table_name.'.$order, $dir);

会产生类似的东西

ORDER BY `my_table_name.notes` DESC

编辑：或者，如果您必须寻址多个不同表中的列，您可以更改$valid_columns数组：

$valid_columns = array(
0=>'my_table_name1.project_id',
1=>'my_table_name2.client',
2=>'my_table_name2.fullname',            
3=>'my_table_name3.notes',
// etc.
);

并保留剩余的原始代码。