Laravel too many queries while calling model methods

Question

So I load all my budgets like so:

$budgets = auth()->user()->budgets()->get(); // With some eager loading and ordering, not needed for this example.

The problem lies in the way I perform some checks in my view. Take for example this snippet:

@foreach($budgets as $budget)

    if($budget->getRemainingAmount() < 1) // getRemainingAmount() performs 6 queries
        class="danger"
    @endif

@endforeach

Now I have one main issue with the above approach. I don't mind the 6 queries, that's just how it works behind the scenes which is totally fine. The thing is, each time I call the method within the view, the 6 queries are run again, which means they are duplicated over and over.

What I want to do is, for example, is include that method in my query and just assign it to a variable.

$budgets = auth()->user()->budgets()->doSomethingToAssignThatMethodToSomeVariable()->get();

Let's just say for now the method does the following simple thing:

public function getRemainingAmount()
{
    return 100;
}

Now, how can I assign the method getRemainingAmount() to a variable called $remaining while performing my query? Or, is there a better way to approach this perhaps? In my view, I just want to be able to change this:

if($budget->getRemainingAmount() < 1)

To this (for example):

if($remaining < 1)

So that I can perform the check multiple times without having to run 6 queries over and over, each time I call the method.

Any thoughts on how to achieve this in a simple manner? I actually have multiple methods that result in the same issue (right now my debugbar says: 66 statements were executed, 41 of which were duplicated, 25 unique). Obviously I want to remove the duplication.

score 1 · Accepted Answer · 2018-01-26 18:27:56Z

1

You would probably be interested in using the cache for storing repetitive queries results.

You may even pass a Closure as the default value. The result of the Closure will be returned if the specified item does not exist in the cache. Passing a Closure allows you to defer the retrieval of default values from a database or other external service:

$value = Cache::get('key', function () {
    return DB::table(...)->get();
});

You have control over how long results are cached and it can drastically improve performance and reduce db load.

So for you, accessing some-budget-result

$result = Cache::get('some-budget-result', function () {
    return auth()->user()->budgets()->doSomethingToAssignThatMethodToSomeVariable()->get();
});

would run the query once, and each subsequent time you access that cache item, same results, no additional queries.

https://laravel.com/docs/5.5/cache#retrieving-items-from-the-cache

edited Jan 26, 2018 at 18:27

answered Jan 26, 2018 at 18:22

user320487

Sign up to request clarification or add additional context in comments.

1 Comment

Hardist Over a year ago

I was actually using caching before, and probably it's what I should be using. So, thanks for the answer :)

Collectives™ on Stack Overflow

Laravel too many queries while calling model methods

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related