How to get Employee wise tasks count in laravel along

I want to get each employees task count in a month and integrate into bar chart,i managed to get total count of tasks based in a month i am confused how to do that user based,let's say i have 10 employees,i need how many tasks each user have and show into a barchart along with task count and name of the employees

i did kind of like this i am not sure the logic is correct or not,i have no idea why i did this.but help would be appreciated

<!-- language-all: php -->

$user_tasks = DB::table('tasks')
    ->select(DB::raw("MONTHNAME(tasks.created_at) as month_name"))
    ->join('users', 'tasks.assigned_to', '=', 'users.id')
    ->where(['users.role_id', '=', auth()->user()->role_id])
    ->whereYear('tasks.created_at', date('Y'))
    ->groupBy(DB::raw("MONTHNAME(tasks.created_at)"))
    ->get();

 
$labels = $user_tasks->keys();
$data = $user_tasks->values();

Error thrown

> Illuminate\Database\QueryException
SQLSTATE[42S22]: Column not found: 1054 Unknown column '0' in 'where clause' (SQL: select MONTHNAME(tasks.created_at) as month_name from tasks inner join users on tasks.assigned_to = users.id where (0 = users.role_id and 1 = = and 2 = 1) and year(tasks.created_at) = 2023 group by MONTHNAME(tasks.created_at))

Employee/User Migration

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('username');
        $table->string('email')->nullable()->unique();
		$table->date('date_of_joining')->nullable();
        $table->date('dob')->nullable();
        $table->string('mobile')->nullable();
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->integer('role_id')->nullable();
        $table->rememberToken();
        $table->softDeletes();
        $table->timestamps();
		$table->integer('status')->default(0);
    });
}

Tasks migration

public function up()
{
    Schema::create('tasks', function (Blueprint $table) {
        $table->id();
        // $table->integer('task_id');
        $table->string('title');
        $table->text('description')->nullable();
        $table->timestamp('deadline_date')->nullable();
        $table->integer('assigned_by');
        $table->integer('assigned_to');
        $table->integer('follow_up')->nullable();
        $table->integer('priority')->default(1);
        $table->integer('work_status')->default(1);
        $table->integer('status')->default(0);
        $table->string('remarks')->nullable();
        $table->integer('updated_by')->nullable();
        $table->timestamp('status_updated_date')->nullable();
        $table->index('id');
        $table->timestamps();
    });
}

In this assigned_to column saves the user_id (id in user table). I want to get the result like this

lables varibale = ['EmpName 1', 'EmpName 2', 'EmpName 3', 'EmpName 4'];
count_of_each_user_task = ['5', '8', '2', '6']

$user_tasks = DB::table('tasks')
    ->selectRaw("CONCAT(users.name, ' (EmpName ', users.id, ')') as employee_name, COUNT(*) as task_count")
    ->join('users', 'tasks.assigned_to', '=', 'users.id')
    ->where('users.role_id', '=', auth()->user()->role_id)
    ->whereYear('tasks.created_at', date('Y'))
    ->groupBy('users.id', 'users.name')
    ->get();

$labels = $user_tasks->pluck('employee_name')->toArray();
$count_of_each_user_task = $user_tasks->pluck('task_count')->toArray();

You have a couple of issues with your query try this one. Let me one if it will not work