聊一聊laravel query builder 使用子查询

概述：

项目当中经常用到复杂SQL带有子查询，而Laravel的查询构造器并没有直接提供转化成子查询的方法，或者说尽管提供了方法，可是给出来的例子却不太具体，对刚上手的新手来说非常不友好，本文章主要是聊一聊Laravel如何构造各种带有子查询的SQL。

准备：

首先，Laravel一般实现子查询的两种方式：

1、toSql()+getQuery()+raw()方法：

1. toSql()方法的作用是为了获取不带有binding 参数的SQL,也就是说带问号的SQL

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

1. getQuery()方法的作用是为了获取binding参数并代替toSql()获得SQL的问号，从而得到完整的SQL

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

1. raw()的作用是直接把SQL套进Laravel的查询构造器中

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

那么你就能获得SQL:

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

注意：直接使用SQL不限于raw()，还有whereRaw()

2、使用查询构造器一些自带的闭包

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

可以获得的SQL:

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

类似这种闭包实现子查询的，还有whereExists、where等等。

实例：

PHP代码:

    $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获得的sql有？，需要填入变量
    $totalObj = $totalObj->where('blacklist_attack_ip.attack_ip', '=', 'sub.ip')->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`

总结：

这两种方法要灵活应用，第一种基本是通用于子查询在任何地方，而第二种是只适用于与where相关的位置。当然个人建议是统一写法，如果你在项目中用了第一种，那么你就应该坚持where的子查询也用第一种方便，而不是混合第一第二种方法使用。除此之外，Laravel 查询构造器也提供了其他的闭包，例如join，lefeJoin，rightJoin等，但是经过个人的测试，这些闭包并不能实现子查询的。