在codeigniter中的条件组

Question

I need to create a query in codeigniter as follows:

SELECT COUNT(*) AS `numrows` FROM `smslog_tbl` WHERE (lower(ResponseStatus) = 'submitted' OR lower(ResponseStatus) = 'inqueue') AND `SMSUsageID` = '1'

so i created a function in model as :

function submittedSMSCount($smsUsageID) {        
    $this->db->where('lower(ResponseStatus)', 'submitted');
    $this->db->or_where('lower(ResponseStatus)', 'inqueue');
    $this->db->where('SMSUsageID', $smsUsageID);
    return $this->db->count_all_results('smslog_tbl');
}

but this function generates a query like :

SELECT COUNT(*) AS `numrows` FROM `smslog_tbl` WHERE lower(ResponseStatus) = 'submitted'OR lower(ResponseStatus) = 'inqueue' AND `SMSUsageID` = '1'

these two queries generates different results because of the conditions. I need to group OR conditions to get the correct result. How can i do this with codeigniter

Answer 1

function submittedSMSCount($smsUsageID) {       

$this->db->where(
       "(lower(ResponseStatus) = 'submitted' OR lower(ResponseStatus) = 'inqueue')", 
       NULL, 
       FALSE
 );  
$this->db->where('SMSUsageID', $smsUsageID);

return $this->db->count_all_results('smslog_tbl');

}

$this->db->where() accepts an optional third parameter. If you set it to FALSE, CodeIgniter will not try to protect your field or table names with backticks.

Ref : https://www.codeigniter.com/userguide3/database/query_builder.html#looking-for-specific-data