Count the number of posts with a certain value (like/dislike)?

Question

MySQL

I have a post table. There is a post rating table of this kind:
| id, post_id, user_id, like |
If the like value is zero, you get a dislike, and if it is positive, you get a like. The functionality of removing a like/dislike is deleting a record in the table.
Now I need to count the number of likes and dislikes, and also determine if the user rated the post.
How would it be better to do it? For example, with connections in the forehead, I did this in the post model:

public function likes()
    {
        return $this->hasMany('App\Rating')->where('like', '1')->count();
    }

    public function dislikes()
    {
        return $this->hasMany('App\Rating')->where('like', '0')->count();
    }

The soul senses the bydlocode and the potential load when recalculating the values ​​for each post.
I was thinking about adding a couple of like/dislike values ​​to the posts table in addition to the rating table. And change them with every like/dislike. This will eliminate the need for constant recalculation, but looks like a crutch.

Answer 1

This is not a crutch, it is quite normal practice. Usually, a trigger is hung on the likes table, which changes the value in the main table.

Answer 2

it would be better to make a table with user id and post id + 1 more like column, where 1 will be written if the user likes it and 0 if it is dislike, and then check everything by mysql

Answer 3

public function likes()
{
    return $this->hasMany('App\Rating')->where('like', '1');
}

public function dislikes()
{
    return $this->hasMany('App\Rating')->where('like', '0');
}

$post->withCount('likes');
$post->withCount('dislikes');

https://laravel.com/docs/5.4/eloquent-relationship...