有活动记录的COUNT / GROUP BY？

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:

我想要的是前10名（数组），每个user_id有大多数条目（从高到低的顺序）。所以使用上面的表我需要以下数组作为回报：

12 => 3 rows
12 => 3行
15 => 2 rows
15 => 2行
18 => 1 row
18 => 1行
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?

如何使用活动记录查询方法使用CodeIgniter执行此操作？可以使用COUNT和GROUP BY user_id完成吗？

5 个解决方案

#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    |

In the past I've always found it difficult to know exactly what CodeIgniter is doing with my active record chains. Especially when the chains get somewhat complex. Your post inspired me to create a simple tool for displaying the actual SQL statement.

在过去，我总是发现很难确切地知道CodeIgniter对我的活跃记录链做了什么。特别是当链条变得有些复杂时。您的帖子激发了我创建一个显示实际SQL语句的简单工具。

To use the tool just paste in your active record and click the Submit button. It should return the corresponding SQL statement. Note: I whipped this up pretty quickly and haven't tested it thoroughly. It's not hooked up to a db so some types of queries might result in errors.

要使用该工具，只需粘贴您的活动记录，然后单击“提交”按钮。它应该返回相应的SQL语句。注意：我很快就把它掀起了，并没有彻底测试过。它没有连接到数据库，因此某些类型的查询可能会导致错误。

Active Record preview tool here.

这里有Active Record预览工具。

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.

更新：正如一些人在评论中指出的那样，原始查询是对user_ids求和而不是计算它们。我已更新活动记录查询以更正此问题。

#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.

我认为你应该用FOUND_ROWS（）和SQL_CALC_FOUND_ROWS计算结果。您将需要两个查询：select，group_by等。您将添加一个加号选择：SQL_CALC_FOUND_ROWS user_id。在此查询之后运行查询：SELECT FOUND_ROWS（）。这将返回所需的数字。

#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.

其中'arvdate'和'dptrdate'是数据库上的两个日期，'reservation'是表名。

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

#1