代码点火器count_all_results()和result()与group_by，join，其中vs

Question

我想用group_by查询结果和计数。

$id= 2;
$limit = 10;
$offset = ($id - 1) * $limit;
$this->db->group_by('BE.etiket_id');
$this->db->from('bulten_icerigi_etiketler as BE');
$this->db->join('etiketler as E', 'BE.etiket_id=E.id', 'left');
$this->db->join('bulten_icerigi as BI', 'BE.bulten_id=BI.id', 'left');
$count = $this->db->count_all_results('', false);
$this->db->limit($limit, $offset);
$this->db->order_by('E.adi');
$results = $this->db->get()->result();

然而，我有错误

Error Number: 1054
Unknown column 'tbl_`E.adi' in 'field list'

我可以同时使用count_all_results((和result((吗？

谢谢。

Answer 1

您不能选择未在select中选择的group by列。

尝试在group by中使用E.adi和E.id，而不是BE.etiket_id。您的查询应该如下所示：

SELECT 
COUNT(*) as count, 
tbl_E.adi, 
tbl_E.id 
FROM tbl_bulten_icerigi_etiketler as BE 
LEFT JOIN tbl_etiketler as E ON BE.etiket_id=E.id 
LEFT JOIN tbl_bulten_icerigi as BI ON BE.bulten_id=BI.id 
GROUP BY tbl_E.adi, tbl_E.id    // here you need to change 
ORDER BY tbl_E.adi

$this->db->group_by(array("E.adi", "E.id"));