[SOLVED] Use ActiveRecord to count rows where id is matched and another column matches one of two values

Use ActiveRecord to count rows where id is matched and another column matches one of two values

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 my 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 generate different results because of the conditions. I need to group the OR conditions to get the correct result. How can I do this with CodeIgniter's Active Record query building methods?

Solution

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