今日报丨【整理分享】Laravel8优化数据库查询的18 个 小技巧

$posts = Post::all(); // 使用 eloquent$posts = DB::table("posts")->get(); // 使用查询构造器 foreach ($posts as $post){ // 处理 posts 操作}

// 当使用 eloquent 时$posts = Post::chunk(100, function($posts){    foreach ($posts as $post){     // Process posts    }}); // 当使用查询构造器时$posts = DB::table("posts")->chunk(100, function ($posts){    foreach ($posts as $post){     // Process posts    }});

// 使用 eloquentforeach (Post::cursor() as $post){   // 处理单个 post} // 使用 query 构建器foreach (DB::table("posts")->cursor() as $post){   // 处理单个 post}

// 使用 eloquent$posts = Post::chunkById(100, function($posts){    foreach ($posts as $post){     // 处理 posts    }}); // 使用 query 构造器$posts = DB::table("posts")->chunkById(100, function ($posts){    foreach ($posts as $post){     // 处理 posts    }});

select * from posts offset 0 limit 100

select * from posts offset 101 limit 100

select * from posts order by id asc limit 100

select * from posts where id > 100 order by id asc limit 100

$posts = Post::find(1); // 使用 eloquent$posts = DB::table("posts")->where("id","=",1)->first(); // 使用 query 构建器

select * from posts where id = 1 limit 1

$posts = Post::select(["id","title"])->find(1); // 使用 eloquent$posts = DB::table("posts")->where("id","=",1)->select(["id","title"])->first(); // 使用 query 构建器

select id,title from posts where id = 1 limit 1

$posts = Post::select(["title","slug"])->get(); // 使用 eloquent$posts = DB::table("posts")->select(["title","slug"])->get(); // 使用 query 构建器

foreach ($posts as $post){    // $post 是 Post 模型或  php 标准对象    $post->title;    $post->slug;}

$posts = Post::pluck("title", "slug"); // 使用 eloquent 时$posts = DB::table("posts")->pluck("title","slug"); // 使用查询构造器时

foreach ($posts as $slug => $title){    // $title 是 post 的 title    // $slug 是 post 的 slug}

$posts = Post::pluck("title"); // 使用 eloquent 时$posts = DB::table("posts")->pluck("title"); // 使用查询构造器时foreach ($posts as  $title){    // $title 是 post 的 title}

$posts = Post::all()->count(); // 使用 eloquent$posts = DB::table("posts")->get()->count(); // 使用查询构造器

select * from posts

$posts = Post::count(); // 使用 eloquent 时$posts = DB::table("posts")->count(); // 使用查询构造器时

select count(*) from posts

class PostController extends Controller{    public function index()    {        $posts = Post::all();        return view("posts.index", ["posts" => $posts ]);    }}

// posts/index.blade.php 文件 @foreach($posts as $post)    
        
{{ $post->title }}
        
Author: {{ $post->author->name }}
    
@endforeach

select * from posts // 假设返回5条数据select * from authors where id = { post1.author_id }select * from authors where id = { post2.author_id }select * from authors where id = { post3.author_id }select * from authors where id = { post4.author_id }select * from authors where id = { post5.author_id }

$posts = Post::all(); // Avoid doing this$posts = Post::with(["author"])->get(); // Do this instead

select * from posts // Assume this query returned 5 postsselect * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )

@foreach($posts as $post)    
        
{{ $post->title }}
        
Author: {{ $post->author->name }}
        
Author"s Team: {{ $post->author->team->name }}
    
@endforeach

$posts = Post::with(["author"])->get();

select * from posts // Assume this query returned 5 postsselect * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )select * from teams where id = { author1.team_id }select * from teams where id = { author2.team_id }select * from teams where id = { author3.team_id }select * from teams where id = { author4.team_id }select * from teams where id = { author5.team_id }

$posts = Post::with(["author.team"])->get();

select * from posts // Assume this query returned 5 postsselect * from authors where id in( { post1.author_id }, { post2.author_id }, { post3.author_id }, { post4.author_id }, { post5.author_id } )select * from teams where id in( { author1.team_id }, { author2.team_id }, { author3.team_id }, { author4.team_id }, { author5.team_id } )

$post = Post::findOrFail();$post->author->id;

select * from posts where id =  limit 1select * from authors where id =  limit 1

$post = Post::findOrFail();$post->author_id; // 帖子表有存放作者 id 的 author_id 列

 $posts, "private_posts" => $private_posts ]);    }}

// posts/index.blade.php @if( request()->user()->isAdmin() )    
Private Posts
    
        @foreach($private_posts as $post)            
                
{{ $post->title }}
                
Published At: {{ $post->published_at }}
            
        @endforeach    
@endif 
Posts
    @foreach($posts as $post)        
            
{{ $post->title }}
            
Published At: {{ $post->published_at }}
        
    @endforeach

$posts = Post::all();$private_posts = PrivatePost::all();

$posts = Post::all();$private_posts = collect();if( request()->user()->isAdmin() ){    $private_posts = PrivatePost::all();}

$published_posts = Post::where("status","=","published")->get();$featured_posts = Post::where("status","=","featured")->get();$scheduled_posts = Post::where("status","=","scheduled")->get();

select * from posts where status = "published"select * from posts where status = "featured"select * from posts where status = "scheduled"

$posts =  Post::whereIn("status",["published", "featured", "scheduled"])->get();$published_posts = $posts->where("status","=","published");$featured_posts = $posts->where("status","=","featured");$scheduled_posts = $posts->where("status","=","scheduled");

select * from posts where status in ( "published", "featured", "scheduled" )

$posts = Post::where("status","=","published")->get();

Schema::table("posts", function (Blueprint $table) {   $table->index("status");});

$posts = Post::paginate(20);

$posts = Post::paginate(20); // 为所有页面生成分页链接$posts = Post::simplePaginate(20); // 仅生成上一页和下一页的分页链接

select * from table_name where column like %keyword%

select * from table_name where column like keyword%

$posts = POST::whereDate("created_at", ">=", now() )->get();

select * from posts where date(created_at) >= "timestamp-here"

$posts = Post::where("created_at", ">=", now() )->get();

select * from posts where created_at >= "timestamp-here"

$posts = Post::latest()->get();// or $posts = Post::orderBy("created_at", "desc")->get();

select * from posts order by created_at desc

$posts = Post::latest("id")->get();// or $posts = Post::orderBy("id", "desc")->get();

select * from posts order by id desc