Question 219753 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

  $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 by this Code

IlluminateDatabaseQueryException
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 result something like this

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

2

Answers


  1. 0 
    $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

    Login or Signup to reply.
  2. 0

    In order to get employee-wise tasks count in Laravel, you can follow these steps:

    Step 1: Set up the Database
    Start by creating a database table called "tasks" with the necessary columns, such as ‘id’, ’employee_id’, and ‘task_name’. Make sure to establish the relationship between employees and tasks through the ’employee_id’ column.

    Step 2: Define Models and Relationships
    Create two models: Employee and Task. In the Employee model, define a hasMany relationship with Task using the method:

    public function tasks()
{
return $this->hasMany(Task::class);
}

    And in the Task model, define a belongsTo relationship with Employee using:

    public function employee()
{
return $this->belongsTo(Employee::class);
}

    Step 3: Fetch Data from Database
    Write a query to fetch all employees along with their task counts. You can achieve this by utilizing Laravel’s Eloquent ORM capabilities. In your controller or service class, use this code snippet:

    use AppModelsEmployee;

$employees = Employee::withCount('tasks')->get();

    This will retrieve all employees from the database while also counting their associated tasks.

    Step 4: Display Results
    Now that you have retrieved employee-wise task counts, you can display them in your view or API response. Loop through each employee object obtained from step 3 and access its attributes like name and task_count.

    For example:

    @foreach($employees as $employee)
{{$employee->name}} - {{$employee->task_count}}


@endforeach

{{-- Or if it's an API response --}}
return response()->json(['employees' => $employees]);

    By following these steps, you’ll be able to retrieve employee-wise task counts efficiently using Laravel’s powerful ORM features.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search