Using active record to maintain an efficient mailbox message thread system

[eluser]Dandy_andy[/eluser]
Ok, I'm stuck. I am using Codeigniter to design a simple mail system. The DB records which user is sending a message to another user so that messages can be stored as threads and recalled. The table I have is similar to:-

Code:

ID   from     to       message                                         date   read

1   1001   1045    This is a message from 1001 to 1045                 (date) 0
2   1045   1001    This is a response from 1045 to 1001                (date) 0
3   1001   1045    This is a message back from 1001 to 1045            (date) 0
4   1045   1001    This is another response from 1045                  (date) 0
5   1001   1999    This is from 1001 but to another user               (date) 0
6   1001   4300    And yet again another user                          (date) 0
7   4300   1001    A response to 1001                                  (date) 0
8   1200   2000    Another message from new user                       (date) 0

Ignoring the 'date', 'read' and 'ID' columns for the moment which should be self explanatory, I am looking for the most efficient way to use the active record class to return the following...

Only the most recent thread from either from or to (if both exist for the same user) so that I can create a message thread summary for a particular user. In other words, assuming I'm user 1001, the returned results would be similar to:-

Click here for message between 1001 and 1045
Click here for Message between 1001 and 1999
Click here for Message between 1001 and 4300

I dont' want the entire thread return for each message, I just want one result per thread (the most recent) so that I can create a summary link.

So far, my model is just extracting everything for a particular user:-

Code:

public function check_thread_exists($mem_id) {

  $this->db->select('from, to, date, read');
  $this->db->from('internal_mail');
  $this->db->where('from', $mem_id);
  $this->db->or_where('to', $mem_id);
  
  $query = $this->db->get();
  
  if ($query->num_rows() > 0)
      {
         return $query->result_array(); //return all row results
      }
   else
   {
   return false;
   }

}//check_thread_exists

Now I'm stuck though as I can't figure out the logic and I'm not sure if there is a clever way to do this with Codeigniters functionality?

Can anyone offer any advice?

[eluser]Aken[/eluser]
What I would do is add a thread_id column, and assign each thread a unique ID. Then you can just SELECT DISTINCT based on that ID (or something along those lines, I'd have to put it together to say for sure)

[eluser]Dandy_andy[/eluser]
I was thinking of trying that method, but was hoping to avoid the need to do it as it adds extra complication when inserting and deleting threads. I'll play around with it though and see if makes things any easier, thanks.

[eluser]Aken[/eluser]
What kind of extra complication? All you need to do is generate some sort of unique ID before inserting the first message of a thread. After that, you should already have the thread_id when someone is sending a reply message, and deleting is easy because you can then search by message ID or thread ID.

The problem with the way it's set up now is that you'd still have to do some PHP logic to filter out additional unneeded results. You could do a SELECT DISTINCT on the to and from columns, which would give you every unique combination of the two. But you'd then need to go through the results and see if any of the to/from columns are the same people, just reversed. And even in that situation, how do you know that the messages are not related to each other, and should be different threads?

Maybe there's another way that I don't know about, but I would add the thread ID.

[eluser]Dandy_andy[/eluser]
Hi, I'm trying to use the SELECT DISTINCT but how can only select distinct thread_id values that relate to particular member ID numbers?