Sub-queries in Code Igniter Active Records

Question

I'm attempting to write the following MySql query in CI Active Records. The query uses a sub query though and I'm struggling with this.

The query in question:

SELECT 
                COUNT(*) AS no_skus, 
                pd_vendor AS brand, 
                (SELECT COUNT(DISTINCT(pd_model_code)) 
                FROM product_data
                WHERE pd_vendor = PD.pd_vendor ) AS unique_models
                FROM product_data PD
                GROUP BY pd_vendor
                ORDER BY 
                COUNT(*) DESC";

Could anyone suggest how this could be written/structured as a CI Active Record. Thanks

Refer: https://arjunphp.com/how-to-write-subqueries-in-codeigniter-active-record/ and http://stackoverflow.com/questions/6047149/subquery-in-codeigniter-active-record — Insane Skull, Dec 10 '15 at 09:21
Thanks, that first link resulted in me managing to create the query — Liam Fell, Dec 10 '15 at 10:00

score 1 · Answer 1 · answered Dec 10 '15 at 09:03

1

Check this:

SELECT COUNT(*) AS no_skus, 
        pd_vendor AS brand, 
        (SELECT COUNT(DISTINCTpd_model_code) FROM product_data WHERE pd_vendor = PD.pd_vendor ) AS unique_models
FROM product_data 
GROUP BY pd_vendor
ORDER BY no_skus DESC;

answered Dec 10 '15 at 09:03

Abhishek Ginani

4,511
4
23
35

score 1 · Answer 2 · answered Dec 10 '15 at 12:22

In Codeigniter way you can write this subquery like this

function getProductData(){
$this->db->select("COUNT(*) AS no_skus,pd_vendor AS brand,(SELECT COUNT(DISTINCT(pd_model_code)) FROM product_data
                WHERE pd_vendor = PD.pd_vendor ) AS unique_models",false);// second parameter for escape--we disable to escap as codeigniter adding ` to protect identifiers
            $this->db->from("product_data PD");
            $this->db->group_by('pd_vendor');
            $this->db->order_by('no_skus', 'DESC');
            $result_arr=$this->db->get()->result_array();
return $result_arr

}

score 0 · Answer 3 · answered Dec 10 '15 at 10:00

I managed to get this working, admittedly using fairly loosely types Active Record syntax. The code is here:

// Main Query
$this->db->select('
        COUNT(*) AS no_skus,
        pd_vendor AS brand')
        ->from('product_data PD')
        ->group_by('pd_vendor');

// Sub Query
$this->db->select('(SELECT
        COUNT(DISTINCT(pd_model_code)) 
        FROM product_data
        WHERE pd_vendor = PD.pd_vendor ) 
        AS unique_models');
$subQuery = $this->db->last_query();

$query = $this->db->get();
return $query->result();

Sub-queries in Code Igniter Active Records

3 Answers3