聊一聊laravel query builder 使用子查询

汪茂
2023-12-01

概述:

项目当中经常用到复杂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等,但是经过个人的测试,这些闭包并不能实现子查询的。

 类似资料: