sql - Codeigniter 3 中的子查询如何

Question

我有 1 个主查询的 2 个子查询，然后我构建这个：

$this->db->select_min('(log1.id_layanan_log) AS id_ambil, log1.wk_rekam AS wk_reka,log1.kode_tiket AS kode_tiket,log1.kode_step AS kode_step');
$this->db->from('layanan_log log1'); 
$this->db->where('log1.aksi' , 'buka' ); 
$this->db->where('log1.kode_step' , '00000');
$this->db->group_by('log1.kode_tiket');
$subQuery1 = $this->db->get_compiled_select();

$this->db->reset_query();  

$this->db->select_max('(log3.id_layanan_log) AS id_submit, log3.wk_rekam  AS wk_rekam,log3.kode_tiket AS kode_tiket,log3.kode_step AS kode_step');
$this->db->from('layanan_log log3'); 
$this->db->where('log3.aksi' , 'submit' ); 
$this->db->where('log3.kode_step' , '00000');
$this->db->group_by('log3.kode_tiket');
$subQuery2 = $this->db->get_compiled_select();

$this->db->reset_query(); 

$this->db->select('TIMEDIFF(logb.wk_rekam, loga.wk_rekam) AS Lama_Pengerjaan,
TIMEDIFF(dis.deadline_step,dis.start_argo) AS SLA,
dis.nip_petugas AS Petugas,
dis.kode_tiket AS kode_tiket,
dis.start_argo AS wk_start,
loga.wk_rekam AS wk_ambil,
logb.wk_rekam AS wk_submit,
dis.deadline_step AS wk_deadline');
$this->db->from('layanan_disposisi dis');
$this->db->join('($subQuery1) loga','dis.kode_tiket = loga.kode_tiket AND dis.kode_step = loga.kode_step', 'left');
$this->db->join('($subQuery2) logb','dis.kode_tiket = logb.kode_tiket','left');
$this->db->where('dis.kode_step', '00000');
$this->db->where("'dis.deadline_step' BETWEEN '2020-10-20 00:00:00' AND '2020-11-20 23:59:59'", NULL, FALSE);
$query = $this->db->get();

但是在我在 Mozilla 上运行之后，控制台上说：

"Error Number: 1064"
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'loga ON `dis`.`kode_tiket` = `loga`.`kode_tiket` LEFT JOIN ($subQuery2) logb ON ' at line 3 

SELECT TIMEDIFF(logb.wk_rekam, loga.wk_rekam) AS Lama_Pengerjaan, TIMEDIFF(dis.deadline_step, dis.start_argo) AS SLA, `dis`.`nip_petugas` AS `Petugas`, `dis`.`kode_tiket` AS `kode_tiket`, `dis`.`start_argo` AS `wk_start`, 
`loga`.`wk_rekam` AS `wk_ambil`, `logb`.`wk_rekam` AS `wk_submit`, `dis`.`deadline_step` AS `wk_deadline` FROM `layanan_disposisi` `dis` LEFT JOIN ($subQuery1) loga ON `dis`.`kode_tiket` = `loga`.`kode_tiket` LEFT JOIN 
($subQuery2) logb ON `dis`.`kode_tiket` = `logb`.`kode_tiket` WHERE `dis`.`kode_step` = '00000' AND 'dis.deadline_step' BETWEEN '2020-11-25 00:00:00' AND '2020-11-27 23:59:59' ORDER BY `NIP` ASC LIMIT 10

问题是如何使这段代码运行良好。好困惑，非常感谢你的回答

标签： sqlcodeignitersubquerycodeigniter-3

Answer 1

您需要将变量与查询生成器连接起来。

$this->db->join('(' . $subQuery1 . ') loga', 'dis.kode_tiket = loga.kode_tiket AND dis.kode_step = loga.kode_step', 'left');
$this->db->join('(' . $subQuery2 . ') logb', 'dis.kode_tiket = logb.kode_tiket','left');