COUNT / GROUP BY with active record?

Question

I have a table with the following info:

id  |  user_id  |  points -------------------------- 1   |  12       |  48 2   |  15       |  36 3   |  18       |  22 4   |  12       |  28 5   |  15       |  59 6   |  12       |  31  etc. 

What I want is a top 10 (array) with most entries per user_id (order high to low). So using the table above I need the following array in return:

• 12 => 3 rows
• 15 => 2 rows
• 18 => 1 row
• etc.

How can I do this with CodeIgniter using the active record query method? Can this be done with COUNT and GROUP BY user_id?

Answer 1

I believe you'll want something like this:

 $this->db->select('user_id, COUNT(user_id) as total');  $this->db->group_by('user_id');   $this->db->order_by('total', 'desc');   $this->db->get('tablename', 10); 

This will produce a result like

|  USER_ID |  TOTAL  | |    12    |    3    | |    15    |    2    | |    18    |    1    | 

UPDATE: As some pointed out in the comments the original query was summing the user_ids rather than counting them. I've updated the active record query to correct this.

Answer 2

Although it is a late answer, I would say this will help you...

$query = $this->db               ->select('user_id, count(user_id) AS num_of_time')               ->group_by('user_id')               ->order_by('num_of_time', 'desc')               ->get('tablename', 10); print_r($query->result());