codeigniter, Using Case When for Where Query

Question

I have this query which i want to make it work in codeigniter query form, so want to know if its possible or not.

This is the query i want to implement in Codeigniter in codeigniter's own way.

    use zorkif_next;
SELECT 
    `osp_job_status_track`.`StatusID`,
    `osp_job_status_track`.`SubStatusID`,
    `osp_job_sub_status`.`CurrentStatus`
FROM
    (`osp_job_details`)
        LEFT JOIN
    `osp_job_status_track` ON `osp_job_status_track`.`JobID` = `osp_job_details`.`JobID`
        LEFT JOIN
    `osp_job_status` ON `osp_job_status`.`StatusID` = `osp_job_status_track`.`StatusID`
        LEFT JOIN
    `osp_job_sub_status` ON `osp_job_sub_status`.`SubStatusID` = `osp_job_status_track`.`SubStatusID`
        LEFT JOIN
    `hr_employee_details` ON `hr_employee_details`.`EmployeeID` = `osp_job_details`.`AssignToEmployeeID`
        LEFT JOIN
    `osp_job_type` ON `osp_job_type`.`JobTypeID` = `osp_job_details`.`JobtypeID`
WHERE
    -- `isDefault` = 1
       --    AND 

    CASE WHEN `osp_job_status`.`StatusID` = 2 
    THEN  `osp_job_sub_status`.`CurrentStatus` = 3
    ELSE `osp_job_status`.`StatusID` >= 2
    END ;

So how to implement this query in

$this->db->where($where);

???

there is no way to implement CASE in CI. CI SQL library is very weak — diEcho
Am not sure it is possible in CI. You can go and see what all are available in active rec .. Check this file - system\database\DB_active_rec.php — Jerin K Alexander
@diEcho So if its not possible in codeigniters way then how to implement it in simple way in codeigniter like $this->db->query('')?? — Sizzling Code
$sql = 'Your complete mySQL query' and then $this->db->query($sql) — diEcho

Jerin K Alexander Jerin K Alexander · Accepted Answer · 2013-05-28T05:55:29

It is not possible in CI.You need to write your own query. Please have a look on this file.You can see all the available methods there.

\system\database\DB_active_rec.php

codeigniter, Using Case When for Where Query

1 Answers