Laravel按员工统计月度任务数及集成柱状图的实现问题

问题详情 #

• 需求：在Laravel中统计每位员工的月度任务数量，将结果用于柱状图展示；已实现月度任务总数统计，需按用户维度拆分（获取对应员工的姓名及任务数）
• 尝试的错误代码：

$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();

• 抛出的错误：

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))

数据表结构 #

User表迁移代码

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表迁移代码

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();
    });
}

• 期望结果格式：

$labels = ['EmpName 1', 'EmpName 2', 'EmpName 3', 'EmpName 4'];
$count_of_each_user_task = ['5', '8', '2', '6'];

问题分析与解决方案 #

1. 原代码错误原因 #

原代码中where(['users.role_id', '=', auth()->user()->role_id])的写法不符合Laravel查询构建器的数组格式要求，正确的数组式where条件应为键值对['users.role_id' => auth()->user()->role_id]，错误写法导致SQL解析时将数组索引当作字段名，引发“Unknown column '0'”异常。

2. 正确的查询代码 #

要实现按员工维度统计月度任务数，需调整查询逻辑：关联用户表获取姓名、按用户分组统计任务数、筛选年份（及可选月份）：

$userTasks = DB::table('tasks')
    ->select('users.name', DB::raw('COUNT(tasks.id) as task_count'))
    ->join('users', 'tasks.assigned_to', '=', 'users.id')
    ->where('users.role_id', auth()->user()->role_id)
    ->whereYear('tasks.created_at', date('Y'))
    // 如需统计指定月份，取消注释并替换为目标月份（1-12）
    // ->whereMonth('tasks.created_at', 10)
    ->groupBy('users.id', 'users.name')
    ->get();

3. 提取图表所需数据 #

从查询结果中直接提取员工姓名和任务数：

$labels = $userTasks->pluck('name')->toArray();
$count_of_each_user_task = $userTasks->pluck('task_count')->toArray();

4. 扩展：包含无任务的员工 #

如果需要展示当月无任务的员工（任务数为0），需改用左连接并调整查询结构：

$userTasks = DB::table('users')
    ->select('users.name', DB::raw('COUNT(tasks.id) as task_count'))
    ->leftJoin('tasks', function($join) {
        $join->on('users.id', '=', 'tasks.assigned_to')
             ->whereYear('tasks.created_at', date('Y'))
             // ->whereMonth('tasks.created_at', 10); // 指定月份
    })
    ->where('users.role_id', auth()->user()->role_id)
    ->groupBy('users.id', 'users.name')
    ->get();

内容的提问来源于stack exchange，提问作者Abhijith