CODEWITHSUNDEEP
laraveleloquentlaravel-7
Beusebiu
Beusebiu

Reputation: 1513

Laravel query with relationship where have all ids

I try to do a query where I get a room where I have all the services.

With my current code I get the room that has at least one of the services from the array, because I am using whereIn.

$rooms = Room::select([
    'rooms.id',
    'rooms.available_from',
    'rooms.available_till',
    'rooms.description',
    'rooms.room_type_id',
    'room_types.name as room_types_name',
])
    ->leftJoin('room_types', 'room_types.id', 'rooms.room_type_id')
    ->with('services','reserved_rooms')
    ->when($request->services, function ($query) use ($request) {
        $query->whereHas('services', function ($q) use ($request) {
            $q->whereIn('id', $request->services);
        });
    })
    ->orderByDesc('id')
    ->get();

Upvotes: 1

Views: 1689

Answers (2)

Yasin Patel
Yasin Patel

Reputation: 5721

If you want to match all services, you can use for loop to create AND query.

Try below code:

->when($request->services, function ($query) use ($request) {
    foreach ($request->services as $key => $service) {
        $query->whereHas('services', function ($q) use ($service) {
            $q->where('id', $service);
        });
    }
})

Upvotes: 2

Mihai
Mihai

Reputation: 26784

->when($request->services, function ($query) use ($request) {
        $query->whereHas('services', function ($q) use ($request) {
            $q->selectRaw('count(distinct id)')->whereIn('id', $request->services);
        }, '=', count($request->services)
);
    })

Changed it,not tested but this is the general idea: get the rows where the COUNT of distinct ids is equal to the length of array.The array should be unique.

Upvotes: 0

Related Questions