How to change SQL format-CodePudding

I have an SQL Query like this

return $this->db->query("SELECT * FROM `rincian_permintaan` 
    JOIN `permintaan` ON `permintaan`.`id_permintaan` = `rincian_permintaan`.`id_permintaan` 
    JOIN `users` ON `permintaan`.`id_peminta` = `users`.`user_id` 
    JOIN `cabang` ON `cabang`.`id_cabang` = `users`.`id_cabang` 
    JOIN `barang` ON `barang`.`id_barang` = `rincian_permintaan`.`id_barang` 
    JOIN `po` ON `rincian_permintaan`.`id_po` = `po`.`id_po` WHERE `po`.`id_cabang` = '201' AND `users`.`id_cabang` != 201 AND 
    NOT EXISTS(SELECT * FROM airwaybill WHERE airwaybill.id_rincian_permintaan = rincian_permintaan.id_rincian_permintaan)
    ORDER BY rincian_permintaan.created_at DESC")->result_array();

I want to change the format to something like this..

$this->db->select('*');
$this->db->from('tableName');
$this->db->join('...');
$this->db->where('...');
...

And this is what i've tried...

$this->db->select('*');
    $this->db->from('rincian_permintaan');
    $this->db->join('permintaan', 'permintaan.id_permintaan = rincian_permintaan.id_permintaan');
    $this->db->join('users', 'permintaan.id_peminta = users.user_id');
    $this->db->join('cabang', 'cabang.id_cabang = users.id_cabang');
    $this->db->join('barang', 'barang.id_barang = rincian_permintaan.id_barang');
    $this->db->join('po', 'rincian_permintaan.id_po = po.id_po');
    $this->db->where('po.id_cabang', '201');
    $this->db->where('users.id_cabang != 201');
    
    $this->db->select('*');
    $this->db->from('airwaybill');
    $this->db->where('NOT EXISTS airwaybill.id_rincian_permintaan = rincian_permintaan.id_rincian_permintaan', '', FALSE);
    return $this->db->get('rincian_permintaan')->result_array();

Here is the error message

Please let me know, how to fix it.. Many thanks

CodePudding user response：

All you need is to generate sql string for subquery without executing it and use it in the outer query and produce the result.

$this->db->select('*');
$this->db->from('airwaybill');
$this->db->where('airwaybill.id_rincian_permintaan = rincian_permintaan.id_rincian_permintaan');
$sub_query = $this->db->get_compiled_select();

$this->db->select('*');
$this->db->from('rincian_permintaan');
$this->db->join('permintaan', 'permintaan.id_permintaan = rincian_permintaan.id_permintaan');
$this->db->join('users', 'permintaan.id_peminta = users.user_id');
$this->db->join('cabang', 'cabang.id_cabang = users.id_cabang');
$this->db->join('barang', 'barang.id_barang = rincian_permintaan.id_barang');
$this->db->join('po', 'rincian_permintaan.id_po = po.id_po');
$this->db->where('po.id_cabang', '201');
$this->db->where('users.id_cabang != 201 ');
$this->db->where('NOT EXISTS('.$sub_query.')');

$query = $this->db->get();
$result = $query->result_array();
return $result;