[eluser]Dandy_andy[/eluser]
I am trying to do a very specific query with the Active Record Class but can't find a way to do it. I have a db table as per the attached image which relates to a mail system in an application I am developing and it is designed to enable two users talking to each other to have their own 'copies' of messages. The specific query I am looking for should check through the table for unique member id's (to_mem_id) and return the latest row in each group related to the current logged in user so that I can find out whether that message has been read or not. The code I have is as follows and this works but it is only returning the earliest or first row within each group.
Code:
$this->db->select('messages.*, members.mem_id, members.screenname, members.last_activity_date');
$this->db->where('from_mem_id', $this->session->userdata('mem_id'));
$this->db->join('members', 'messages.to_mem_id = members.mem_id');
$this->db->group_by('messages.to_mem_id');
$this->db->limit($limit, $pgn);
$query = $this->db->get('messages');
return $query->result_array();
For example, the code above would return the following results from the table:-
Code:
Array ( [0] => Array ( [id] => 7 [to_mem_id] => 120175 [from_mem_id] => 9001 [type] => sent [thread_id] => 0 [datetime] => 2014-06-23 16:44:20 [message] => Message from 9001 to 120175 Migwaini [status] => read [mem_id] => 120175 [screenname] => Migwaini [last_activity_date] => 2014-06-23 16:51:36 ) )
What I want to see is:-
Code:
Array ( [0] => Array ( [id] => 14 [to_mem_id] => 120175 [from_mem_id] => 9001 [type] => inbox [thread_id] => 0 [datetime] => 2014-06-23 16:51:36 [message] => Hi Jody, you ok? [status] => new [mem_id] => 120175 [screenname] => Migwaini [last_activity_date] => 2014-06-23 16:51:36 ) )
Any suggestions on how I can pull the last result in a group?
Thanks in advance
