php - 将我的两次 mysql 调用减少为一次

Question

使用 codeigniter (php)，我想转到一个名为任务的表，获取表中的最新结果，并将该行的数据添加到传递给 View 的数组中。我有一个丑陋的黑客来完成这项工作(如下所示)。基本上，我会向表询问最近的日期，然后使用它再次查询表以获取我想要的数据。理想情况下，这应该是对数据库的一次调用。

理想情况下，我希望能够从表行中检索所有内容，而不是像我在这里那样只检索一列 - 在模型中足够简单，但让我困惑的是如何将其集成到 Controller 中，以便返回的数组与发送到 View 的数组集成

Controller :

public function index()
{   
    //Load models
    $this->load->model('project_model');
    $this->load->model('task_model');

    $flasks = $this->task_model->get_user_tasks_two($this->session->userdata('user'));

    foreach($flasks as $key => $value){
        //get last task
        $task2 = $this->task_model->get_last_task_date($value['project_id']);

        $task3 = $this->task_model->get_last_task_details($task2 , $value['project_id']);
        $flasks[$key]['flask_type'] = $task3;

    }
    $data['tasks'] = $flasks;

    // Load View
    $this->load->view('dashboard', $data);

}

型号:

public function get_user_tasks_two($user)
{
    $this->db->select(' project.id as project_id, project.name as project_name, project.archive as project_archive');
    $this->db->from('project');
    $this->db->join('user_project', 'project.id = user_project.project AND user_project.user = '.$user.'');
    $this->db->order_by('id', 'asc');
    $get = $this->db->get();

    if($get->num_rows > 0) return $get->result_array();
    return array();
}

public function get_last_task_date($id)
{
    $this->db->select_max('date_created');
    $this->db->from('task');
    $this->db->where('project_id', $id);

    $get = $this->db->get();
    $ret = $get->row();
    return $ret->date_created;
}

public function get_last_task_details($date, $id)
{
    $this->db->select('user_name');  
    $this->db->from('task');
    $this->db->where('date_created', $date);
    $this->db->where('project_id', $id);

    $get = $this->db->get();
    $ret = $get->row();
    return $ret->user_name;
}

Answer 1

首先在 phpmyadmin/mysql 工具中尝试此查询

select 
        p.id as project_id, 
        p.name as project_name, 
        p.archive as project_archive,
            t.task_name, 
            t.user_name,
            t.date_created
from 
        project p 
join 
            task t 
on 
           p.id=t.project_id and    
           t.id> (
               select id from tasks  where t.project_id = p.id order by id DESC LIMIT 1
               )