'Method whereBetween does not exist' when using cached query.

Still relatively new to Laravel (5.5) so I think I'm not understanding this correctly.

I'm caching a database query in Redis using the following:

$log = Cache::remember('log'.auth()->id(), 10, function () {
                return Log::select(['id', 'clientip', 'token', 'timestamp'])->get();
            });

I want to perform a whereBetween on the cached result and have tried:

$logquery = Cache::get('log'.auth()->id());
$now = Carbon::now();
$to = Carbon::parse($request->get('daterange'));
$logrange = $logquery->whereBetween('timestamp', [$to,$now]);

But this fails with Method whereBetween does not exist in my json response.

What am I doing wrong/not understanding?

Any help appreciated.

tykus

8 years ago

Best Answer

Level 104

You have a collection rather than a Builder instance; there is no whereBetween method available on Collection. You can chain two where methods:

$logquery->where('timestamp', '>=', $to)->where('timestamp', '<', $now);

or you can use a filter

$logquery->filter(function ($log) {
    return $log->timestamp '>= $to && $log->timestamp < $now; 
});

click

8 years ago

Level 35

a quick note on doing it like this. If you have 100.000 log records in your database and you have 100 users you are caching 100.000 * 100 = 1.000.000 records in your cache.

I do not know the use case but it feels inefficient.

2 likes

ElijahPaul

8 years ago

Level 1

@tykus Awesome, thank you.

I need to read up on Collections again, the error seems so obvious now!

ElijahPaul

8 years ago

Level 1

@m-rk I believe you're right.

I don't think I will end up using this, but still wanted to understand why it wasn't working.

Looking at various ways of reducing DB queries and caching whatever possible (if it improves overall performance and scales of course).

click

8 years ago

Level 35

I undestand but caching results in combination with the option to filter is quite complicated. In general SQL is better at filtering and sorting data than PHP.

ElijahPaul

8 years ago

Level 1

@m-rk yeah, I'm slowly figuring that out.

The DB (postgres) has ≈ 20,000 records at the moment, and all the 'fiddling' I've done so far regarding caching + filtering has negatively impacted performance.

Overall query and filtering performance is actually pretty good as it is. So I may just leave this one alone for now.

Still learning, so I appreciate the input.

Please or to participate in this conversation.