[FIXED] Codeigniter linking queries

Issue

I am sorry if this is a really n00b question but i just cant seem to get my head around this problem.

I am using the mahana message library and I am trying to create some basic views and the basic controller to send a message and retrieve messages. I have sending a message nailed (just about) and I can view the sent message on the recipients profile.

This is my problem. I want to be able to show the details of the user that sent the message along side the sent message on the recipients view message page. These details are stored in the users table.

The question is, how do I run a model query to get the username, avatar location etc from the view_my_messages function in my messages controller, based on the result of the get_all_threads($user_id) model in the messaging model?

I have included the code from the controller, view and model below.

Controller:

function view_my_messages(){
        $this->load->library('mahana_messaging');
        $user_id = $this->session->userdata('id');

        $data['message'] = $this->mahana_model->get_all_threads($user_id);

        $this->load->view('messages/my_messages',$data);

    }

Message Model:

function get_all_threads($user_id, $full_thread = FALSE, $order_by = 'asc')
    {
        $sql = 'SELECT m.*, s.status, t.subject, '.USER_TABLE_USERNAME .
        ' FROM ' . $this->db->dbprefix . 'msg_participants p ' .
        ' JOIN ' . $this->db->dbprefix . 'msg_threads t ON (t.id = p.thread_id) ' .
        ' JOIN ' . $this->db->dbprefix . 'msg_messages m ON (m.thread_id = t.id) ' .
        ' JOIN ' . $this->db->dbprefix . USER_TABLE_TABLENAME . ' ON (' . USER_TABLE_TABLENAME .'.'. USER_TABLE_ID . ' = m.sender_id) '.
        ' JOIN ' . $this->db->dbprefix . 'msg_status s ON (s.message_id = m.id AND s.user_id = ? ) ' .
        ' WHERE p.user_id = ? ' ;

        if (!$full_thread)
        {
            $sql .= ' AND m.cdate >= p.cdate';
        }

        $sql .= ' ORDER BY t.id ' . $order_by. ', m.cdate '. $order_by;

        $query = $this->db->query($sql, array($user_id, $user_id));

        return $query->result_array();
    }

View:

<div class="row-fluid">

    <div class="span12">
        <div class="well">
            <h3>View Messages</h3>
            <?php foreach($message as $messagefield):?>

            <h4><?php echo $messagefield['subject'];?></h4>

            <?php endforeach; ?>
        </div>
    </div>
 </div>

Solution

@Morph_ByteSense - you have two options:

1) msg_participants p is the table that holds all the other people on the thread (not just the sender), you you can join users table once more to that & extend your SELECT stmt

2) ( not as good, but you don't need to extend the library) in your result, you can do a foreach() loop and query over all the participants & query details

I would recommend the first option - I've never had anyone ask for this before, but it might make a good enhancement

Answered By - jmadsen