issues in table alias in codeigniter

Question

Here, I am creating an alias for the inner selection, but it automatically adds the table prefix to the alias table. My sql query is as follows:

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

i am working on codeigniter framework.

---

in codeigniter model just like this

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

here i didn't give any prefix in third join tb4 but CI Add automatically when the running time.

Answer 1

Why you using query strings? If you using coeignoter why dont you use active record. Active Record queries are automatically protected with sql injection. you can do kind of this....its not same you want just idea how you can use active record sql.

$query = $this->db->select('*',FALSE)
                  ->from('tb1','tb2'.....)
                  ->join('tb2', 'tb1.cms_project_type = tb2.cms_project_type','left')
                  ->join('tb3', 'tb1.cms_project_type = tb2.cms_project_type','left')
                  ->group_by('projid')  
                  ->order_by('work_date','DESC') 
                  ->get();

https://ellislab.com/codeigniter/user-guide/database/queries.html

Hope this help.

Answer 2

Please check the code order in CodeIgniter Documentation.

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

This is the correct order. Changing the order sometimes causes issues in complex queries.

Visit this link https://github.com/bcit-ci/CodeIgniter/issues/2975