Joining tables together in a model function

[eluser]intractve[/eluser]
This to me seems like a database tables layout issue. It is common usage that any ID's or keys are in one table and related data, (if they have to be are broken up) are stored in separate tables and have the relevant ID's. The first option is to rethink this.

But you can work around your current design too... You will have to lookup each time if there are any entries for the qms_id and only then add the relevant where clause.

For Eg.

Code:

function include_criteria($task_id, $qms_id)
    {
        $this->db->where('quality_criteria.task_id', $task_id);
        if($this->check_qms($qms_id)) $this->db->where('quality_data.qms_id', $qms_id);
        $this->db->join('quality_tasks', 'quality_tasks.task_id = quality_criteria.task_id');
        $this->db->join('quality_data', 'quality_data.criteria_id = quality_criteria.criteria_id', 'right');
        $this->db->order_by('quality_criteria.criteria_order', "ASC");
        
        $query = $this->db->get('quality_criteria');
        $return['rows'] = $query->result();
        
        $this->db->where('task_id', $this->uri->segment(3, 0));
        $query = $this->db->get('quality_criteria');
        $return['num_rows'] = $query->num_rows();
        
        return $return;
    }
function check_qms($qms_id)
{
     $this->db->where('qms_id', $qms_id);
     $num_rows = $this->db->get('quality_data')->num_rows();

     //Next part is just to simplify checking in the other function or you could just return the number

     if($num_rows > 0) {
              return TRUE;
     } else {
              return FALSE;
     }

}