php - 如何在 Codeigniter 中的 from 语句中嵌套联接

Question

好吧，我的任务是在 Codeigniter 中重写一个小型的旧 PHP 应用程序，但我遇到了一些障碍。我不确定如何处理查询中的联接。

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

然后在 FROM 之后还有一些连接，我认为这相当容易。

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

任何帮助将不胜感激。

更新:

我决定将嵌套连接放入 for 语句中，然后继续。这是原始查询。

$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!");

这是新的查询:

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

该查询的输出:

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

借助 CI 的事件记录，您可以使用以下格式轻松连接表

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

有关更多信息，请查看CI's active record documentation

您还可以通过向 join() 函数添加第三个参数来指定连接类型

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