There are three tables

1. workshops (column: id,name,phone,email)
2. services (column: id, name)
3. service_workshop (column: id, workshop_id, service_id, active)

Between tables workshops and services relationships many to many. For filtering in the model i use local scopes

How to write a request correctly?

CodePudding user response：

Can you please share the full rows of the result? If I am not mistaken. The result is valid because it's return workshop_id 2 due the condition of the first row or id (117)

The value is : service_id : 1 workshop_id : 2 active : 1

which means, you have workshop_id 2 with active value

Remember you add whereIn [1,2] which is valid as long as the service_id is 1 or 2

Update :

To make sure it's only return all workshop_id that contains all service_id with status active you need to group it and add having condition

Try this :

return $query->when(count($services), function ($query) use ($services) {
        $query->whereIn('id', function ($query) use ($services) {
            $query->select('workshop_id')
                ->from('service_workshop')
                ->groupBy('workshop_id')
                ->where('active', true)
                ->whereIn('service_id', $services)
                ->having(DB::raw('count(*)', >=, count($services)));
        });
    });

MySQL Query :

SELECT workshop_id FROM service_workshop WHERE service_id IN (1,2) AND active = 1 GROUP BY workshop_id HAVING COUNT(*) >= 2

Note : Change the condtion of having count to the length of the service condition