Codeigniter where not exists does not work

Question

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: // 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

Answer 1

It would be better to use IN if you are trying to have a data where is not null ( meaning it exists ) or you can also change it to NOT IN if the answer you are looking for is the other way around. anyway, i did not change anything in your query, i just put the POST_ID and IN in the where statement

function get_data() {

    $id = isset($_REQUEST['id']) ? $_REQUEST['id'] : '';
    if ($id != null) {

        $in = array('type_1', 'type_2');
        $this->db->where('ID',$id);
        $this->db->select('POST.ID,POST.DATE,TYPE.Post_Type as P_TYPE', FALSE);
        $this->db->join('TYPE', 'POST.TYPE_ID = TYPE.ID', 'left');
        $this->db->order_by('POST.ID', 'DESC');
        $this->db->where_in('POST.Post_Type', $in);
        $this->db->where("POST.POST_ID IN(SELECT C.POST_ID FROM CATEGORY C )");
        $this->db->from('POST');
        $result = $this->db->get();
    } 
    return $result;
}