Laravel 5: Fluent Query Builder Join with subquery

Laravel is a strong php based framework today. Below is a code sample showing how raw query used for join.

$list = DB::table("users AS u")
    ->join(DB::raw('(SELECT id AS oid,name FROM organization) o'), function($join) {
        $join->on('o.oid', '=', 'u.organization_id');
    })
    ->where("o.oid", 15)
    ->limit(10)
    ->orderBy("u.id", "DESC")
    ->select("u.id", "o.oid", "o.name")
    ->get();

Laravel 5.X: Use of SubQuery | Sub Query in Where Condition

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.

$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

MySQL UPDATE with SUBQUERY of same table

I want to update a table named "test" with some sub-query where sub-query is the same "test" table. I wrote the below SQL first to update my "test" table:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT id FROM test WHERE id%2=0)

And got below error:

#1093 - Table 'test' is specified twice, both as a target for 'UPDATE' and as a separate source for data.

And in this case we have a nice solution provided by MySQL itself is processing temporary table, query is below:

UPDATE test SET roll=CONCAT(roll,"-",id) WHERE id IN (SELECT t.id FROM (SELECT * FROM test) AS t WHERE t.id%2=0)

And it works. Marked yellow color section actually do the temporary table works.