Thursday, 10 March 2016

To Write a Sample Join Query in Laravel 5.2

Please follow the example below to write the the Multiple Join Query on Laravel 5.2
// NOTE: Please do not forgot add below line at the top to use DB instance
$whereClause = array( $this->table.".checklists_id" => 1 );
$items = DB::table($this->table)
->join('approval_states', '', '=', $this->table.'.approval_state_id')
->join('approval_levels', '', '=', 'approval_level_conditions.approval_levels_id')
 ' as level_name', 'approval_levels.level',
 DB::raw("IF( approval_states.state='Approved','1','0' ) AS state_value")
->where($this->table.'.deleted_at', null) // to ignore the soft deleted records.

Output Query (will produce the following JOIN query):
SELECT `approval_checklists`.*, `approval_levels`.`name` AS `level_name`, `approval_levels`.`level`, `approval_states`.`state`, 
IF( approval_states.state='Approved','1','0' ) AS state_value FROM `approval_checklists` 
INNER JOIN `approval_states` ON `approval_states`.`id` = `approval_checklists`.`approval_state_id` 
INNER JOIN `approval_levels` ON `approval_levels`.`id` = `approval_level_conditions`.`approval_levels_id` 
WHERE (`approval_levels`.`checklists_id` = 1) 
AND `approval_checklists`.`deleted_at` IS NULL

Hope it helps!!!

No comments:

Post a Comment

Please post any queries and comments here.