Laravel 關聯查詢限制條數和分組查詢顯示為零的結果

guaosi發表於2019-03-22
Laravel

本文搬運自我自己的部落格

目前有這樣兩個需求:

1) 查詢使用者，顯示使用者的資訊以及他寫過的書籍。如果使用者有書籍，則顯示，最多顯示2本。如果沒有，則不顯示書籍。

2) 顯示使用者的id號以及對應的書籍件數(只用SQL實現，不使用業務邏輯)。

        本文中用到的user模型，資料，控制器，路由之類的都已經在另一篇文章 手摸手教你讓Laravel開發Api更得心應手 建立好了。

users表中的資料

file

books表中的資料

file

這個比較容易，只要在關聯函式限制條數即可。

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.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;
}

4.2.5. 測試

最後的結果符合我們的需求

file

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章