CODEWITHSUNDEEP
phpsqllaravel-8crud
Muhammad Fahrur Rizky
Muhammad Fahrur Rizky

Reputation: 11

Search through laravel relationship with filtered date

i search data 1 (this exist in database) within date range of 13-04-2022 to 14-04-2022 (there is no data in this date range). From this query that i used on my controller still return the data that contains a keyword that i search for (data 1). What i wanted is to return collection of data with the range of the date filter (which is nothing since i dont have any data from that date range). How to solve this problem? is there any best practices that i could implement for this search within the filtered date? this is my back-end :

$rekans = Rekan::query()
                    ->join('dokters','dokters.id','=','rekans.dokter_id')
                    ->join('pasiens','pasiens.id','=','rekans.pasien_id')
                    ->join('penyakits','penyakits.id','=','rekans.dokter_id')
                    ->join('treatments','treatments.id','=','rekans.treatment_id');

    if(request('from_date')){
        $rekans->whereDate('rekans.created_at','>=',request('from_date'));
    }
    if(request('to_date')){
        $rekans->whereDate('rekans.created_at','<=',request('to_date'));
    }
    if(request('search')){
        $rekans->where('berat','LIKE','%'.request('search').'%')
            ->orWhere('rekan_inv','LIKE','%'.request('search').'%')
            ->orWhere('suhu','LIKE','%'.request('search').'%')
            ->orWhere('hasil_pemeriksaan','LIKE','%'.request('search').'%')
            ->orWhere('anamnesa','LIKE','%'.request('search').'%')
            ->orWhere('pengobatan','LIKE','%'.request('search').'%')
            ->orWhere('kasus','LIKE','%'.request('search').'%')
            ->orWhere('dokters.nama_dokter','LIKE','%'.request('search').'%')
            ->orWhere('pasiens.nama','LIKE','%'.request('search').'%')
            ->orWhere('penyakits.nama_penyakit','LIKE','%'.request('search').'%')
            ->orWhere('treatments.nama_treatment','LIKE','%'.request('search').'%');
    }

this is my sql

select * from `rekans` inner join `dokters` on `dokters`.`id` = `rekans`.`dokter_id` inner join `pasiens` on `pasiens`.`id` = `rekans`.`pasien_id` inner join `penyakits` on `penyakits`.`id` = `rekans`.`dokter_id` inner join `treatments` on `treatments`.`id` = `rekans`.`treatment_id` where date(`rekans`.`created_at`) >= ? and date(`rekans`.`created_at`) <= ? and `berat` LIKE ? or `rekan_inv` LIKE ? or `suhu` LIKE ? or `hasil_pemeriksaan` LIKE ? or `anamnesa` LIKE ? or `pengobatan` LIKE ? or `kasus` LIKE ? or `dokters`.`nama_dokter` LIKE ? or `pasiens`.`nama` LIKE ? or `penyakits`.`nama_penyakit` LIKE ? or `treatments`.`nama_treatment` LIKE ? order by `rekans`.`created_at` desc

Upvotes: 0

Views: 156

Answers (1)

Muhammad Fahrur Rizky
Muhammad Fahrur Rizky

Reputation: 11

Thanks again for the refrence that you gave me, to solve my problem i change my backend for the search and date filter to this

if(request('from_date')){
        $rekans->whereDate('rekans.created_at','>=',request('from_date'));
    }
    if(request('to_date')){
        $rekans->whereDate('rekans.created_at','<=',request('to_date'));
    }
    if(request('search')){
        $rekans->where(function ($query){
            $query->where('rekans.berat','LIKE','%'.request('search').'%')
            ->orWhere('rekans.rekan_inv','LIKE','%'.request('search').'%')
            ->orWhere('rekans.suhu','LIKE','%'.request('search').'%')
            ->orWhere('rekans.hasil_pemeriksaan','LIKE','%'.request('search').'%')
            ->orWhere('rekans.anamnesa','LIKE','%'.request('search').'%')
            ->orWhere('rekans.pengobatan','LIKE','%'.request('search').'%')
            ->orWhere('rekans.kasus','LIKE','%'.request('search').'%')
            ->orWhere('dokters.nama_dokter','LIKE','%'.request('search').'%')
            ->orWhere('pasiens.nama','LIKE','%'.request('search').'%')
            ->orWhere('penyakits.nama_penyakit','LIKE','%'.request('search').'%')
            ->orWhere('treatments.nama_treatment','LIKE','%'.request('search').'%');
        });
            
    }

Upvotes: 1

Related Questions