Laravel 5.X: Use of Sub-query | Sub Query in Where Condition. It's very important. Below is an sample example how we can use sub-query in our application.
And will generate below SQL:
$test_data = Employee::from((new Employee())->getTable() . " AS entity")
OR
$test_data = DB::table((new Employee())->getTable() . " AS entity") ->whereIn("entity.id", function($q) { $q->select("entity.id"); $q->from((new Employee())->getTable() . " AS entity"); $q->join((new Supervisor())->getTable() . " AS user", "user.id", "=", "entity.sup_id"); $q->whereNotNull("entity.sup_id"); $q->where("user.first_name", "like", "%E%"); }) ->select("entity.id", "entity.type") ->limit(5) ->get();
And will generate below SQL:
SELECT `entity`.`id`, `entity`.`type` FROM `employee` AS `entity` WHERE `entity`.`id` IN ( SELECT `entity`.`id` FROM `employee` AS `entity` INNER JOIN `users` AS `user` ON `user`.`id` = `entity`.`sup_id` WHERE `entity`.`sup_id` IS NOT NULL AND `user`.`first_name` LIKE '%E%' ) LIMIT 5