CodeIgniter - 按顺序分组，但未按预期工作 [英] CodeIgniter - Group by with order by not working as expected

问题描述

所以我得到了这个查询:

So I got this query:

    $CI->db->where('user_id', $CI->session->userdata('user_id'));
    $CI->db->where('amount >', 0);
    $CI->db->order_by('ur_time', 'desc');
    $CI->db->group_by('resource_id');
    $query = $CI->db->get('users_resources');
    $resources = $query->result();

我的问题是:order_by 'ur_time' 不起作用.如果我有 2 行，一个金额为 10000 和 ur_time 1384303464，另一行金额为 100 和 ur_time 1384304656，结果我仍然得到金额为 10000 的行.

My problem is: The order_by 'ur_time' doesn't really work. If I got 2 rows, one with amount 10000 and ur_time 1384303464 and another row with amount 100 and ur_time 1384304656 I still get the row with amount 10000 as result.

我做错了什么?

也许我需要解释一下.假设我有一些行:

Maybe I have to explain it a bit. Let's say I got some rows:

ur_id | ur_time     | user_id | resource_id | amount

1     | 1384304656  | 1       | 1           | 100
2     | 1384303464  | 1       | 1           | 10000
3     | 1384303464  | 1       | 2           | 200
4     | 1384304656  | 1       | 2           | 20000

我想得到的结果是:两行，ur_id 为 1 的行和 ur_id 为 4 的行，因为对于每个 resource_id，我想要具有最高 ur_time 的行.我希望现在清楚了.

What I'd like to get here as result: Two rows, the one with ur_id 1 and the one with ur_id 4 because for each resource_id I want the row with the highest ur_time. I hope it's clear now.

PS:我需要数量 > 0 子句，因为可能有负数量，但我不希望它们出现在结果中.

PS: I need the amount > 0 clause because there can be negative amounts but I don't want them in the result.

推荐答案

尝试将其添加到您的查询中，看看它们是否会合并到您的选择语句中

Try adding this in your query see if they will combine in your select statement

$CI->db->select('*');
$CI->db->select_max('ur_time' , 'max_ur_time'); // this will produce max(ur_time) as max_ur_time
$CI->db->where('user_id', $CI->session->userdata('user_id'));
$CI->db->where('amount >', 0);
$CI->db->order_by('ur_time', 'desc');
$CI->db->group_by('resource_id');
$query = $CI->db->get('users_resources');
$resources = $query->result();

这篇关于CodeIgniter - 按顺序分组，但未按预期工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！