Mutiple SQL count() from several tables

Mutiple SQL count() from several tables - 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: Mutiple SQL count() from several tables (/showthread.php?tid=5913)

Pages: 1 2

Mutiple SQL count() from several tables - El Forum - 03-25-2008

[eluser]oliviermarian[/eluser]
Thxs.
actually i did some tests and when an error occurs it returns the sql generated so I think I managed to do What i wanted.

may be useless out of context but here is my final code, in case it brings light to someone else:
OPTION ACTIVE RECORD:

Code:
$this->db->select("        count(*) AS how_many_comments,

                                comment.id AS comment_id,

                                brand.name AS brand_name,

                                guitar.model AS guitar_model,

                                guitar.year AS guitar_year,

                                guitar.id AS guitar_id

                            ");

        $this->db->from('comment');

        $this->db->from('guitar');

        $this->db->from('brand');

        $this->db->where("brand.id = guitar.brand_id");

        $this->db->where("comment.guitar_id = guitar.id");

        $this->db->group_by("comment.guitar_id");

        $this->db->order_by('how_many_comments DESC, guitar_brand_name ASC');

        $this->db->limit(10);

        $data['query'] = $this->db->get();        

        $this->load->view('home_view', $data);

which does the same job as

Code:
$sql_right_column = "

        SELECT 

        count(*) as how_many_comments, 

        comment.id as comment_id, 

        brand.name as guitar_brand_name, 

        guitar.model as guitar_model_name, 

        guitar.year as guitar_year,

        guitar.id as guitar_id

        FROM 

        comment,

        guitar,

        brand

        WHERE 

        guitar.id = comment.guitar_id AND

        brand.id = guitar.brand_id

        GROUP BY comment.guitar_id

        ORDER BY how_many_comments DESC, guitar_brand_name ASC

        LIMIT 10;";

        $this->db->query("SET NAMES 'utf8'"); 

        $data['query_right_column'] = $this->db->query($sql_right_column);

For such queries, what is the best practice : use Active Record ? Why ?
Thanks for your advices.
Olivier.

Mutiple SQL count() from several tables - El Forum - 03-25-2008

[eluser]GSV Sleeper Service[/eluser]
you can use count(1) instead of count(*) for a slight perfomance boost.
it's probably worthwhile sticking EXPLAIN in front of your query to see where the bottleneck is.