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

Does this answer your question? [Laravel - Disable the "select count" on load when using paginate method](https://stackoverflow.com/questions/37729226/laravel-disable-the-select-count-on-load-when-using-paginate-method) — Johannes, Apr 13 '20 at 17:02

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

3

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 13 '20 at 17:02

Malkhazi Dartsmelidze

4,783
4
16
40

Oh ok, I will just ignore that for now, then. – code-8 Apr 13 '20 at 17:03
How do I access `"total"`, on my view ? I want to print it. – code-8 Apr 13 '20 at 17:05
You can use `$visitors->total()` – Malkhazi Dartsmelidze Apr 13 '20 at 17:08
I edited answer and look at it and use it if you prefer. If you find my answer helpful pleace mark it as accepted – Malkhazi Dartsmelidze Apr 13 '20 at 17:17
Thanks for your suggestion on refactoring my codes. I’ll definitely take it into consideration – code-8 Apr 13 '20 at 17:24
I would like to stick Laravel codes as possible, I noticed you use SQL codes as part of ur suggestion. Possible to suggest with pure laravel syntax? – code-8 Apr 13 '20 at 17:26
I edited code using carbon. You can also use `strtotime` method but since `now()` returns `Carbon` instance I did it with carbon. Don't forget to import it – Malkhazi Dartsmelidze Apr 13 '20 at 17:36
Thank-you for that, I tried your codes, I notice 1 more tiny issue. When I'm at `http://app.test/visitor?interval=week` and click on (2) on pagination, I kept redirecting to `http://app.test/visitor?page=2` . Keep in mind that I am in a weekly view. – code-8 Apr 13 '20 at 17:51
Is there a way to keep `?interval=week&page=2` ? – code-8 Apr 13 '20 at 17:52
Unfortunately there is no official way. you have to do it yourself. Look at this question: https://stackoverflow.com/questions/9666909/keeping-url-parameters-during-pagination – Malkhazi Dartsmelidze Apr 13 '20 at 18:17
It's okay, I found the solution to it. : https://stackoverflow.com/a/61193962/4480164 – code-8 Apr 13 '20 at 18:17

score 1 · Answer 2 · answered Apr 13 '20 at 17:06

1

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.

answered Apr 13 '20 at 17:06

Mondini

580
4
16

1

Maybe because `The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17 and will be removed in a future MySQL version` – Marcin Orlowski Apr 13 '20 at 17:09

How to optimize query statement in Laravel 7?

As you can see

2 Answers2

EDIT:

Linked