CodeIgniter Forums
Optimise Model function? - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Optimise Model function? (/showthread.php?tid=22371)



Optimise Model function? - El Forum - 09-07-2009

[eluser]JasonS[/eluser]
Hi, I have a function and I was wondering if someone could help me improve it. The problem is that it uses 1 query to get the company names, then an additional query for each company to count the number of coupons related to said company.

Is there a way I can combine this into one query or perhaps structure it better? I am a bit concerned about what will happen if my client creates 50 or a 100 featured companies.

Code:
// Get Featured Companies
public function get_featured_companies()
{
    // Define Container
    $data = array();
    
    // Grab featured companies
    $companies = $this->db->where('featured', 1)->get('companies');
    
    if ($companies->num_rows() > 0)
    {
        // Loop through SQL results
        foreach ($companies->result() as $company)
        {
            $coupons = $this->db->select('id')->where('company', $company->id)->get('coupons');
            $data[] = array (
                'id' => $company->id,
                'name' => $company->name,
                'coupons' => $coupons->num_rows()
            );                
        }
    }
    return $data;
}



Optimise Model function? - El Forum - 09-07-2009

[eluser]Dam1an[/eluser]
Have you tried with a join? Something like...
Code:
SELECT `companies`.`id`, `companies`.`name`, COUNT(`coupons`.`id`) AS `coupon_count`
FROM `companies`
JOIN `coupons` ON `companies`.`id` = `coupons`.`company`
WHERE `featured` = 1



Optimise Model function? - El Forum - 09-07-2009

[eluser]Johan André[/eluser]
Joins must be the BIG mystery of SQL...
I always solved problems like this without them before.
I found joins very confusing then.
I found a great guide (a couple of years ago) and I studied it and
it was like I discovered a whole new universe! Smile

Viva la join!