COUNT / GROUP BY与活动记录？ [英] 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.

我要的是一个前10名（阵列），每个USER_ID大多数条目（为了由高到低）。 因此，使用上面的表格我需要回报以下数组：

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行
• 15 => 2行
• 18 => 1行
• 等

我怎样才能做到这一点与codeIgniter使用活动记录查询方法？可这与COUNT和GROUP做BY user_ID的？

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

推荐答案

我相信你会想是这样的：

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

这会产生这样的结果。

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

在过去我总是发现它很难知道到底是什么codeIgniter是干嘛用我的活动记录链。特别是当链变得有些复杂。您的文章启发了我，显示实际的SQL语句创建一个简单的工具。

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.

要只使用粘贴工具，在你的活动记录，然后点击提交按钮。它的应的返回相应的SQL语句。 注意：我迅速刮起这件事pretty的，并没有彻底测试它。这不是迷上了一个数据库，以便某些类型的查询可能会导致错误。

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.

Active Record的preVIEW工具 href="http://brettdewoody.com/labs/active-check/index.php">。

Active Record preview tool here.

更新：作为部分指出在评论中最初的查询是总结了user_ids，而不是指望他们。我已经更新了活动记录查询，以改正这一点。

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.

这篇关于COUNT / GROUP BY与活动记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！