mysql - codeigniter 使用 case 语句进行连接

Question

这是我对 codeigniter 模型的查询

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

正如您在 locations 表的连接语句中看到的那样，我正在尝试实现一个 case 语句，但它对我不起作用。我想要的是，如果 e.city_id 中有任何整数值，那么 locations 表的连接语句应该变成

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

否则应该变成

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

非常感谢您对此方法或替代方法的任何帮助。感谢阅读

编辑这是我得到的错误

Answer 1

错误信息本身给了你一个提示:

syntax to use near '== e.city_id

mySQL 比较运算符是=，因此case when l.ID == e.city_id 应该是case when l.ID = e.city_id

参见 MySQL Comparison Functions and Operators