在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

下面是基本的迁移文件代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
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');
});

下面是模型文件和对应的关系:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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模版代码来展示上面表格中的内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$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条查询。

1
2
3
4
5
6
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 方法即可。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
$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条记录。除了内存使用量的增加,相关的计算耗时也会增加。而且这只是一个保守的例子。当有百万级数据时这就很可怕了。

缓存

你这时可能会想,没什么大不了的,缓存上次登录记录即可:

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

当用户登录的时候,我们在创建登录记录的同时,更新 last_login_id 字段。然后在User模型中新建一个模型关系 lastLogin,然后懒加载。

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

这确实是一个比较合理的解决办法。但是要注意的是,有时候我们想要的内容不会这么简单。而且我们有更好的解决办法。

子查询

另外的一个解决办法是——使用子查询。你可能不知道的是 Laravel 已经支持我们使用 selectSub 方法来进行子查询。首先看一个普通的例子:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$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

在上面的例子中,我们并没有使用模型关联,而是使用了子查询。实际上的查询语句如下:

1
2
3
4
5
6
7
8
9
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中就可以了

1
2
3
4
5
6
7
8
9
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 后 我们可以把代码改成下面的样子:

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

使用 Scope

更近一步,我们可以使用 scope 来进一步优化代码

1
2
3
4
5
6
7
8
9
10
11
12
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地址。我们应当怎么做呢?

一个简单的方法是创建第二条子查询

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

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

当然,这肯定是行得通的。但是如果我们能够获得 Login 实例就更好了。特别是模型有其他方法的情况,比如模型关联和访问器方法。

1
2
3
$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 的方法。但是,这里我们使用子查询来进行构造。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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>

上面的代码执行后,只有两条查询语句,第一条的查询如下:

1
2
3
4
5
6
7
8
9
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 字段的功能,但是并不会真正的创建这个字段。现在让我们看看第二条查询语句:

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

我们的子查询只会返回上次的登录信息。并且由于我们使用了标准的 Laravel 模型关联,我们能够继续使用 Login 模型中的相关方法。非常棒。

懒加载动态模型关系

值得注意的是我们无法向下面那样直接使用模型关联,这是因为我们的 scope 并没有默认加载。

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

如果你想要懒加载功能,可以加入到全局的scope中:

1
2
3
4
5
6
7
8
9
10
11
class User extends Model
{
protected static function boot()
{
parent::boot();

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

上面的内容能否使用 HasOne 来替代?

你也许会好奇我们能否使用 HasOne 来进行处理? 就结论而言,是不行的。让我们看看是为什么。

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

首先上面的代码确实能够返回我们所需要的数据。但是如果我们查看查询语句的话就会发现问题。

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

同样会导致我们之前提到的问题。那么我们加上limit会怎样呢?

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

查询语句如下

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

这会导致只有最后登录的用户返回时间,其他所有用户返回null