在 Laravel 中使用子查詢來構建動態模型關係

Epona發表於2019-07-26
Laravel模型

本文翻譯自 Dynamic relationships in Laravel using subqueries

當我在使用 Laravel 寫程式的時候，我腦海中一直有兩個目標：

  1. 儘可能少的使用資料庫查詢
  2. 儘可能少的使用記憶體

我們可能很容易的解決第一個問題。比如我們熟知的 N+1 問題可以通過懶載入進行解決。但是，我們對第二個問題卻不太注意，有時候我們可能會做的更糟糕。

讓我解釋一下這是如何發生的，並且怎樣在程式碼中進行改善。

挑戰

考慮下面的例子。在你的程式中需要展示一些個人資訊，包含上一次登入時間。這個看似簡單的頁面其實也有比較複雜的東西。

Name Email Last Login
Adam Campbell adam@hotmeteor.com Nov 10, 2018 at 12:01pm
Taylor Otwell taylor@laravel.com Never
Jonathan Reinink jonathan@reinink.ca Jun 2, 2018 at 5:30am
Adam Wathan adam.wathan@gmail.com Nov 20, 2018 at 7:49am

下面是基本的遷移檔案程式碼：

Schema::create('users', function (Blueprint $table) {
    $table->increments('id');
    $table->string('name');
    $table->string('email');
    $table->timestamps();
});

Schema::create('logins', function (Blueprint $table) {
    $table->increments('id');
    $table->integer('user_id');
    $table->string('ip_address');
    $table->timestamp('created_at');
});

下面是模型檔案和對應的關係：

class User extends Model
{
    public function logins()
    {
        return $this->hasMany(Login::class);
    }
}

class Login extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

我們可以使用下面的blade模版程式碼來展示上面表格中的內容

$users = User::all();

@foreach ($users as $user)
    <tr>
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
        <td>
            @if ($lastLogin = $user->logins()->latest()->first())
                {{ $lastLogin->created_at->format('M j, Y \a\t g:i a') }}
            @else
                Never
            @endif
        </td>
    </tr>
@endforeach

如果我們是一個好的開發者的話（我們確實是），我們會發現這裡有 N + 1 的問題，假設我們有50個使用者，那麼我們會觸發51條查詢。

select * from "users";
select * from "logins" where "logins"."user_id" = 1 and "logins"."user_id" is not null order by "created_at" desc limit 1;
select * from "logins" where "logins"."user_id" = 2 and "logins"."user_id" is not null order by "created_at" desc limit 1;
// ...
select * from "logins" where "logins"."user_id" = 49 and "logins"."user_id" is not null order by "created_at" desc limit 1;
select * from "logins" where "logins"."user_id" = 50 and "logins"."user_id" is not null order by "created_at" desc limit 1;

解決問題的方法也很簡單，使用 with 方法即可。

$users = User::with('logins')->get();

@foreach ($users as $user)
    <tr>
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
        <td>
            @if ($user->logins->isNotEmpty())
                {{ $user->logins->sortByDesc('created_at')->first()->created_at->format('M j, Y \a\t g:i a') }}
            @else
                Never
            @endif
        </td>
    </tr>
@endforeach

這樣我們將51條查詢語句降低到了2條，大成功！

其實，並不完全正確。這裡就會引發記憶體使用的問題。誠然，我們解決了 N + 1 問題，但是我們也帶來了更多的記憶體使用量：

  • 每個頁面的使用者量 50個使用者
  • 使用者平均登入次數 250次
  • 總的載入記錄 12500條記錄

我們可以看到，假設我們有50個使用者，平均每個使用者有250條訪問資料，那麼我們會載入12500條記錄。除了記憶體使用量的增加，相關的計算耗時也會增加。而且這只是一個保守的例子。當有百萬級資料時這就很可怕了。

快取

你這時可能會想，沒什麼大不了的，快取上次登入記錄即可：

Schema::table('users', function (Blueprint $table) {
   $table->integer('last_login_id');
});

當使用者登入的時候，我們在建立登入記錄的同時，更新 last_login_id 欄位。然後在User模型中新建一個模型關係 lastLogin，然後懶載入。

$users = User::with('lastLogin')->get();

這確實是一個比較合理的解決辦法。但是要注意的是，有時候我們想要的內容不會這麼簡單。而且我們有更好的解決辦法。

子查詢

另外的一個解決辦法是——使用子查詢。你可能不知道的是 Laravel 已經支援我們使用 selectSub 方法來進行子查詢。首先看一個普通的例子：

$lastLogin = Login::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
    ->limit(1)
    ->getQuery();

$users = User::select('users.*')
    ->selectSub($lastLogin, 'last_login_at')
    ->get();

@foreach ($users as $user)
    <tr>
        <td>{{ $user->name }}</td>
        <td>{{ $user->email }}</td>
        <td>
            @if ($user->last_login_at)
                {{ $user->last_login_at->format('M j, Y \a\t g:i a') }}
            @else
                Never
            @endif
        </td>
    </tr>
@endforeach

在上面的例子中，我們並沒有使用模型關聯，而是使用了子查詢。實際上的查詢語句如下：

select
    "users".*,
    (
        select "created_at" from "logins"
        where "user_id" = "users"."id"
        order by "created_at" desc
        limit 1
    ) as "last_login_at"
from "users"

