正在尝试使用活动记录在codeigniter中合并我的查询..一定是更好的方法

Question

我有一个codeigniter应用程序，我的模型会将当月的一些统计数据发送到前端。我的代码现在看起来非常多余，但我不知道如何使用活动记录查询进行组合。希望有人能给我看下面的简化版本。最初，我得到了我们所在的月份（从今天开始）。以下查询之间的唯一区别是$this->db->where（'leadStatus'，0）。这4个查询能以某种方式组合起来吗？

先斩后奏next获取当前值（0）下一个被存档（1）最后导致交易的线索（2）

    function get_stats($data) {
date_default_timezone_set('America/Halifax');
$today = date("Y-m-t 23:59:59") ;
$monthStart = date('Y-m-01 00:00:00');
$parent_id = $data['parent_id'];

//leads all
$this->db->select('id');
$this->db->from('leads');
$this->db->where('parent_id', $parent_id);
$this->db->where('leadCreated >=', $monthStart);
$this->db->where('leadCreated <=', $today);
$query = $this->db->get();
$data['total_leads'] = $query->num_rows();

//leads current
$this->db->select('id');
$this->db->from('leads');
$this->db->where('parent_id', $parent_id);
$this->db->where('leadStatus', 0);
$this->db->where('leadCreated >=', $monthStart);
$this->db->where('leadCreated <=', $today);
$query = $this->db->get();
$data['leads_current'] = $query->num_rows();
//leads archived
$this->db->select('id');
$this->db->from('leads');
$this->db->where('parent_id', $parent_id);
$this->db->where('leadStatus', 1);
$this->db->where('leadCreated >=', $monthStart);
$this->db->where('leadCreated <=', $today);
$query = $this->db->get();
$data['leads_archived'] = $query->num_rows();

//leads sent to deal
$this->db->select('id');
$this->db->from('leads');
$this->db->where('parent_id', $parent_id);
$this->db->where('leadStatus', 2);
$this->db->where('leadCreated >=', $monthStart);
$this->db->where('leadCreated <=', $today);
$query = $this->db->get();
$data['leads_todeal'] = $query->num_rows();

Answer 1

这就是我在PHP:中的操作方式

function get_stats($data) {
    date_default_timezone_set('America/Halifax');
    $today = date("Y-m-t 23:59:59") ;
    $monthStart = date('Y-m-01 00:00:00');
    $parent_id = $data['parent_id'];
    $data = array(
        'total_leads' => $this->getLeadsCount($parent_id, $monthStart, $today),
        'leads_current' => $this->getLeadsCount($parent_id, $monthStart, $today, 0),
        'leads_archived' => $this->getLeadsCount($parent_id, $monthStart, $today, 1),
        'leads_todeal' =>  $this->getLeadsCount($parent_id, $monthStart, $today, 2),
    );
}
function getLeadsCount($parent_id, $monthStart, $today, $leads = false) {
    $this->db->select('id');
    $this->db->from('leads');
    $this->db->where('parent_id', $parent_id);
    $this->db->where('leadCreated >=', $monthStart);
    $this->db->where('leadCreated <=', $today);
    if ($leads !== false) {
        $this->db->where('leadStatus', $leads);
    }
    return $this->db->get()->num_rows();
}

然而，我会更进一步，生成一个SQL查询：

function getLeadsCount($parent_id, $monthStart, $today) {
    $this->db->select('COUNT(*) AS count', false);
    $this->db->select('leadStatus');
    $this->db->from('leads');
    $this->db->where('parent_id', $parent_id);
    $this->db->where('leadCreated >=', $monthStart);
    $this->db->where('leadCreated <=', $today);
    $this->db->group_by('leadStatus');
    return $this->db->get()->result_array();
}
// Returns an array of arrays, each array containing 'leadStatus' and 'count' keys.
// Note that this won't return total leads - you can combine all the values to get it, though.
$this->getLeadsCount();

您甚至可以使用纯SQL获得总计数：

function getLeadsCount($parent_id, $monthStart, $today) {
    $sql = "SELECT leadStatus, COUNT(*) AS count
            FROM `leads`
            WHERE parent_id = ?
            AND leadCreated >= ?
            AND leadCreated <= ?
            GROUP BY leadStatus WITH ROLLUP";
    // WITH ROLLUP returns an extra row with the total count, but with NULL for leadStatus.
    return $this->db->query($sql, array($parent_id, $monthStart, $today))->result_array();
}

Answer 2

大免责声明-尚未对此进行测试！但我认为它应该工作

// make an array to hold your common where search values 
$leadsearch = array('parent_id' => $parent_id, 'leadCreated >=' => $monthStart, 'leadCreated <=' => $today);

// now "chain" it together to make it more compact
//leads all
$this->db->select('id')->from('leads')->where($leadsearch) ;
$query = $this->db->get();
$data['total_leads'] = $query->num_rows();
// add the extra where condition of lead status    
//leads archived
$this->db->select('id')->from('leads')->where($leadsearch)->where('leadStatus', 1) ;
$query = $this->db->get();
$data['leads_archived'] = $query->num_rows();

有关详细信息，请参阅手册中的此页http://ellislab.com/codeigniter/user-guide/database/active_record.html