如何在Codeigniter中的from语句中嵌套连接

Question

Okay, I've been tasked with rewriting a small, old PHP app in Codeigniter, and I ran into a bit a road bump. I'm not sure how to go about handling the joins in the query.

FROM(
    (
        (
            (
                (mobiledoc.labdata labdata JOIN mobiledoc.items items 
                   ON (labdata.ItemId = items.itemID)
                )  JOIN mobiledoc.enc enc 
                   ON (labdata.EncounterId = enc.encounterID)
            ) JOIN mobiledoc.users users_patient 
              ON (users_patient.uid = enc.patientID)
        ) JOIN mobiledoc.users users_Provider 
          ON (users_Provider.uid = enc.doctorID)
    ) JOIN mobiledoc.facilitygroupmembers facilitygroupmembers 
      ON (enc.facilityId = facilitygroupmembers.FacilityId)
    )

And then after that FROM there are a few more joins, which I think would be fairly easy.

JOIN mobiledoc.facilitygroups facilitygroups ON (facilitygroups.Id = acilitygroupmembers.GroupId) 
JOIN mobiledoc.patients patients ON (enc.patientID = patients.pid)

Any help would be greatly appreciated.

UPDATE:

I decided to just put the nested joins inside the for statement, and move on. Here's the original query.

$result = mysql_query("SELECT patients.ControlNO AS PatientID, users_patient.ulname AS patulname,
users_patient.ufname AS patufname, users_patient.uminitial AS patuminitial, users_patient.dob AS 
patdob, users_Provider.ulname AS ULname, users_Provider.ufname AS UFname, items.itemName, 
labdata.Notes,enc.date, enc.startTime FROM(((((mobiledoc.labdata labdata JOIN mobiledoc.items 
items ON (labdata.ItemId = items.itemID)) JOIN mobiledoc.enc enc ON (labdata.EncounterId = 
enc.encounterID)) JOIN mobiledoc.users users_patient ON (users_patient.uid = enc.patientID)) JOIN 
mobiledoc.users users_Provider ON (users_Provider.uid = enc.doctorID)) JOIN 
mobiledoc.facilitygroupmembers facilitygroupmembers ON (enc.facilityId = 
facilitygroupmembers.FacilityId)) JOIN mobiledoc.facilitygroups facilitygroups ON 
(facilitygroups.Id = facilitygroupmembers.GroupId) JOIN mobiledoc.patients patients ON 
(enc.patientID = patients.pid) WHERE (facilitygroups.Name = '". $_POST['facility_id'] . "') AND 
(items.itemName LIKE '%X RAY%' OR items.itemName LIKE '%Cast%' OR items.itemName LIKE '%Splint%' 
OR items.itemName LIKE '%DEXA%') AND (enc.VisitType NOT IN ('', 'MT', 'TEL')) AND (enc.`date` = 
'" . $_POST['txtYear'] . "-" . $_POST['txtMonth'] . "-" . $_POST['txtDay'] ."') ORDER BY 
enc.startTime ASC") or die ("could not execute query!");

Here's the new query:

$this->db->select('patients.ControlNO as id');
$this->db->select('users_patient.ulname as lastName');
$this->db->select('users_patient.ufname as firstName');
$this->db->select('users_patient.uminitial as mInitial');
$this->db->select('users_patient.dob as dob');
$this->db->select('users_Provider.ulname as phys_lastName');
$this->db->select('items.itemName');
$this->db->select('labdata.notes');
$this->db->select('enc.date');
$this->db->select('enc.startTime');
$this->db->from('((((mobiledoc.labdata labdata JOIN mobiledoc.items items ON (labdata.ItemId 
= items.itemID)) JOIN mobiledoc.enc enc ON (labdata.EncounterId = enc.encounterID)) JOIN 
mobiledoc.users users_patient ON (users_patient.uid = enc.patientID)) JOIN mobiledoc.users 
users_Provider ON (users_Provider.uid = enc.doctorID)) JOIN mobiledoc.facilitygroupmembers 
facilitygroupmembers ON (enc.facilityId = facilitygroupmembers.FacilityId)) JOIN 
mobiledoc.facilitygroups facilitygroups ON (facilitygroups.Id = facilitygroupmembers.GroupId) 
JOIN mobiledoc.patients patients ON (enc.patientID = patients.pid)');
$this->db->where($where_array);
$this->db->like($like_array);
$this->db->or_like($or_like_array);
$this->db->where_not_in('enc.VisitType', $not_in);
$this->db->order_by('enc.startTime', 'asc');
$this->db->get();

And the output from that query:

SELECT `patients`.`ControlNO` as id, `users_patient`.`ulname` as lastName, 
`users_patient`.`ufname` as firstName, `users_patient`.`uminitial` as mInitial, 
`users_patient`.`dob` as dob, `users_Provider`.`ulname` as phys_lastName, `items`.`itemName`, 
`labdata`.`notes`, `enc`.`date`, `enc`.`startTime` FROM ((((((mobiledoc.labdata labdata JOIN 
mobiledoc.items items ON (labdata.ItemId = items.itemID)) JOIN mobiledoc.enc enc ON 
(labdata.EncounterId = enc.encounterID)) JOIN mobiledoc.users users_patient ON (users_patient.uid 
= enc.patientID)) JOIN mobiledoc.users users_Provider ON (users_Provider.uid = enc.doctorID)) 
JOIN mobiledoc.facilitygroupmembers facilitygroupmembers ON (enc.facilityId = 
facilitygroupmembers.FacilityId)) JOIN mobiledoc.facilitygroups facilitygroups ON 
(facilitygroups.Id = facilitygroupmembers.GroupId) JOIN mobiledoc.patients patients ON 
(enc.patientID = patients.pid)) WHERE `facilitygroups`.`Name` = 0 AND `enc`.`date` = '12/05/2014' 
AND `enc`.`VisitType` NOT IN ('', 'MT', 'TEL') AND `items`.`itemName` LIKE '%X RAY%' OR 
`items`.`itemName` LIKE '%DEXA%' OR `0` LIKE '%items.itemName%' ORDER BY `enc`.`startTime` asc

Answer 1

With CI's active record you can easily join tables by using the following format

$qry = $this->db->select('*')
                ->from('table1')
                ->where('table1.id', 1)
                ->join('table2','table2.t_id = table1.id')
                ->get();

For more information take a look at CI's active record documentation

Also you can specify the join type by adding a third parameter to the join() function

$this->db->join('table3', 'table3.id = table2.t_id', 'left');