日期顺序按 DESC 和'header_id '分组顺序在代码中点火器查询

Question

我需要一个查询，receive_date降序也有header_id从表消息分组。其中连接message_headers表status = 0。

$this->db->select('me.*, mh.*,max(me.receive_date) as receive_date');
$this->db->from('messages as me');
$this->db->join('message_headers as mh', 'em.header_id = eh.id');
$this->db->where('me.account_id',1); 
$this->db->where('mh.status', 0);
$this->db->order_by('receive_date', 'DESC');
$this->db->group_by('me.header_id');
$message = $this->db->get()->result_array();

输出不显示desc顺序。 有些崩溃了。 一些正确的方式。

Answer 1

$this->db->select('me.*, mh.*,max(me.receive_date) as receive_date');
$this->db->from('messages as me');
$this->db->join('message_headers as mh', 'me.header_id = mh.id');
$this->db->where('me.account_id',1); 
$this->db->where('mh.status', 0);
$this->db->group_by('me.header_id');
$this->db->order_by('receive_date', 'DESC');
$message = $this->db->get()->result_array();

Answer 2

使用STR_TO_DATE作为日期格式。

$this->db->select('me.*, mh.*, MAX(STR_TO_DATE(me.receive_date, '%Y-%m-%d %h:%i:%s')) as receive_date');
$this->db->from('messages as me');
$this->db->join('message_headers as mh', 'em.header_id = eh.id');
$this->db->where('me.account_id',1); 
$this->db->where('mh.status', 0);
$this->db->order_by('receive_date', 'DESC');
$this->db->group_by('me.header_id');
$message = $this->db->get()->result_array();