Group by Query help from multiple tables

Hello,

I need help in a query from multiple tables.

Here are the table names:-

products 
images (for product's images)
users

1. I need the query to return result as 4 products per user but maximum 3 users.
   
2. and calculate the total number of products per user.
   

As show in the picture:-



Here is the query I have been trying:-

Code:

public function random_three_sellers(){
   $this->db->select(
     'p.id as p_id, p.slug as p_slug, p.title as p_title, p.user_id as p_uid, p.status as p_status,
     u.id as u_id, u.username as u_name, u.slug as u_slug, u.email_status as u_estatus, u.banned as u_ban,
     i.product_id as i_pid, i.image_small as i_ism
     ');
   $this->db->where('p.status', 1);
   $this->db->from('products p');
   $this->db->limit(12);
   $this->db->join('images i','i.product_id = p.id', 'left');
   $user_array = array('u.banned' => 0 , 'u.email_status' => 1);
   $this->db->where($user_array);
   $this->db->join('users u','u.id = p.user_id', 'left');
   $this->db->group_by('p_uid');
   $query = $this->db->get();
   // var_dump($query);
   // die();

   if ($query->num_rows() > 0) {
     return $query->result_array();
   }else{
     return false;
   }
 }

 
3. Is it possible to add total number of followers per user. This data is in another table named "followers" , the column is "following_id"

4. Finally, how to show them in the view's loop?

Please ask me if any more info is required for the solution.


Regards.

@jTam,

if you give me the table definitions (and test data for each of the 4 tables-products, images,users and followers) then I can use http://sqlfiddle.com/ to help you figure it out.

Hello php_rocs,

Thank you for reply.

The tables are connected by these columns:

products (id) -> images (product_id)

products (user_id) -> users (id)

users (id) -> followers (following_id)



Edit:
uploaded test data "sql" file .. I can not see the uploaded file. So sharing link from googledrive.

https://drive.google.com/open?id=1tuctBn...pdViMWcLPm