How to COUNT Column and use WHERE condition by each ID(user) with Laravel Fluent?

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

ujjwal verma · Accepted Answer · 2018-07-22T12:25:08.310

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.

check if the logged in user is the administrator.
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.

yap its right i know it because i use it for normal user, but i dont know what variable that can make administrator see the $total_msk, $total_tdkmsk, $total_telat from every normal user — Pratama, Jul 22 '18 at 05:31
you want to give administrator access to the `$total_msk, $total_tdkmsk, $total_telat` of a normal user? — ujjwal verma, Jul 22 '18 at 05:35
yap simply its right, then i want it shown on 1 view of table the $total_msk, $total_tdkmsk, $total_telat of every normal users — Pratama, Jul 22 '18 at 05:37
hmm, i've done the solution given. ok maybe my question is not clear, i've edited my question — Pratama, Jul 22 '18 at 06:23

How to COUNT Column and use WHERE condition by each ID(user) with Laravel Fluent?

1 Answers1