Summary:

Complex SQL with subqueries is often used in projects, and Laravel's query constructor does not directly provide a method to convert it into subqueries, or even though it does, the examples given are not very specific and are not very friendly to beginners. This article mainly talks about how Laravel constructs various SQL with subqueries.

Get ready:

First, Laravel generally implements subqueries in two ways:

1. toSql()+getQuery()+raw() method:

1. The purpose of the toSql() method is to obtain SQL without a binding parameter, that is, SQL with a question mark

select * from `rooms` where `rooms`.`project_id` = ?

1. The purpose of the getQuery() method is to get the binding parameter and get the SQL question mark instead of toSql() to get the complete SQL

select * from `rooms` where `rooms`.`project_id` = 3

1. raw() is used to nest SQL directly into Laravel's query constructor

  `$sql = 'select * from `rooms` where `rooms`.`project_id` = 3';
  $resultSql = DB::table('DB::raw($sql as room')->toSql();`   

Then you get SQL:

select * from (select * from `rooms` where `rooms`.`project_id` = 3) as room

Note: Direct use of SQL is not limited to raw(), but where raw()

2. Use some of the built-in closures of the query constructor

for example:
User::whereIn('id', function($query){ 
    $query->select('user_id') 
    ->from('admin_user') 
    ->whereIn('type', ['1', '2']); 
})->get(); 

Available SQL:

select * from `user` where `id` in (select `user_id` from `admin_user` where `type` in (1, 2));

Closure like this implements subqueries, such as where Exists, where, and so on.

Example:

PHP code:

    $ipCountObject = new \Model\BlacklistIpCountDate();
    $ipCountObj = $ipCountObject->selectRaw("sum(attack_count) AS attack_times, ip")->where('attack_count', '>', '0')->whereBetween('data', ['2017-10-10', '2017-10-11'])->groupBy('ip');
    $totalObj = DB::table( DB::raw("({$ipCountObj->toSql()}) as sub, blacklist_attack_ip"))->mergeBindings($ipCountObj->getQuery())->select('attack_ip', 'country', 'province', 'city', 'line', 'info_update_time AS attack_time', 'attack_times'); //toSql Acquired sql Yes?, variable needs to be filled in
    $totalObj = $totalObj->where('blacklist_attack_ip.attack_ip', '=', 'sub.ip')->get();

Get SQL:

SELECT
    `attack_ip`,
    `country`,
    `province`,
    `city`,
    `line`,
    `info_update_time` AS `attack_time`,
    `attack_times` 
FROM
    ( SELECT sum( attack_count ) AS attack_times, ip FROM `blacklist_ip_count_date` WHERE `attack_count` > 0 AND `date` BETWEEN '2017-10-10' AND '2017-10-11' GROUP BY `ip` ) AS sub,
    blacklist_attack_ip 
WHERE
    `blacklist_attack_ip`.`attack_ip` = `sub`.`ip`

Summary:

These two methods are flexible, the first is generally used everywhere for subqueries, and the second is only for locations related to where.Of course, my personal recommendation is uniform writing. If you use the first method in your project, then you should insist that where's subquery also use the first convenient method instead of mixing the first and second methods.In addition, the Laravel query constructor provides other closures, such as join, lefeJoin, rightJoin, and so on, but these closures have not been personally tested to implement subqueries.