CodeIgniter – How to combine where() and where_not_in() in one query?

In one of my CodeIgniter based application, I need to combine both where() and where_not_in() in one single query. The query I wrote is:

$where = array(
    'proj.project_code' =>  $project_code
);

$where_not_in = array();

if( $open_ticket == 1 ) {
    $where_not_in['tic.status_id'] = $this->getStatusIdByStatusName('draft');
    $where_not_in['tic.status_id'] = $this->getStatusIdByStatusName('voided');
    $where_not_in['tic.status_id'] = $this->getStatusIdByStatusName('closed');
} else {
    $where_not_in['tic.status_id'] = $this->getStatusIdByStatusName('new');
    $where_not_in['tic.status_id'] = $this->getStatusIdByStatusName('waiting');
    $where_not_in['tic.status_id'] = $this->getStatusIdByStatusName('on hold');
    $where_not_in['tic.status_id'] = $this->getStatusIdByStatusName('in progress');
}


$this->db->select('tic.id id, prio.name priority, tic.title title, issue.name type, parent.ticket_code parent_code, par_proj.project_code par_proj_code, status.name status, usr.first_name first_name, usr.last_name last_name, count(exempt.id) exempt, tic.last_edit last_edit, tic.due_date due_date, tic.ticket_code ticket_code, proj.project_code project_code, par_proj.id par_proj_id');
$this->db->from('tickets tic');
$this->db->join('priorities prio', 'prio.id = tic.priority_id', 'left');
$this->db->join('issue_types issue', 'issue.id = tic.issue_type_id', 'left');
$this->db->join('statuses status', 'status.id = tic.status_id', 'left');
$this->db->join('projects proj', 'proj.id = tic.project_id', 'left');
$this->db->join('users usr', 'usr.id = tic.assignee_id', 'left');
$this->db->join('tickets parent', 'parent.id = tic.parent_id', 'left');
$this->db->join('projects par_proj', 'par_proj.id = parent.project_id', 'left');
$this->db->join('exemption_requests exempt', 'exempt.ticket_id = tic.id', 'left');
$this->db->where( $where );
$this->db->where_not_in( $where_not_in );
$this->db->group_by('tic.id, prio.name, tic.title, issue.name, parent.ticket_code, par_proj.project_code, status.name, usr.first_name, usr.last_name, tic.last_edit, tic.due_date');
$this->db->order_by('tic.id', 'ASC');
$this->db->limit( $limit, ($page_no - 1) * $limit );

Here:

$open_ticket is sent from views and is either 0 or 1,
getStatusIdByStatusName(status_name) returns the primary_id from status table
$project_code, $page_no, $limit is sent from views

So when I test the result, it does not filter the data as expected, that’s why I print the generated query by CodeIgniter, and here it is:

SELECT `tic`.`id` `id`, `prio`.`name` `priority`, `tic`.`title` `title`, `issue`.`name` `type`, `parent`.`ticket_code` `parent_code`, `par_proj`.`project_code` `par_proj_code`, `status`.`name` `status`, `usr`.`first_name` `first_name`, `usr`.`last_name` `last_name`, count(exempt.id) exempt, `tic`.`last_edit` `last_edit`, `tic`.`due_date` `due_date`, `tic`.`ticket_code` `ticket_code`, `proj`.`project_code` `project_code`, `par_proj`.`id` `par_proj_id`
    FROM `tickets` `tic`
    LEFT JOIN `priorities` `prio` ON `prio`.`id` = `tic`.`priority_id`
    LEFT JOIN `issue_types` `issue` ON `issue`.`id` = `tic`.`issue_type_id`
    LEFT JOIN `statuses` `status` ON `status`.`id` = `tic`.`status_id`
    LEFT JOIN `projects` `proj` ON `proj`.`id` = `tic`.`project_id`
    LEFT JOIN `users` `usr` ON `usr`.`id` = `tic`.`assignee_id`
    LEFT JOIN `tickets` `parent` ON `parent`.`id` = `tic`.`parent_id`
    LEFT JOIN `projects` `par_proj` ON `par_proj`.`id` = `parent`.`project_id`
    LEFT JOIN `exemption_requests` `exempt` ON `exempt`.`ticket_id` = `tic`.`id`
    WHERE `proj`.`project_code` = '510042520'
    GROUP BY `tic`.`id`, `prio`.`name`, `tic`.`title`, `issue`.`name`, `parent`.`ticket_code`, `par_proj`.`project_code`, `status`.`name`, `usr`.`first_name`, `usr`.`last_name`, `tic`.`last_edit`, `tic`.`due_date`
    ORDER BY `tic`.`id` ASC
    LIMIT 20

as you can see, it doesn’t include the condition of where_not_in() . I can’t figure out how to solve this problem!

Can anyone tell me how can I combine both where() and where_not_in() in one single query.

Thanks

Answer

Your where_not_in() should have two arguments – first being the key tic.status_id and the second being the array.

Change your $where_not_in assignment to:

if( $open_ticket == 1 ) {
    $where_not_in[] = $this->getStatusIdByStatusName('draft');
    $where_not_in[] = $this->getStatusIdByStatusName('voided');
    $where_not_in[] = $this->getStatusIdByStatusName('closed');
} else {
    $where_not_in[] = $this->getStatusIdByStatusName('new');
    $where_not_in[] = $this->getStatusIdByStatusName('waiting');
    $where_not_in[] = $this->getStatusIdByStatusName('on hold');
    $where_not_in[] = $this->getStatusIdByStatusName('in progress');
}

And the where_not_in() call to:

$this->db->where_not_in('tic.status_id', $where_not_in);

where_not_in() does not support the same associative array argument like where().

Advertisement

Answer