代码之家 › 专栏 › 技术社区 › geeth

使用ActiveRecord计算id匹配的行数,另一列匹配两个值之一

where-clause activerecord codeigniter mysql php

geeth · 技术社区 · 7 年前

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

因此,我在模型中创建了一个函数:

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');
}

但该函数生成如下查询:

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

由于条件不同,这两个查询生成不同的结果。我需要将或获得正确结果的条件。如何使用CodeIgniter的活动记录查询构建方法实现这一点?

4 回复 | 直到 3 年前

Akshay Hegde 7 年前

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() 接受可选的第三个参数。如果您设置如果设置为FALSE,CodeIgniter将不会试图保护您的字段或表带反勾号的名称。

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

romal tandel 7 年前

将组与查询生成器一起使用

function submittedSMSCount($smsUsageID) {        
    $this->db->select('COUNT(*) AS numrows')
    ->group_start()
    ->where('lower(ResponseStatus)', 'submitted')
    ->or_where('lower(ResponseStatus)','inqueue')
    ->group_end()
    ->where('SMSUsageID', $smsUsageID);
    return $this->db->get('smslog_tbl');
}

输出

从中选择COUNT(*)作为numrows smslog_tbl 其中(lower(ResponseStatus)=“submitted”或lower(ResponseStatus)=“inqueue”)和 SMSUsageID = '1'

Hanan Ashraf 7 年前

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

mickmackusa 3 年前

而不是试图用鞋钉 OR 在您的查询中,只需使用 IN() .

代码:

public function submittedSMSCount(int $smsUsageID): int
{
    return $this->db->where_in('LOWER(ResponseStatus)', ['submitted', 'inqueue'])
                    ->where('SMSUsageID', $smsUsageID)
                    ->count_all_results('smslog_tbl');
}

这将导致 LOWER(ResponseStatus) 匹配两个白名单中的一个值,并 SMSUsageID 将等于 $smsUsageID .