How to query with contain array in Laravel?

Question

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.

Query seems correct, can you tell me what $request->symptom actually contains? — geertjanknapen
– geertjanknapen, Commented Jul 12, 2022 at 6:04
Dear geertjanknapen, $request->symptom is array set from view. — joenpc npcsolution
– joenpc npcsolution, Commented Jul 12, 2022 at 6:06
Dear nice_dev, once I pass whereIn('symptom_id',[1,2]) then I got disease_id [1,2]. This is wrong, because symptom_id [1,2] is only belong to disease_id = 1. So it should return [1] instead. — joenpc npcsolution
– joenpc npcsolution, Commented Jul 12, 2022 at 6:09
This seems to be a "Many To Many" relationship. Laravel provides an easy way to access this data. You can check the documentation : laravel.com/docs/9.x/eloquent-relationships#many-to-many — paulin-crtn
– paulin-crtn, Commented Jul 12, 2022 at 7:02

apokryfos · Accepted Answer · 2022-07-12 12:18:19Z

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.

Collectives™ on Stack Overflow

How to query with contain array in Laravel?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related