0

Here is my codeigniter's active record query I am working on

$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 = e.city_id', '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')
             ->where("find_in_set($get_engineer_id, wo.`assigned_to_enggs`)");

In the work_orders table I have a column assigned_to_enggs which has comma seperated id of employees for each record. I want to get the coma seperated names of employees against these ids by joining with employees table.

Here is my work_orders table

╔══════════╦═══════════════════╗
║ ID       ║assigned_to_enggs  ║
╠══════════╬═══════════════════╣
║ 1        ║ 2,4,5             ║
║ 2        ║ 1,2               ║
║ 3        ║ 2                 ║
║ 4        ║ 3,1               ║
║ 5        ║ 2,4,1             ║
╚══════════╩═══════════════════╝

employees table

╔══════════╦═════════════════╗
║ emp_id   ║    name         ║
╠══════════╬═════════════════╣
║ 1        ║ Irfan           ║
║ 2        ║ Kamran Khan     ║
║ 3        ║ Faiza           ║
║ 4        ║ Atif            ║
║ 5        ║ Farrukh         ║
╚══════════╩═════════════════╝

I Have tried GROUP_CONCAT() but didn't work for me. Can someone help me. Thanks in advance

Vickel
  • 7,879
  • 6
  • 35
  • 56
Muhammad Asif Raza
  • 647
  • 2
  • 5
  • 24
  • 3
    Related: [is-storing-a-delimited-list-in-a-database-column-really-that-bad](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – Paul Spiegel May 20 '19 at 20:39
  • As Pault said is a bad idea but if you have to check here https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows – Juan Carlos Oropeza May 20 '19 at 20:42
  • 1
    Well.. you need a LEFT JOIN with `employees` table with `FIND_IN_SET()` in the ON clause. `GROUP BY wo.ID` and `SELECT ... GROUP_CONCAT(employees.name) ...`. This is going to be terribly slow, if you have alot of data. And I have no clue of how to code it in CI. – Paul Spiegel May 20 '19 at 20:51

0 Answers0