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: How to Select from Sub-query using Laravel Query Builder

Laravel 5: How to Select from Sub-query using Laravel Query Builder. It's very easy to use Sub-Query as Select query in Laravel. Below is a sample code snippet:

$query = DB::table("employee")->whereIn("id", array(1, 2, 3, 4));
$result = DB::table(DB::raw("(" . $query->toSql() . ") as res"))
    ->mergeBindings($query)
    ->whereIn("res.id", array(2, 3))->get();

Which will generate below SQL:

select * from (select * from `employee` where `id` in (1, 2, 3, 4)) as res 

where `res`.`id` in (2, 3))

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.

CakePHP 3.X Sub Query Example

$sub_query = TableRegistry::get("Test2")->find();
$sub_query->select(["count" => "SUM(Test2.score)"])
    ->where(["Test2.ref = Test1.id"]);

$query = TableRegistry::get("Test1")->find("All");
$query->select([
    "Test1.id", "Test1.name", "Test1.roll",
    "score" => $sub_query
]);

$list = $query->all()->toArray();