Codeigniter链接查询 [英] Codeigniter linking queries

问题描述

很抱歉，如果这是一个真正的问题，但我似乎无法解决这个问题.

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

我正在使用mahana消息库，并且试图创建一些基本视图和基本控制器来发送消息和检索消息.我已经将发送的消息钉上了钉子(正好差不多)，并且可以在收件人个人资料上查看发送的消息.

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.

问题是，如何基于消息传递模型中的get_all_threads($ user_id)模型的结果，如何运行模型查询以从消息控制器中的view_my_messages函数获取用户名，头像位置等?

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.

控制器:

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);

    }

消息模型:

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();
    }

查看:

<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>

推荐答案

@Morph_ByteSense-您有两个选择:

@Morph_ByteSense - you have two options:

1)msg_participants p是容纳线程中所有其他人(而不仅仅是发送者)的表，您可以再次将users表加入到该&中.扩展您的SELECT stmt

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)(效果不佳，但不需要扩展库)，您可以执行foreach()循环并查询所有参与者&查询详细信息

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

这篇关于Codeigniter链接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！