Home / Questions / 556385
Accepted
Kevin Carrascal
Asked: 2022-09-11 19:49:08 +0800 CST 2022-09-11 19:49:08 +0800 CST

How to perform a count query in Laravel

  • 772

I need help creating a query or some way to do a count. Context:

I have two tables:
user: where I have my users or workers
work: where I will assign a task to a user with statuses (complete, in process, error, etc).

My problem arises when I want to pass my employees in a select to my view: I can do that easily, but at the same time I need to know the jobs that each employee has in progress, example:

    <select class="form-control mb-2" name="" required>
      <option selected> Seleccione el trabajador </option>
      <option value="">Jose - 2 trabajos asignados en curso</option>
      <option value="">Luis - 5 trabajos asignados en curso</option>
</select>

So that in my view my workers are seen that way with the number of jobs assigned but from least to greatest.

jquery

2 Answers

  1. Best Answer

    Assuming the structure of user is

    • id
    • Name

    and the work

    • go

    • id_user

    • condition

      $query = DB::table('user', 'u')->select('count(t.id_user) as numberofjobs', 'u.name as name')->join('job as t', ' u.id','=','t.id_user')->groupBy('name')->orderBy('numberofjobs','desc')->get();

    This will return https://isstatic.aoverflow.com/71AO3.jpg

    And if you want me to also bring you those who don't have jobs, just put a Left JOIN

    • 0

  2. it's very simple in case you need to add additional text you can use a foreach

    public function datosUsers(){

    $users = \App\User::select('id', 'name')->limit(4)->get();
    foreach ($users as $key => $user) {
      
      $coutn = \App\TrabajoModel::where('users_id', $user['id'])->count();
      $users[$key]['text'] = $user['name'] . ' - ' . $coutn . ' trabajos asignados en curso';

    }

    return response()->json($users, 202);

}

    with this code the following JSON votes for me

    [
{
"id": 1,
"name": "msaavedra",
"text": "msaavedra - 0 trabajos asignados en curso"
},
{
"id": 2,
"name": "wnarvaez",
"text": "wnarvaez - 1 trabajos asignados en curso"
},
{
"id": 3,
"name": "will",
"text": "will - 4 trabajos asignados en curso"
},
{
"id": 4,
"name": "operador",
"text": "operador - 8 trabajos asignados en curso"
}
]
    • 0