codeigniter using case statement for joins

Question

Here is my query for codeigniter model

$this->db->select('wo.*,
            en.equipment_name as conventional_name,
            cat.name as category_name,
            toe.name as type_of_equipment_text,
            wos.wo_status,
            l.name as location_name, spl.name as supplier_name,
            ht.shortcode as type_code, c.name as city_name, 
            dt.name as district_name, d.name as division_name, z.name as zone_name')
             ->from('work_orders wo')
             ->join(DB_PREFIX . 'equipment e', 'e.ID = wo.tag_num', 'left')
             ->join(DB_PREFIX . 'equipment_names en', 'en.ID = e.conventional_name', 'left')
             ->join(DB_PREFIX . 'categories cat', 'cat.ID = en.category_id', 'left')
             ->join(DB_PREFIX . 'equipment_type toe', 'toe.ID = e.type_of_equipment', 'left')
             ->join(DB_PREFIX . 'suppliers spl', 'spl.ID = e.purchased_from_supplier_name AND spl.status=1', 'left')
             ->join(DB_PREFIX . 'locations l', 'l.ID = (CASE WHEN l.ID == e.city_id THEN e.city_id ELSE wo.location)', 'left')
             ->join(DB_PREFIX . 'hospital_type ht', 'ht.ID = l.type_id', 'left')
             ->join(DB_PREFIX . 'cities c', 'c.ID = l.city_id', 'left')
             ->join(DB_PREFIX . 'districts dt', 'dt.ID = c.district_id', 'left')
             ->join(DB_PREFIX . 'divisions d', 'd.ID = dt.division_id', 'left')          
             ->join('work_orders_status wos', 'wos.id = wo.wo_status', 'left')
             ->join(DB_PREFIX . 'zones z', 'z.ID = d.zone_id', 'left');

As you can see in the join statement for locations table I'm trying to implement a case statement and its not working for me. What I want is if there is any integer value in e.city_id then the join statement for locations table should become

->join(DB_PREFIX . 'locations l', 'l.ID = e.city_id', 'left')

otherwise it should become

->join(DB_PREFIX . 'locations l', 'l.ID = wo.location', 'left')

Any help in this approach or alternate way of doing this is much appreciated. thanks for reading

Edits Here is the error I am getting

What do you mean by "its not working for me"? Does it give an error? Is the output not as expected? If so, show what it gives and (possibly) what it should show instead. — wallyk, Oct 17 '19 at 20:27
@wallyk I have edited the question and attached the error snapshot. Now you can review it — Muhammad Asif Raza, Oct 17 '19 at 20:32

Vickel · Answer 1 · 2019-10-17T22:23:19.667

0

The error message itself gives you a hint:

syntax to use near '== e.city_id

The mySQL comparison operator is =, therefore case when l.ID == e.city_id should be case when l.ID = e.city_id

see MySQL Comparison Functions and Operators

edited Oct 17 '19 at 22:23

answered Oct 17 '19 at 22:11

Vickel

7,879
6
35
56

score 0 · Accepted Answer · answered Oct 18 '19 at 17:07

I don't think its smart way but it works for me

$this->db->select('wo.*,
            en.equipment_name as conventional_name,
            cat.name as category_name,
            toe.name as type_of_equipment_text,
            wos.wo_status,
            l.name as location_name, spl.name as supplier_name,
            ht.shortcode as type_code, c.name as city_name, 
            dt.name as district_name, d.name as division_name, z.name as zone_name')
             ->from('work_orders wo')
             ->join(DB_PREFIX . 'equipment e', 'e.ID = wo.tag_num', 'left')
             ->join(DB_PREFIX . 'equipment_names en', 'en.ID = e.conventional_name', 'left')
             ->join(DB_PREFIX . 'categories cat', 'cat.ID = en.category_id', 'left')
             ->join(DB_PREFIX . 'equipment_type toe', 'toe.ID = e.type_of_equipment', 'left')
             ->join(DB_PREFIX . 'suppliers spl', 'spl.ID = e.purchased_from_supplier_name AND spl.status=1', 'left')
             ->join(DB_PREFIX . 'locations l', 'l.ID = (CASE WHEN wo.tag_num in (select eq.ID from berc_equipment eq) THEN e.city_id ELSE wo.location END)', 'left')
             ->join(DB_PREFIX . 'hospital_type ht', 'ht.ID = l.type_id', 'left')
             ->join(DB_PREFIX . 'cities c', 'c.ID = l.city_id', 'left')
             ->join(DB_PREFIX . 'districts dt', 'dt.ID = c.district_id', 'left')
             ->join(DB_PREFIX . 'divisions d', 'd.ID = dt.division_id', 'left')          
             ->join('work_orders_status wos', 'wos.id = wo.wo_status', 'left')
             ->join(DB_PREFIX . 'zones z', 'z.ID = d.zone_id', 'left');

codeigniter using case statement for joins

2 Answers2