Как использовать подзапросы в качестве таблиц JOINed с помощью построителя запросов CodeIgniter ⇐ Php

[Anonymous]

Мне нужно использовать 2 подзапроса в 1 основном запросе. Я построил это:

Код: Выделить всё

$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();

Но когда я запускаю код, я получаю:

Код: Выделить всё

"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

Как обеспечить хорошую работу этого кода?

Подробнее здесь: https://stackoverflow.com/questions/651 ... ry-builder