COUNT / GROUP BY with active record?

问题:

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?

回答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.

回答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());

回答3:

I think you should count the results with FOUND_ROWS() and SQL_CALC_FOUND_ROWS. You'll need two queries: select, group_by, etc. You'll add a plus select: SQL_CALC_FOUND_ROWS user_id. After this query run a query: SELECT FOUND_ROWS(). This will return the desired number.

回答4:

This code counts rows with date range:

Controller:

$this->load->model("YourModelName");
$data ['query'] = $this->YourModelName->get_report();

Model:

  public function get_report()
     {   
       $query = $this->db->query("SELECT  *
FROM   reservation WHERE arvdate <= '2016-7-20' AND  dptrdate >= '2016-10-25' ");
       return $query;
     }

where 'arvdate' and 'dptrdate' are two dates on database and 'reservation' is the table name.

View:

<?php
 echo $query->num_rows();
?>

This code is to return number of rows. To return table data, then use

$query->rows();
return $row->table_column_name;

回答5:

$this->db->select('overal_points');
$this->db->where('point_publish', 1);
$this->db->order_by('overal_points', 'desc'); 
$query = $this->db->get('company', 4)->result();