+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: codeigniter join (/showthread.php?tid=78668)
codeigniter join - durairaj - 02-23-2021
Code:
SELECT `payment`.* FROM `payment`
LEFT JOIN `people` as `P` ON `P`.`id` = `payment`.`customer_id` and ( `payment`.`customer_type` = 'P' or `payment`.`customer_type` = 'CP' or `payment`.`customer_type` = 'LP' or `payment`.`customer_type` = 'BP' or `payment`.`customer_type` = 'TS' )
LEFT JOIN `branch` AS `B` ON `B`.`id` = `payment`.`branch_id` WHERE `payment`.`status` = 'active' AND `payment`.`type` = 'Income' AND `payment`.`mode` = 'Payin' GROUP BY `payment`.`customer_id`here i need to convert codeigniter query with join condition pls tell me how to do it ,bcz this query has multi and / or condition in join
pls someone help me
RE: codeigniter join - durairaj - 02-23-2021
$person_interest = "( SELECT pi.person_id, pi.payment_id, pi.person_interest_id, pi.paid_interest FROM person_interest as pi JOIN payment as p ON p.payment_id = pi.payment_id WHERE pi.status = 'active' AND p.mode = 'Payin' AND p.customer_type = 'P' GROUP BY p.customer_id )";
$this->db->select( 'payment.*, SUM( COALESCE( payment.amount ) ) AS amount, P.name, PI.paid_interest, B.branch_name' );
$this->db->where( 'payment.status', $status );
$this->db->where( 'payment.type', $mode_type );
$this->db->where( 'payment.mode', $mode );
$branch_id = $this->session->userdata( 'branch_id' );
$this->db->where( 'payment.branch_id', $branch_id );
$this->db->join( 'people as P', "P.id = payment.customer_id AND (payment.customer_type = 'P' or payment.customer_type = 'CP' OR payment.customer_type = 'LP' OR payment.customer_type = 'BP' OR payment.customer_type = 'TS' )", 'left' );
$this->db->join( $person_interest . ' AS PI', 'PI.payment_id = payment.payment_id', 'left' );
$this->db->join( 'branch AS B', 'B.id = payment.branch_id', 'left' );
$this->db->group_by( 'payment.customer_id' );
$query = $this->db->get( 'payment' );
i found solution , this worked for me perfectly