familytree365/backend

use Illuminate\Foundation\Auth\User as Authenticatable;
 
class User extends Authenticatable
{
    //
    public function userChats()
    {
        return $this->belongsToMany(Chat::class, ChatMember::class, 'user_id', 'chat_id')->withPivot('latest_read_msg');
    }
    //
    public function totalUnreadMessages(){
        $count = 0;
        $userChats = $this->userChats;
        foreach($userChats as $chat){
            $latestReadMsgId = $chat->pivot->latest_read_msg;
            $count += $chat->chatMessages()->where('id', '>', $latestReadMsgId)->count();
        }
        return $count;
    }
}

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
 
class CreateChatMembersTable extends Migration
{
    public function up()
    {
        Schema::create('chat_members', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('chat_id');
            $table->unsignedBigInteger('user_id');
            $table->unsignedBigInteger('latest_read_msg')->default(0);
            $table->foreign('chat_id')->references('id')->on('chats');
            //$table->foreign('latest_read_msg')->references('id')->on('chat_messages');
            $table->timestamps();
        });
    }
    //
}