13、Laravel 11 模型关系映射与 N+1 查询问题
我们的文章分类已经完成了，是时候对文章进行操作了。
文章 CURD
1、创建控制器、模型
$ php artisan make:controller PostController --resource --model=Post
2、添加路由
routes/web.php
...
Route::middleware('auth')->group(function () {
...
Route::middleware('is_admin')
->group(function () {
Route::resource('categories', \App\Http\Controllers\CategoryController::class);
Route::resource('posts', \App\Http\Controllers\PostController::class);
});
});
3、添加导航
resources/views/layouts/navigation.blade.php
...
<x-nav-link :href="route('dashboard')" :active="request()->routeIs('dashboard')">
{{ __('Dashboard') }}
</x-nav-link>
@if(auth()->user()->is_admin)
<a href="{{ route('categories.index') }}" class="inline-flex items-center px-1 pt-1 border-b-2 border-transparent text-sm font-medium leading-5 text-gray-500 hover:text-gray-700 hover:border-gray-300 focus:outline-none focus:text-gray-700 focus:border-gray-300 transition duration-150 ease-in-out">
分类
</a>
<a href="{{ route('posts.index') }}" class="inline-flex items-center px-1 pt-1 border-b-2 border-transparent text-sm font-medium leading-5 text-gray-500 hover:text-gray-700 hover:border-gray-300 focus:outline-none focus:text-gray-700 focus:border-gray-300 transition duration-150 ease-in-out">
文章
</a>
@endif
...
我们使用使用 @if 指令，来判断是不是管理员。如果是管理员才显示。
4、文章 CURD
app/Http/Controllers/PostController.php
<?php
namespace App\Http\Controllers;
use App\Models\Category;
use App\Models\Post;
use Illuminate\Http\Request;
class PostController extends Controller
{
public function index()
{
$posts = Post::all();
return view('posts.index', compact('posts'));
}
public function create()
{
$categories = Category::all();
return view('posts.create', compact('categories'));
}
public function store(Request $request)
{
Post::create([
'title' => $request->input('title'),
'text' => $request->input('text'),
'category_id' => $request->input('category_id'),
]);
return redirect()->route('posts.index');
}
public function show(Post $post)
{
//
}
public function edit(Post $post)
{
$categories = Category::all();
return view('posts.edit', compact('post', 'categories'));
}
public function update(Request $request, Post $post)
{
$post->update([
'title' => $request->input('title'),
'text' => $request->input('text'),
'category_id' => $request->input('category_id'),
]);
return redirect()->route('posts.index');
}
public function destroy(Post $post)
{
$post->delete();
return redirect()->route('posts.index');
}
}
5、文章列表页
resources/views/posts/index.blade.php
<x-app-layout>
<x-slot name="header">
<h2 class="font-semibold text-xl text-gray-800 leading-tight">
{{ __('Posts') }}
</h2>
</x-slot>
<div class="py-12">
<div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
<div class="bg-white overflow-hidden shadow-sm sm:rounded-lg">
<div class="p-6 bg-white border-b border-gray-200">
<a href="{{ route('posts.create') }}" class="inline-flex items-center px-4 py-2 bg-gray-800 border border-transparent rounded-md font-semibold text-xs text-white uppercase tracking-widest hover:bg-gray-700 focus:bg-gray-700 active:bg-gray-900 focus:outline-none focus:ring-2 focus:ring-indigo-500 focus:ring-offset-2 transition ease-in-out duration-150">添加文章</a>
<br /><br />
<table>
<thead>
<tr>
<th>标题</th>
<th>分类</th>
<th></th>
</tr>
</thead>
<tbody>
@foreach($posts as $post)
<tr>
<td>{{ $post->title }}</td>
<td>{{$post->category_id}}</td>
<td>
<a href="{{ route('posts.edit', $post) }}">编辑</a>
<form method="POST" action="{{ route('posts.destroy', $post) }}">
@csrf
@method('DELETE')
<button type="submit" onclick="return confirm('Are you sure?')">删除</button>
</form>
</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</div>
</div>
</x-app-layout>
6、添加文章
resources/views/posts/create.blade.php
<x-app-layout>
<x-slot name="header">
<h2 class="font-semibold text-xl text-gray-800 leading-tight">
{{ __('新文章') }}
</h2>
</x-slot>
<div class="py-12">
<div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
<div class="bg-white overflow-hidden shadow-sm sm:rounded-lg">
<div class="p-6 text-gray-900">
<form method="POST" action="{{ route('posts.store') }}">
@csrf
<div>
<div>
<label for="title">标题:</label>
</div>
<input type="text" name="title" id="title" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm">
</div>
<div>
<div>
<label for="text">内容:</label>
</div>
<textarea name="text" id="text" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm"></textarea>
</div>
<div>
<div>
<label for="category_id">分类:</label>
</div>
<select name="category_id" id="category_id" class="rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
@foreach ($categories as $category)
<option value="{{ $category->id }}">{{ $category->name }}</option>
@endforeach
</select>
</div>
<div>
<button type="submit" class="inline-flex items-center px-4 py-2 bg-gray-800 border border-transparent rounded-md font-semibold text-xs text-white uppercase tracking-widest hover:bg-gray-700 focus:bg-gray-700 active:bg-gray-900 focus:outline-none focus:ring-2 focus:ring-indigo-500 focus:ring-offset-2 transition ease-in-out duration-150">
添加
</button>
</div>
</form>
</div>
</div>
</div>
</div>
</x-app-layout>
7、编辑文章
resources/views/posts/edit.blade.php
<x-app-layout>
<x-slot name="header">
<h2 class="font-semibold text-xl text-gray-800 leading-tight">
{{ __('编辑文章') }}
</h2>
</x-slot>
<div class="py-12">
<div class="max-w-7xl mx-auto sm:px-6 lg:px-8">
<div class="bg-white overflow-hidden shadow-sm sm:rounded-lg">
<div class="p-6 text-gray-900">
<form method="POST" action="{{ route('posts.update', $post) }}">
@csrf
@method('PUT')
<div>
<div>
<label for="title">文章:</label>
</div>
<input type="text" name="title" id="title" value="{{ $post->title }}" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm">
</div>
<div>
<div>
<label for="text">内容:</label>
</div>
<textarea name="text" id="text" class="border-gray-300 focus:border-indigo-500 focus:ring-indigo-500 rounded-md shadow-sm">{{ $post->text }}</textarea>
</div>
<div>
<div>
<label for="category_id">分类:</label>
</div>
<select name="category_id" id="category_id" class="rounded-md shadow-sm border-gray-300 focus:border-indigo-300 focus:ring focus:ring-indigo-200 focus:ring-opacity-50">
@foreach ($categories as $category)
<option value="{{ $category->id }}" @selected($category->id == $post->category_id)>{{ $category->name }}</option>
@endforeach
</select>
</div>
<div>
<button type="submit" class="inline-flex items-center px-4 py-2 bg-gray-800 border border-transparent rounded-md font-semibold text-xs text-white uppercase tracking-widest hover:bg-gray-700 focus:bg-gray-700 active:bg-gray-900 focus:outline-none focus:ring-2 focus:ring-indigo-500 focus:ring-offset-2 transition ease-in-out duration-150">
更新
</button>
</div>
</form>
</div>
</div>
</div>
</div>
</x-app-layout>
模型关联
文章列表中，分类显示的是分类 ID，鬼知道分类 ID 是什么意思。我们可以通过模型关联轻松取出分类名称。
1、模型关联
app/Models/Post.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
class Post extends Model
{
use HasFactory;
protected $fillable = ['title', 'text', 'category_id'];
public function category(): BelongsTo
{
return $this->belongsTo(Category::class);
}
}
然后就可以通过调用属性的方式来获取分类名称了。
2、文章列表显示分类名称
...
@foreach($posts as $post)
<tr>
<td>{{ $post->title }}</td>
<td>{{ $post->category->name }}</td>
<td>
<a href="{{ route('posts.edit', $post) }}">编辑</a>
<form method="POST" action="{{ route('posts.destroy', $post) }}">
@csrf
@method('DELETE')
<button type="submit" onclick="return confirm('Are you sure?')">删除</button>
</form>
</td>
</tr>
@endforeach
...
N+1 问题
分类都已经显示，一切貌似都很 OK？下面我们来看看都有什么问题。
1、安装 debug 工具
$ composer require barryvdh/laravel-debugbar --dev
2、查看 SQL
select * from `b_categories` where `b_categories`.`id` = 1 limit 1
1ms
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 1 limit 1
220μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 1 limit 1
210μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 2 limit 1
170μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 2 limit 1
150μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 2 limit 1
160μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 2 limit 1
140μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 2 limit 1
150μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 1 limit 1
150μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 1 limit 1
160μs
book_laravel11_blog
index.blade.php#36
select * from `b_categories` where `b_categories`.`id` = 2 limit 1
执行了 N 次 SQL 查询，这就是问题。
当我们使用 $post->category->name 时，每执行一次都要去查询一次数据库，这哪受得了。解决方法是使用预加载。
3、预加载解决 N+1 问题
app/Http/Controllers/PostController.php
...
public function index()
{
$posts = Post::with('category')->get();
return view('posts.index', compact('posts'));
}
当没有任何条件时，可以使用 all() 方法。如果有任何条件，则最后应使用 get() 方法。
现在，Eloquent 将会在一个查询中加载所有帖子的所有分类。
请登录后再评论