group_by and order_by with codeigniter

本文关键字：by codeigniter with and group order | 更新日期: 2023-09-27

我正试图弄清楚这一点，但一点运气都没有。我一直得到一些奇怪的结果！首先，我有一个MySQL数据库，其中有一些行，结构如下，

IDMESSAGE_ID消息时间日期

现在，message_id可以多次相同，这就是我的问题开始的地方。我想选择复制消息的最后一行_ID按ID排序。

所以，假设我有一个正常的get请求，结果看起来像这样，

Array
(
    [0] => Array
        (
            [id] => 3
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => last
            [time_sent] => 1331874924
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )
    [1] => Array
        (
            [id] => 2
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => middle
            [time_sent] => 1331874920
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )
    [2] => Array
        (
            [id] => 1
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => first
            [time_sent] => 1331874916
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )
    [3] => Array
        (
            [id] => 4
            [message_id] => 2
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => test
            [time_sent] => 1331874916
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )
)

现在，我想要的是删除重复的message_id，只得到这个（注意它应该如何按id排序）

Array
(
    [0] => Array
        (
            [id] => 3
            [message_id] => 1
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => last
            [time_sent] => 1331874924
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )
    [1] => Array
        (
            [id] => 4
            [message_id] => 2
            [from_user_id] => 3
            [to_user_id] => 1
            [message] => test
            [time_sent] => 1331874916
            [date_sent] => 16/03/2012
            [opened] => 0
            [ip] => ::1
            [deleted] => 0
            [reported] => 0
        )
)

我以前一直在尝试做的是，

$this->db->order_by(‘id’, ‘DESC’);
$this->db->group_by(‘message_id’);

和

$this->db->group_by(‘message_id’);
$this->db->order_by(‘id’, ‘DESC’);

但我没有从message_id中得到最新的结果，只有第一个（看起来在删除之前没有soring？）

任何帮助都会很棒！

我在我的表上成功地做到了这一点：

select * from 
    (select * from da_user_messages order by id desc) t 
where receiver=1 group by msg_session

在您的例子中，msgsession是message_id，receiver是touser_id，dausermessages是您的表名。