Codeigniter 显示用户的统计信息
我正在一个社区工作,我想在用户个人资料中显示大量用户统计信息。
最好的做法是什么?
我现在有了这个,但我不认为这是最好的方法,所以我会很高兴你能解释我可以做什么来在我的查询中加载大量统计信息:
//Get the stats of a user in hes profile
function getState($username) {
//it has false so it dont try to escape the COUNT()
$this->db->select('username, firstname, lastname, freetext, imgPath, city, hood, online, level,
COUNT(forumThread.id) as totalThreads,
COUNT(forumComments.id) as totalComments
', FALSE);
$this->db->join('forumThread', 'forumThread.creater = users.username');
$this->db->join('forumComments');
$this->db->group_by('users.id');
$this->db->where('username', $username);
$statQuery = $this->db->get('users');
return $statQuery->row();
}
I'm working on a community and I want to show a lot of user stats in the user profile.
What is the best practice to do?.
I have this now, but I don't think its the best way of doing it, so I will be really glad of you can explain me what I can do to load a lot of stats in my query:
//Get the stats of a user in hes profile
function getState($username) {
//it has false so it dont try to escape the COUNT()
$this->db->select('username, firstname, lastname, freetext, imgPath, city, hood, online, level,
COUNT(forumThread.id) as totalThreads,
COUNT(forumComments.id) as totalComments
', FALSE);
$this->db->join('forumThread', 'forumThread.creater = users.username');
$this->db->join('forumComments');
$this->db->group_by('users.id');
$this->db->where('username', $username);
$statQuery = $this->db->get('users');
return $statQuery->row();
}
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
更多
发布评论
评论(1)
任何时候开始连接表时,您都会面临查询速度相当慢的风险(随着表的增长)。
一种选择是缓存结果,这样您就不必在每次加载页面时都运行查询。 (我认为 CI 确实为您提供了查询缓存能力)
另一种选择是在用户表中添加另一个字段,其中包含论坛主题的数量以及他们发表的论坛评论的数量。当您将新条目插入数据库时,该计数器也会增加。因此,当在统计页面中加载所需的数据时,您只需查询 1 个表。
第二个选项显然会给您带来稍大的开销,即在输入其他数据时运行另一个查询,并在删除数据时递减。和/或创建维护脚本,以确保用户表中的值实际上是准确的(并在后台定期运行)。
无论如何,我觉得我现在只是在闲逛。我希望有些人认为这是有道理的!
Any time you start joining tables, you run the risk of making your query fairly slow (as the tables grow).
One option would be to cache the results so you don't have to run the query every time the page is loaded. (I think CI does give you query caching abilities)
Another option would be to have another field in the users table, which has the number of forum threads and the number of forum comments they have made. When you insert a new entry into the database, you increment this counter as well. So when loading the data you need in the stats page, you're just querying the 1 table.
The 2nd option does give you obviously a slightly bigger overhead, of running another query when you enter the other data, and decrementing when you delete one. And/Or creating maintenance script which makes sure that the values in the users table are actually accurate (and runs periodically in the background).
Anyway, I feel like I'm just rambling now. I hope some if that made sense!