尝试使用Active Record在codeigniter中整合我的查询...这是更好的方法

Question

I have a codeigniter app that my model sends out some stats for the current month to the front end. My code now seems very redundant but I don't know how to combine using Active Record queries. Hoping someone can show me a simplified version of below. Initially I get the month we're in (start and as of today). The only difference between the queries below is $this->db->where('leadStatus', 0). Can the 4 queries be somehow combined?

First gets all next gets current (0) next gets archived (1) and lastly leads that resulted in a deal (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

This is how I would do it in 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();
}

I would go a further step by making a single SQL query however:

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();

You can even get the total counts with pure 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();
}