Mysql codeigniter php asc desc order by count resu

Question

I have three tables named:

at_category:

1. cat_id
2. name

at_category_taxonomy:

1. cat_taxonomy_id
2. cat_id
3. taxonomy

at_shop:

1. shop_id
2. shop_category

I want to join those three tables by counting result.

For example the category named as Electronic Shops in at_category table and the value will be stored in at_category_taxonomy table and the this category id's are having two shops in at_shop table. same as for remaining categories aaa, bbb, ccc etc...it may be having one or two shops else zero shops.

Example :

1. at_category

    ______________

    cat_id   name

     1       Electronic Shops
     2       Ice Cream Shops
    _______________

    2. at_category_taxonomy

    _______________________________________________

    cat_taxonomy_id   cat_id   taxonomy

      3                 1       Electronic Shops
      4                 2       Ice Cream Shops
    _______________________________________________

    3. at_shop

    ________________________________

    shop_id   shop_name   shop_category

     1            A         1 (ie.Electronic Shops) 
     2            B         1 (ie.Electronic Shops) 
     3            C         1 (ie.Electronic Shops) 
     4            D         2 (ie.Ice Cream Shops) 

    ________________________________

Now : The category Electronic Shops having 3 shops and Ice cream shops having 1 shop

Expecting output :

No.Of.Shops (ASC) (Desc)    Category Name   (ASC) (Desc)

     3                         Electronic Shops
     1                         Ice cream Shops 

When i click asc order in no.of shops column then the output will be

No.Of.Shops (ASC) (Desc)    Category Name   (ASC) (Desc)

     1                          Ice cream Shops   
     3                          Electronic Shops

This is vise verse for category name too.

now I want to display the result by count the number shops by desc order using codeigniter.

Answer 1

Not sure whether this is the best practice. Also may be not good if you have a lot of data in DB. But I would like to suggest.

1.Get the category IDs into one array.

2.Join tables as follows, for each category. (Using a foreach loop for the array)

$this->db->select('*');    
$this->db->from('at_category');
$this->db->join('at_category_taxonomy', 'at_category.cat_id = at_category_taxonomy.cat_id');
$this->db->join('at_shop', 'at_category.cat_id = at_shop.shop_category');
$this->db->where('at_category', 1);
$query = $this->db->get();

in the above code, $this->db->where('at_category', get ID from array);

3.$query is the result for each category.

4.Number of shops for each category can be taken as,

$numberOfShos = $query->num_rows();

Answer 2

select email, count(*) as c FROM orders GROUP BY email

$this->db->select('COUNT(sh.shop_category) as No_of_shops,cat.name as category_name',FALSE);
$this->db->from('at_shop sh');
$this->db->join('at_category cat', 'cat.cat_id = sh.shop_category');
$this->db->order_by('sh.shop_category');
$query = $this->db->get();
retuen $query->result_array();