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

Laravel 7 query whereBetween 2 month

I want to find data between 2 month, for example data between months 8 and 9.

Edit: The issue is in this line

else if ($bulan1 != "semua" && $bulan2 != "semua") {
   $query->whereBetween(DB::raw('MONTH(kred_kep_aktf_kompetensi.mulai)'), [8, 9]);
}

And this is the full code of the function

public function load_kompetensi(Request $request) {
        $jenis_kegiatan = $request->input('jenis_kegiatan');
        $bulan1 = $request->input('bulan1', 'semua');
        $bulan2 = $request->input('bulan2', 'semua');
        $tahun = $request->input('tahun', 'semua');
        $unit = $request->input('unit', 'semua');

        try {
            $query = DB::connection('database')
                ->table('kred_kep_aktf_kompetensi')
                ->join('Karyawan', 'kred_kep_aktf_kompetensi.nid_karyawan', '=', 'Karyawan.NID')
                ->join('kred_jenis_kegiatan', 'kred_kep_aktf_kompetensi.id_jenis_kegiatan', '=', 'kred_jenis_kegiatan.id')
                ->join('kred_jenis_peran', 'kred_kep_aktf_kompetensi.id_peran', '=', 'kred_jenis_peran.id')
                ->select('kred_kep_aktf_kompetensi.*', 'Karyawan.Nama', 'kred_jenis_kegiatan.nama_kegiatan', 'kred_jenis_peran.nama_peran');

            if (session('jabatane') == "Pelaksana") {
                $query->where('kred_kep_aktf_kompetensi.nid_karyawan', session('nidkaryawan'));
            } else if (session('jabatane') == "Subag/Kanit") {
                $query->where('kred_kep_aktf_kompetensi.id_unit_kerja', session('unite_id'));
            }

            if ($unit != "semua") {
                $query->where('kred_kep_aktf_kompetensi.id_unit_kerja', $unit);
            }

            if ($jenis_kegiatan && $jenis_kegiatan != 'semua') {
                $query->where('kred_kep_aktf_kompetensi.id_jenis_kegiatan', $jenis_kegiatan);
            }


            if ($bulan1 != "semua" && $tahun != "semua") {
                $query->whereMonth('kred_kep_aktf_kompetensi.mulai', $bulan1)
                      ->whereYear('kred_kep_aktf_kompetensi.mulai', $tahun);
            } else if ($bulan1 != "semua" && $bulan2 == "semua") {
                $query->whereMonth('kred_kep_aktf_kompetensi.mulai', $bulan1);
            } else if ($bulan1 != "semua" && $bulan2 != "semua") {
                $query->whereBetween(DB::raw('MONTH(kred_kep_aktf_kompetensi.mulai)'), [8, 9]);
            } else if ($tahun != "semua") {
                $query->whereYear('kred_kep_aktf_kompetensi.mulai', $tahun);
            }

            $files = $query->orderBy('kred_kep_aktf_kompetensi.id', 'asc')->get();

            foreach ($files as $file) {
                $file->mulai = Carbon::parse($file->mulai)->format('d-m-Y');
                $file->akhir = Carbon::parse($file->akhir)->format('d-m-Y');
            }

        } catch (Throwable $th) {
            throw $th;
        }

        return view("keperawatan.list_aktf_kompetensi_load", ['files' => $files]);
    }

And this is the format of kred_kep_aktf_kompetensi.mulai is (YYYY-MM-DD)

2024-08-09 06:51:30.000

What I have tried is making a query in SQL with the following query:

SELECT * FROM kred_kep_aktf_kompetensi
WHERE (MONTH(mulai) BETWEEN 8 AND 9);

and the result is same as what I want.
Thank you!

Tags:

2

Answers


  1. 0 
    else if ($bulan1 != "semua" && $bulan2 != "semua") {
   $query->whereBetween(DB::raw('MONTH(kred_kep_aktf_kompetensi.mulai)'), [8, 9]);
}

    The problem with above query is that Laravel’s whereBetween() method expects the first argument to be a column name, not a raw SQL expression.

    So instead try this approach with whereRaw.

    else if ($bulan1 != "semua" && $bulan2 != "semua") {
       $query->whereRaw('MONTH(kred_kep_aktf_kompetensi.mulai) BETWEEN ? AND ?', [8, 9]);
    }

    For more information about whereRaw check the docs-link here

    Login or Signup to reply.
  2. 0

    It’s all about the where clause and also the structure of your database tables. If you wanna use where between it required two values to set the range between them but keep in mind also the year for example if you have two rows with date like (2024-06-10 AND 2023-07-10) this will be included also inside your result. Better query maybe if you used DATE_SUB as MySQL function for something like this:

    SELECT col1, col2, col3 FROM tbl_name -> WHERE DATE_SUB(YOUR_DESIRED_DATE, INTERVAL 30 DAY) <= mulai
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search