How to calculate the rating of a laravel post-CodePudding

I need to calculate the server rating. I have tables server_user_likes where server_id and user_id are stored, server likes from users are stored in it. 1 like = 1 line in ServerUserLike. With server_id and user_id values

        Schema::create('server_user_likes', function (Blueprint $table) {
            $table->engine = 'InnoDB';
            $table->id();
            $table->unsignedBigInteger('server_id');
            $table->string('user_id');
            $table->index('server_id', 'server_user_likes_server_idx');
            $table->foreign('server_id', 'server_user_likes_server_fk')->on('servers')->references('id');
            $table->timestamps();
        });

Model ServerUserLike

    public function server()
    {
        return $this->hasMany(Server::class);
    }

I counted the number of server likes

        $ratingCounts = DB::table('server_user_likes')
            ->where('server_id', $server->id)
            ->count();

And I counted the total number of likes, all servers

        $allRatingCounts = DB::table('server_user_likes')
            ->count();

And now I need to somehow calculate the server's rating, the so-called top 10 Subtraction will not help, because servers and users can increase every time

p.s Server is the same as Post.

CodePudding user response：

I recommend to use this query to get top 10 server/post based on likes.

// here I use eloquent with model ServerUserLike

$results = ServerUserLike::query()
  ->select('server_id')
  ->selectRaw('COUNT(*) AS likes_count')
  ->groupBy('server_id')
  ->orderByDesc('likes_count')
  ->take(10)
  ->get();

The result will be list of 10 top servers [server_id, likes_count]

And as requested, this is an example how you can show top 10 in top_server.blade.php

@foreach ($results as $topServer)
<tr>
  <td>{{ $loop->iteration }}</td>
  <td>{{ $topServer->server_id }}</td>
  <td>{{ $topServer->likes_count }}</td>
</tr>
@endforeach

$loop->iteration will show number starting from 1 to 10.
https://tutsforweb.com/loop-variable-foreach-blade-laravel/