What is the most optimized way to generate statistics in Laravel without recalculating everything every time a page is loaded?
Hi everyone!
I have an optimisation question for you. I have an articles table, a comments table and a reviews table. My reviews table contains this kind of data:
| id | article_id | comment_id | expectation | review |
|----|------------|------------|-------------|---------------------------------------------------------------------------------------|
| 1 | 1 | 1 | exceeded | a:4:{i:0;s:5:"video";i:1;s:9:"exercices";i:2;s:8:"examples";i:3;s:12:"explanations";} |
| 2 | 1 | 2 | yes | a:4:{i:0;s:12:"explanations";i:1;s:8:"examples";i:2;s:3:"pep";i:3;s:5:"video";} |
My serialize data looks like that:
array(4) {
[0]=>
string(12) "explanations"
[1]=>
string(8) "examples"
[2]=>
string(3) "pep"
[3]=>
string(5) "video"
}
Note that "exceeded", "yes", "explanations", etc. they all come from a configuration field in the Voyager administration.
I would like to get statistics from the reviews table for a specific article. For the expectation field, I have something like that:
public function reviews_count()
{
$reviews_categories = json_decode(setting('article.expectations'), true);
foreach ($reviews_categories as $k => $v) {
$reviews_count[$k] = $this->hasMany(ArticleReview::class)->where('expectation', $k)->count();
}
return $reviews_count;
}
- This works very well, but wouldn't it be very time consuming if I had to recalculate every time a user viewed the page and I had 30,000 reviews?
- How would you do this calculation? Can MySQL or Laravel cache this kind data? If so, would you have to update the caches with each new comment or create a command that regularly updates the caches?
And now, about the review field... I have serialized data: impossible to do a ->count(). How should I retrieve these statistics with the least amount of computation possible?
1
Hello,
Just came across this question. Yes, you could do that by storing the result of the query in cache, like Redis for example.
This will look as follows:
$value = Cache::remember('users', $seconds, function () {
return DB::table('users')->get();
});
You will only need to change the logic in the closure and the seconds that you want this to be cached for.
Alternatively, you can do this with Materialize which lets you define materialized views that are kept up to date incrementally, so you can retrieve real-time data at any time without having to recalculate the results each time.
Best,
Bobby
Hello,
Just came across this question. Yes, you could do that by storing the result of the query in cache, like Redis for example.
This will look as follows:
$value = Cache::remember('users', $seconds, function () {
return DB::table('users')->get();
});
You will only need to change the logic in the closure and the seconds that you want this to be cached for.
Alternatively, you can do this with Materialize which lets you define materialized views that are kept up to date incrementally, so you can retrieve real-time data at any time without having to recalculate the results each time.
Best,
Bobby