How to query with contain array in Laravel?-CodePudding

Hi I have table that contain two foreign key as below.

symptom_diseases table

disease_id symptom_id 
1            1
1            2
2            1
2            3

I would like to query with symptom_id array and return disease_id array. For example

symptom_id array = [1]  then return disease_id [1,2]
symptom_id array = [1,2]  then return disease_id [1]
symptom_id array = [1,3]  then return disease_id [2]
symptom_id array = [2,3]  then return null

I try to query using

whereIn

$diseaseArr = SymptomDisease::whereIn('symptom_id',$request->symptom)->pluck('disease_id')->toArray();

but I got wrong result. Any advice or guidance on this would be greatly appreciated, Thanks.

CodePudding user response：

Firstly the whereIn query will get all models which match any of the entries in the array. You seem to want models which match all of them.

A naïve approach to do this would be something like:

$diseaseArr = SymptomDisease::whereIn('symptom_id',$request->symptom)
    ->select('disease_id')
    ->groupBy('disease_id')
    ->having(\DB::raw('COUNT(*)'),'=',count($request->symptom))
    ->pluck('disease_id')->toArray();

This will get all disease ids that occur the exact number of times as the number of symptoms you are looking for.

However you have made a pivot table into a model. That is usually a bad approach. I suggest you go about this via your Disease model (assuming one exists and has the relationships correctly set-up).

In which case you could do:

$disease = Disease::whereHas('symptoms',function ($query) use ($request) {
   $query->whereIn('id', $request->symptom);
}, count($request->symptom))->get();

This should get all diseases which have all the symptoms specified in the $request->symptom array.