Welcome Guest, Not a member yet? Register   Sign In
Specific query not working with Active Record Class and row grouping
#1

[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

#2

[eluser]CroNiX[/eluser]
Code:
order_by('datetime', 'DESC')
?
#3

[eluser]Dandy_andy[/eluser]
That doesn't do anything in this case because of the grouping. Already tried it!
#4

[eluser]CroNiX[/eluser]
select_max('timestamp')
#5

[eluser]Dandy_andy[/eluser]
Tried that too and the same result. I have got closer by using
Code:
$this->db->select_max('messages.id');
because this returns the highest id value but doesn't change any of the other array values so all it's doing is replacing the id value in the incorrect row.
#6

[eluser]Dandy_andy[/eluser]
Can't figure this one out. I'm guessing this is Codeigniter's limitation? I have managed to do what I need to do using a direct query but a bit disappointed that the Active Record Class will only do very basic queries.

Code:
$query = $this->db->query('
   SELECT x.*, y.mem_id, y.screenname, y.last_activity_date
   FROM (SELECT messages.* FROM messages ORDER BY datetime DESC) AS x
   JOIN members AS y ON x.to_mem_id = y.mem_id
   WHERE (from_mem_id = '.$this->session->userdata('mem_id').')
   GROUP BY x.to_mem_id');
$this->db->limit($limit, $pgn);
return $query->result_array();
#7

[eluser]joergy[/eluser]
The problem is, that CI does not want to reimplement a complete SQL parser for Active Records.
So only very simple statements are possible.
Anything with "(" or "'" might fail.
Another restriction is, that Active Records do not support "bindings" from MySQLi.
In Your case You better build Your SQL completely Yourself.
Don't forget to escape Your values!




Theme © iAndrew 2016 - Forum software by © MyBB