Get all messages for logged in user with all answers for each message using Codeigniter

Question

I have three tables,
users => id, user_name, etc etc
messages => id, user_id, message_text etc etc
answers => id, user_id, message_id, answer_text.

I want to get all messages for logged in user along will all answers for each message. I have tried many join examples, but I always get all answers (coupled with their message data).

I need data like that

message_1 => all_answers

message_2 => all_answers

message_3 => all_answers

Answer 1

It's impossible, or too complicated, to get that result with one query only. You must extract data in stages. Using model functions would be the best. Let's say you have the id of the user. You have to get the messages, and that's easy.

$this->db->where('user_id', $user_id);
$query = $this->db->get('messages');

Now that must be put inside the model. Let't call it messages_model.

class messages_model extends CI_Model {

    function __construct(){
        parent::__construct();
    }

    function get_messages($user_id){
       $this->db->where('user_id', $user_id);
       $query = $this->db->get('messages');
       return $query->result();
    }
}

Say, you want to put those messages inside some variable, and get them to your view. In your controller:

$this->load->model('messages_model');
$rough_messages = $this->messages_model->get_messages($user_id);
foreach($rough_messages as $message){
   $messages[$message->id][$message_text] =  $message->message_text;
   $messages[$message->id]['answers'] = $this->messages_model->get_answers($message->id);
}

Then, your 'get_answers()' model function would look like this, inside messages_model(edit: I split it in two functions, one for db qwery and the other for refined results):

function get_answers($message_id){
   $rough_answers = $this->get_rough_answers($message_id);
   $answers = array();
   foreach($rough_answers as $answer){
      $answers[$answer->id] = $answer->answer_text;
   }
   return $answers;
}

function get_rough_answers($message_id){
   $this->db->where('message_id', $message_id);
   $query = $this->db->get('answers');       
   return $query->result();
}

Now, if you do echo '<pre>'.print_r($messages, true).'</pre>'; in your controller, you will get an multi-dimensional array, with your messages and corresponding answers, for a given user id. You can now alter and use this as you see fit.