如何在Laravel 7中优化查询语句? [英] How to optimize query statement in Laravel 7?

问题描述

我在屏幕快照中显示代码，因为我想向大家展示我的第43行

I show code in the screenshot, because I want to show you guys my line 43

实际代码在这里:

public function index()
{
    $inputs    = Request::all();
    $interval  = '';

    if(array_key_exists('interval', $inputs)){
        $interval  = $inputs['interval'];
    }

    switch ($interval) {
        case 'day':
        $visitors = Visitor::where('created_at', '>', now()->today())->paginate(20);;
        break;
        case 'week':
        $visitors = Visitor::where('created_at', '>', now()->subMonth())->paginate(20);;
        break;
        case 'month':
        $visitors = Visitor::where('created_at', '>', now()->subMonth())->paginate(20);;
        break;
        case 'year':
        $visitors = Visitor::where('created_at', '>', now()->subYear())->paginate(20);
        break;
        default:
        $visitors = Visitor::orderBy('updated_at', 'desc')->paginate(20);
        break;
    }

    return View::make('layouts.be.visitors.index', get_defined_vars());
}

我访问

http://app.test/visitor?interval=year

如您所见

Laravel Debugbar检测到我在 43

为什么2?这是预期的吗?

Why 2 ? Is this expected ?

我可以将其提高到1吗?

Can I improve this into 1 ?

请告知

推荐答案

第二次查询是通过 paginate()方法运行的.

second query is run by paginate() method.

如果您查看laravel官方文档中的分页响应，您将看到一个名为 total 的字段.这就是为什么该查询运行以获取记录总数的原因:

If you look at this pagination response from laravel official documentation you will see one field called total. That's why that query runs to get total number of records:

{
   "total": 50,
   "per_page": 15,
   "current_page": 1,
   "last_page": 4,
   "first_page_url": "http://laravel.app?page=1",
   "last_page_url": "http://laravel.app?page=4",
   "next_page_url": "http://laravel.app?page=2",
   "prev_page_url": null,
   "path": "http://laravel.app",
   "from": 1,
   "to": 15,
   "data":[
        {
            // Result Object
        },
        {
            // Result Object
        }
   ]
}

该从访问者WHERE created_at>处选择count(*)作为汇总.'2019-03-13 12:22:22 查询自动运行.

如果要手动设置，则必须使用 LengthAwarePaginator .

If you want to set it manually you have to use LengthAwarePaginator.

但是我不建议您这样做，因为只进行了一次查询

But I don't recomend to do that because of only one more query

请参阅此处有关 LengthAwarePaginator 的信息: https://github.com/laravel/ideas/issues/826

See here about LengthAwarePaginator: https://github.com/laravel/ideas/issues/826

这是优化代码的方法:

use DB;
use Carbon\Carbon;
...
public function index()
{
    $inputs    = Request::all();
    $interval  = '';

    if(array_key_exists('interval', $inputs)){
        $interval  = $inputs['interval'];
    }

    $visitors = Visitor::when(in_array($interval, ['day', 'month', 'week', 'year']), function($q) use ($interval){
        return $q->where('created_at', '>', Carbon::parse("now -1 $interval"));
    }, function($q) {
        return $q->orderBy('updated_at', 'desc');
    })->paginate(20);

    return View::make('layouts.be.visitors.index', get_defined_vars());
}

在此处查看有关 when 方法的更多信息: https://laravel.com/docs/7.x/queries#conditional-clauses

See more about when method here: https://laravel.com/docs/7.x/queries#conditional-clauses

希望这会有所帮助

这篇关于如何在Laravel 7中优化查询语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！