Codeigniter where not exists does not work

Codeigniter where not exists does not work - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11)
+--- Thread: Codeigniter where not exists does not work (/showthread.php?tid=77781)

Codeigniter where not exists does not work - optic4predator1 - 10-18-2020

I'm working on Codeigniter project and came across a problem. I would like that my function on my model to bring me the data from the POST table, where POST_ID in CATEGORY Table is not null, without joining the table, so (do not bring back the data whose POST_ID in CATEGORY table not exists). so i tried this method how can i get this result?

Code:
code: // MyModel

function get_data() {

    $id = isset($_REQUEST['id']) ? $_REQUEST['id'] : '';

    if ($id != null) {

        $this->db->where('ID',$id);

        $result =  $this->db->select('POST.ID,POST.DATE,'

        . ' TYPE.Post_Type P_TYPE,)

        ->join('TYPE', 'POST.TYPE_ID = TYPE.ID', 'left')

        ->order_by('POST.ID', 'DESC')

        ->where_in('POST.Post_Type  = type_1, type_2')

        ->where("NOT EXISTS(SELECT POST_ID FROM CATEGORY WHERE POST.ID=CATEGORY.POST_ID )", FALSE)

        ->get('POST');

    } 

    return $result;

}

Thank you for help

RE: Codeigniter where not exists does not work - ojmichael - 10-18-2020

Why would you not want to use a JOIN? It would certainly be faster than a subquery.

You could probably do something like this (untested):

PHP Code:
$result = $this->db->select('POST.ID, POST.DATE, TYPE.Post_type AS P_TYPE, (SELECT ID FROM CATEGORY WHERE CATEGORY.POST_ID = POST.ID) AS CID', false)
  ->join('TYPE', 'POST.TYPE_ID = TYPE.ID', 'left')
  ->where('POST.ID', $id)
  ->where_in('TYPE.Post_Type', ['type_1', 'type_2'])
  ->order_by('POST.ID', 'DESC')
  ->having('CID IS NOT NULL', false)
  ->get('POST'); 

RE: Codeigniter where not exists does not work - optic4predator1 - 10-20-2020

Thank you for replying i tried this code but i get this error : ORA-00933 SQL Command Not Properly Ended

RE: Codeigniter where not exists does not work - InsiteFX - 10-21-2020

Your if statement is going to always work because both values assigned to $id
will not be null as you think it will be if you want to check it to null replace '' with null.

PHP type comparison tables