使用子查詢能過讓我們通過一條查詢語句得到我們所需要的資訊，同時還有巨大的效能提升。

Macro

在我們進入下一步前，我想向你們展示一下我寫的一個Macro來使我的程式碼更加簡潔，使用方法很簡單，將下面的程式碼新增到AppServiceProvider中就可以了

use Illuminate\Database\Query\Builder;

Builder::macro('addSubSelect', function ($column, $query) {
    if (is_null($this->columns)) {
        $this->select($this->from.'.*');
    }

    return $this->selectSub($query->limit(1), $column);
});

關於 Macro 的簡單使用，可以看 使用巨集（Macro）來擴充套件 Laravel 的資料庫請求構建器
原文作者針對這個 Macro 寫了一個 Composer 包 reinink/advanced-eloquent。

使用 Macro 後 我們可以把程式碼改成下面的樣子：

$users = User::addSubSelect('last_login_at', Login::select('created_at')
    ->whereColumn('user_id', 'users.id')
    ->latest()
)->get();

使用 Scope

更近一步，我們可以使用 scope 來進一步優化程式碼

class User extends Model
{
    public function scopeWithLastLoginDate($query)
    {
        $query->addSubSelect('last_login_at', Login::select('created_at')
            ->whereColumn('user_id', 'users.id')
            ->latest()
        );
    }
}

$users = User::withLastLoginDate()->get();

構建動態模型關係

現在到了最有趣的部分，我們已經能夠使用子查詢來獲得上次登入時間，但是如果我們需要獲得更多其他的資訊呢？比如，我們想要獲得上次登入時的IP地址。我們應當怎麼做呢？

一個簡單的方法是建立第二條子查詢

$users = User::withLastLoginDate()->withLastLoginIpAddress()->get();

{{ $user->last_login_at->format('M j, Y \a\t g:i a') }} ({{ $user->last_login_ip_address }})

當然，這肯定是行得通的。但是如果我們能夠獲得 Login 例項就更好了。特別是模型有其他方法的情況，比如模型關聯和訪問器方法。

$users = User::withLastLogin()->get();

{{ $user->lastLogin->created_at->format('M j, Y \a\t g:i a') }} ({{ $user->lastLogin->ip_address }})

首先，我們定義一個 lastLogin方法，返回模型關係。在之前的例子中我們介紹了在 users 表中加入 last_login_id 的方法。但是，這裡我們使用子查詢來進行構造。

class User extends Model
{
    public function lastLogin()
    {
        return $this->belongsTo(Login::class);
    }

    public function scopeWithLastLogin($query)
    {
        $query->addSubSelect('last_login_id', Login::select('id')
            ->whereColumn('user_id', 'users.id')
            ->latest()
        )->with('lastLogin');
    }
}

$users = User::withLastLogin()->get();

<table>
    <tr>
        <th>Name</th>
        <th>Email</th>
        <th>Last Login</th>
    </tr>
    @foreach ($users as $user)
        <tr>
            <td>{{ $user->name }}</td>
            <td>{{ $user->email }}</td>
            <td>
                @if ($user->lastLogin)
                    {{ $user->lastLogin->created_at->format('M j, Y \a\t g:i a') }}
                @else
                    Never
                @endif
            </td>
        </tr>
    @endforeach
</table>

上面的程式碼執行後，只有兩條查詢語句，第一條的查詢如下：

select
    "users".*,
    (
        select "id" from "logins"
        where "user_id" = "users"."id"
        order by "created_at" desc
        limit 1
    ) as "last_login_id"
from "users"

這裡我們基本上實現了 last_login_id 欄位的功能，但是並不會真正的建立這個欄位。現在讓我們看看第二條查詢語句：

select * from "logins" where "logins"."id" in (1, 3, 5, 13, 20 ... 676, 686)

我們的子查詢只會返回上次的登入資訊。並且由於我們使用了標準的 Laravel 模型關聯，我們能夠繼續使用 Login 模型中的相關方法。非常棒。

懶載入動態模型關係

值得注意的是我們無法向下面那樣直接使用模型關聯，這是因為我們的 scope 並沒有預設載入。

$lastLogin = User::first()->lastLogin; // 會返回null

如果你想要懶載入功能，可以加入到全域性的scope中：

class User extends Model
{
    protected static function boot()
    {
        parent::boot();

        static::addGlobalScope(function ($query) {
            $query->withLastLogin();
        });
    }
}

上面的內容能否使用 HasOne 來替代？

你也許會好奇我們能否使用 HasOne 來進行處理？ 就結論而言，是不行的。讓我們看看是為什麼。

public function lastLogin()
{
    return $this->hasOne(Login::class)->latest();
}

首先上面的程式碼確實能夠返回我們所需要的資料。但是如果我們檢視查詢語句的話就會發現問題。

select * from "logins" where "logins"."user_id" in (1, 2, 3...99, 100) order by "created_at" desc

同樣會導致我們之前提到的問題。那麼我們加上limit會怎樣呢？

public function lastLogin()
{
    return $this->hasOne(Login::class)->latest()->limit(1);
}

查詢語句如下

select * from "logins" where "logins"."user_id" in (1, 2, 3...99, 100) order by "created_at" desc limit 1

這會導致只有最後登入的使用者返回時間，其他所有使用者返回null。

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

There's nothing wrong with having a little fun.

相關文章