我有三个表,如下所示。主键高亮显示,外键以斜体显示
-
项目任务[项目任务id,项目结构id,分配任务名称]
-
项目结构[projectstructureid,projectid,structurename]
-
项目[projectid,项目名称]
我可以从会话数组中获得比较"assignto"和userID的项目任务。这样我就可以将任务分配给已登录的用户。
以Codeigniter的方式从"assignto"字段获取"projectstructure"answers"projects"的最佳方式是什么。
我想写,get_structures()和get_projects()
代码,
$user_id = $this->session->userdata('userID');
$data = array('main_content' => 'view_project_tasks_page',
'tasks' => $this->projects->get_project_tasks($user_id),
'structures' => $this->projects->get_structures(),
'projects' => $this->projects->get_projects() );
$this->load->view('template', $data);
模型-get_project_tasks($user_id)
function get_project_tasks($user_id)
{
$rules = array('assignto' => $user_id );
$this->db->select()->from('projecttasks')->where($rules);
$sql_stmt = $this->db->get();
return $sql_stmt->result();
}
您搜索的是一个简单的Right Join
在"CodeIgniter方式"中,它应该看起来像这样(未测试)
function get_structures($user_id) {
$rules = array('projecttasks.assignto' => $user_id );
$this->db->select('projectstructure.*')
->from('projectstructure')
->join('projecttasks', 'projecttasks.projectstructureid = projectstructure.projectstructureid')
->where($rules);
$sql_stmt = $this->db->get();
return $sql_stmt->result();
}
function get_projects($user_id) {
$rules = array('projecttasks.assignto' => $user_id );
$this->db->select('project.*')
->from('projectstructure')
->join('projecttasks', 'projecttasks.projectstructureid = projectstructure.projectstructureid')
->join('project', 'projecttasks.projectid = project.projectid')
->where($rules);
$sql_stmt = $this->db->get();
return $sql_stmt->result();
}