如何使用Laravel Fluent COUNT列并使用每个ID（用户）的WHERE条件？

Question

i'm newbie to laravel. I want to count the number with where condition in column 'ket'. In this code, i've successfully applied to normal user (code 1), i want to applied the code 1 $total_tdkmsk $total_msk $total_telat into code 2. So this is what i've done.

Database Structure

Laravel Fluent Query

$id_alias = Auth::user()->id;
$tdkmsk = 'Tidak Masuk';
$msk = 'Masuk';
$total_tdkmsk = DB::table('presensi')
                    -> where('presensi.ket','=',$tdkmsk)
                    -> where('presensi.id_user','=',$id_alias)
                    -> count();
$total_msk = DB::table('presensi')
                    -> where('presensi.ket','=',$msk)
                    -> where('presensi.id_user','=',$id_alias)
                    -> count();
$total_telat = DB::table('presensi')
                    -> where('presensi.id_user','=',$id_alias)
                    -> sum('ketidakhadiran');

into this query

$items = DB::table('presensi')
            ->join('jabatan', 'presensi.id_user', '=', 'jabatan.id')
            ->join('users', 'presensi.id_user', '=', 'users.id')
            ->join('jadwal', 'presensi.jadwal', '=', 'jadwal.id')
            ->join('matakuliah', 'jadwal.id', '=', 'matakuliah.id')
            ->join('mahasiswakelas', 'jadwal.id_kelas', '=', 'mahasiswakelas.id_kelas')
            ->join('kelas', 'mahasiswakelas.id_kelas', '=', 'kelas.id')
            ->select('users.id AS id_users','users.name AS nama_users' ,'presensi.id AS id','jabatan.id AS id_jabatan','presensi.*', 'matakuliah.namamatakuliah','jabatan.nama_jabatan','kelas.nama_kelas','mahasiswakelas.tahunakademik',DB::raw('COUNT(*) as tidakada'))
            -> where('presensi.ket','=',$tdkmsk)
            ->orderby('presensi.id', 'DESC')
            ->groupby('presensi.id_user')
            ->get();

I get the result, but this is not what i'm expected. Any advice for this? thank you very much

Answer 1

For achieving what you just described below, you can do the following-

You should have a field in your database which specifies which user is having the administrator rights and which user is a normal user.

1. check if the logged in user is the administrator.

2. If he is the administrator, Get the data of all the users.

   if($checked){
     //fetch the data of all normal users.
   }
   

In your controller

$query = DB::table('users')->where([
  ['role', '=', 'admin'],  // you should have a role field in the database.
  ['user_id', '=', $id_alias],
])->get();

if($query){
  $total_tdkmsk = DB::table('presensi')
                -> where('presensi.ket','=',$tdkmsk)
                -> count();
  $total_msk = DB::table('presensi')
                -> where('presensi.ket','=',$msk)
                -> count();
  $total_telat = DB::table('presensi')
                -> where('presensi.id_user','=',$id_alias)
                -> sum('ketidakhadiran');
}

Edit the table names according to your database design. Also I'm not sure of the code written inside the if statement here. So check and debug it by yourself.

Also since you want to fetch the information of all the users, you should not pass user_id in the where clause because you are not having user id's of all the users stored in the variable.