how can i use if statement in join query in codeigniter

Question

I need to use if statement in join query in Codeigniter. I try this query...

$partners = $this->db->select("h.partner_id,hd.id as detail_id,v.*, hd.handshake_id ,sum(if(hd.result = '0',1, 0)) AS not_tested ,sum(if(hd.result = '1',1, 0)) AS positive,sum(if(hd.result = '2',1, 0)) AS negative", FALSE)
                        ->from("handshake h")
                        ->join("visitor v", 'IF(h.visitor_id = "'.$visitor_data['id'].'", h.partner_id = v.id , h.visitor_id = v.id ) ', 'left',FALSE)                        
                        ->join("handshake_detail hd", ' hd.handshake_id = h.id AND hd.visitor_id <> "'.$visitor_data["id"].'"', 'left',FALSE)                       
                        ->where("h.visitor_id", $visitor_data['id'])  
                        ->or_where("h.partner_id", $visitor_data['id'])
                        ->group_by("h.partner_id") 
                        ->get()->result_array();

if we write above query in MySQL so, it work but when I try in Codeigniter syntax so it generate error like this...

FUNCTION trustcrowd_1.0.IF does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

so please help to find out right syntax of Codeigniter query.

Answer 1

my working query is...

$this->db->set_dbprefix('');

$partners = $this->db->select("hd.visitor_id partner, h.id handshake, h.utp intro_date, v.name, v.email, v.birth_date, v.location, v.gender, v.image, v.test_date, v.clinic_place, SUM( IF( hd.result =  '0', 1, 0 ) ) AS not_tested, SUM( IF( hd.result =  '1', 1, 0 ) ) AS positive, SUM( IF( hd.result =  '2', 1, 0 ) ) AS negative from tc_handshake h left join tc_visitor v on IF(h.visitor_id = '".$visitor_data['id']."', h.partner_id = v.id , h.visitor_id = v.id )", FALSE)
           ->join("tc_handshake_detail hd", 'h.id=hd.handshake_id AND hd.visitor_id <> "'.$visitor_data["id"].'"', 'left',FALSE)
           ->where("h.visitor_id", $visitor_data['id'])  
           ->or_where("h.partner_id", $visitor_data['id'])
           ->group_by("h.partner_id,h.utp")                
           ->order_by("h.id", "desc")
           ->limit($total_dis_record, $page)
           ->get()->result_array(); 

$this->db->set_dbprefix('tc_');

Answer 2

use singele quotes and false parameter

$this->db->join('table1', 'table1.id = table2.id and table1.status=1 and IF(table1.type=0,status=1,status=2)', 'left',false);

$this->db->get('table2')->result();