Laravel 关联查询限制条数和分组查询显示为零的结果

guaosi 的个人博客 / 2647 / 3 / 创建于 5年前 / 更新于 5年前

本文搬运自我自己的博客

1. 需求

目前有这样两个需求:

1) 查询用户，显示用户的信息以及他写过的书籍。如果用户有书籍，则显示，最多显示2本。如果没有，则不显示书籍。

2) 显示用户的id号以及对应的书籍件数(只用SQL实现，不使用业务逻辑)。

2. 准备

本文中用到的user模型，数据，控制器，路由之类的都已经在另一篇文章手摸手教你让Laravel开发Api更得心应手创建好了。

users表中的数据

file

books表中的数据

file

3. 关联查询限制条数

这个比较容易，只要在关联函数限制条数即可。

3.1. 创建Book模型

php artisan make:model Models/Book

3.2. 添加关联函数并且限制条数

编辑 app/Models/User.php,添加关联函数

public function books(){
    return $this->hasMany(Book::class,'user_id','id')->limit(2); //一对多，最多关联2条
}

3.3. 测试

在app/Http/Controllers/Api/UserController.php里，随意添加一个测试函数

//关联查询限制条数
public function test(Request $request){
    $users= User::with('books')->get();
        return $users;
}

测试结果，符合要求，id为1的用户原来是3本书籍，现在只被取出2本。

file

4. 分组查询显示为零的结果

4.1. SQL语句

一开始，我们会这样写SQL语句

select `u`.`id`,`u`.`name`,`num` from `users` as `u` left join (select `user_id`,count(*) as `num` from books group by `user_id`) as `b` on `u`.id = `b`.user_id

最后显示如下，并不会将没有的显示为0

file

所以我们稍加修改，用上MySQL的内置函数

select distinct `u`.`id`,`u`.`name`,IFNULL( `b`.`num`, 0 ) AS num from `users` as `u` left join (select `user_id`,count(*) as `num` from books group by `user_id`) as `b` on `u`.id = `b`.user_id

符合我们的需求。

file

4.2. Laravel框架中使用

写SQL很容易，那我们应该如何在框架中使用呢(不允许查完再用业务逻辑后获得答案)?同时我们再附加一个条件，只要id为1,2,3,4,5的用户。

4.2.1. 直接编写

查询Laravel手册，参考查询构造器的高级join语句，我们会立刻想到下面这样编写

public function test3(){
    //统计出所有内部员工的user_id
    $user_ids = [1,2,3,4,5];
    $users = User::selectRaw('u.id,IFNULL( b.number, 0 ) AS number')
        ->from('users as u')
        ->distinct()
        ->whereIn('id', $user_ids)
        ->leftJoin('books as b',function ($join) use($user_ids){
               $join->selectRaw('user_id,count(*) as number')->whereIn('user_id', $user_ids)->groupBy('user_id')->on('u.id', '=', 'b.user_id');
            })
        ->get();
    return $users;
}

测试的时候我们发现报了错

Unknown column 'b.number' in 'field list' (SQL: select distinct u.id,IFNULL( b.number, 0 ) AS number from `users` as `u` left join `books` as `b` on `user_id` in (1, 2, 3, 4, 5) and `u`.`id` = `b`.`user_id` where `id` in (1, 2, 3, 4, 5))

最后的SQL语句跟我们想象中的不太一样。

4.2.2. 问题分析

错误的原因是，我们其实是使用left join连接了子查询，但是Laravel的联表查询，例如join，lefeJoin，rightJoin等，经过个人的测试，这些闭包并不能实现子查询的。所以最后获得的SQL语句是错误的。

Laravel官方文档的子查询并没有这方面详细的介绍，所以我们一起来了解一下其他地方查来的资料

4.2.3. Query Builder

4.2.3.1. toSql()

toSql()方法的作用是为了获取不带有binding参数的SQL

例如:

select * from `users` where `users`.`id` = ?

4.2.3.2. getQuery()

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

select * from `users` where `users`.`id` = 1

4.2.4. 修复问题

现在我们使用Query Builder来修复一下之前的问题

public function test2(){

    //统计出所有内部员工的user_id

    $user_ids = [1,2,3,4,5];

    $bookQuery = Book::selectRaw('user_id,count(*) as number')->whereIn('user_id', $user_ids)->groupBy('user_id'); //制作一个query builder

    $users = User::selectRaw('u.id,IFNULL( b.number, 0 ) AS number')
        ->from('users as u')
        ->distinct()
        ->whereIn('id', $user_ids)
        ->leftJoin(\DB::raw("({$bookQuery->toSql()}) as b"),function ($join) use($bookQuery){
            //toSql()返回的是等待绑定参数的SQL语句
            $join->mergeBindings($bookQuery->getQuery())->on('u.id','=','b.user_id');
            //mergeBindings是将SQl的参数进行绑定
        })
        ->get();

    return $users;
}