phpmysqlcodeignitercodeigniter-3entity-attribute-value

how to avoid multiple joins in a single query in CodeIgniter

this is my code

$this->db->select('custome_module.id, custome_module.name, min(l1.nmark_completed) as call_waiter, min(l2.nmark_completed) as bill,min(l3.nmark_completed) as tray,min(l4.nmark_completed) as ordera');
$this->db->from("custome_module");
$this->db->join('restaurant_logs as l1', 'custome_module.id = l1.nmodule_id AND l1.ntype = 1', 'left');
$this->db->join('restaurant_logs as l2', 'custome_module.id = l2.nmodule_id AND l2.ntype = 2', 'left');
$this->db->join('restaurant_logs as l3', 'custome_module.id = l3.nmodule_id AND l3.ntype = 6', 'left');
$this->db->join('restaurant_logs as l4', 'custome_module.id = l4.nmodule_id AND l4.ntype = 5', 'left');
$this->db->where('custome_module.nbranch_id', $this->data['user_session']['nid']);
$this->db->group_by('custome_module.id'); 
$this->db->get();

how to optimize the query.

Solution

  • EAV is a clumsy and inefficient schema pattern. Condolences.

    This will help performance some:

    INDEX(nmodule_id, ntype)