mysql - codeigniter 表别名中的问题

Question

在这里，我正在为内部选择创建一个别名，但它会自动将表前缀添加到别名表中。我的sql查询如下:

SELECT
    `tb1`.*, `tb2`.*, `tb3`.`client_name`,
    `cms_tb4`.*,  `tb1`.`project_status` AS pstat
FROM
    (`cms_projects` tb1)
JOIN  `cms_project_type` tb2 ON `tb2`.`id` = `tb1`.`project_type`
JOIN  `cms_clients` tb3 ON `tb1`.`client_id` = `tb3`.`client_id`
JOIN  (
    SELECT
        *
    FROM
        (
            SELECT
                t1.project_id AS projid,
                 t2.work_id,
                t2.work_date
            FROM
                cms_projects_mod t1,
                cms_work_status_emp  t2
            WHERE
                t1.module_id = t2.pro_module_id
            ORDER BY
                t2.work_date DESC
        )  AS subtb4
    GROUP BY
        projid
    ORDER BY
        work_date DESC
) AS tb4 ON  `cms_tb4`.`projid` = `tb1`.`project_id`
LIMIT 50

我正在研究 codeigniter 框架。

---

在 codeigniter 模型中就像这样

         $this->db->select('tb1.*');
                $this->db->select('tb1.project_status as pstat');
                $this->db->from('cms_projects tb1');
               $this->db->join('project_type tb2', 'tb2.id=tb1.project_type');
             $this->db->join('clients tb3', 'tb1.client_id = tb3.client_id');
            $this->db->join("(
                       select * from (
              select t1.project_id as projid,                                     t2.work_id,t2.work_date from cms_projects_mod t1, cms_work_status_emp t2  where  t1.module_id = t2.pro_module_id order by  t2.work_date desc) as subtb4 
 group by  projid  order by  work_date desc ) as tb4 ",
"tb4.projid = tb1.project_id" ,false);
      $this->db->group_by("tb1.project_id");
            $this->db->order_by("tb3.work_date",'desc');

这里我没有在第三个连接tb4中给出任何前缀但是CI在运行时自动添加。

Answer 1

请检查 CodeIgniter 文档中的代码顺序。

$this->db
   ->select()
   ->from()
   ->join()

这是正确的顺序。更改顺序有时会导致复杂查询出现问题。

访问此链接 https://github.com/bcit-ci/CodeIgniter/issues/2975