How to optimize query statement in Laravel 7?

Question

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

The actual code is here :

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());
}

I visit

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

As you can see

Laravel Debugbar detected that I did 2 queries on line 43

Why 2 ? Is this expected ?

Can I improve this into 1 ?

Please advise

Answer 1

second query is run by paginate() method.

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
        }
   ]
}

That select count(*) as aggregate from visitors WHERE created_at > '2019-03-13 12:22:22 query runs automatically.

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

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

EDIT:

Here is how you can optimize your code:

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());
}

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

Hope this helps

Answer 2

It's because of the pagination object. You want to run the LIMIT, but still know how many rows would have been returned without the LIMIT. There is a way using mysql function FOUND_ROWS() but Laravel use the two queries way to count.