I have a table that houses hotel ids and amenity ids. When a user chooses amenities i need to only pull hotels that have ALL of the chosen amenities. As of now it gets me all hotels that have at least one. How can i change this builder query to handle it so it does not include hotels that do not have all of them.

$builder = Hotels::query(); 
$builder->select('hotels.id','hotels'.'hotels_name')
$request_amenities = $request->amenities;
$builder->join('amenities_hotels', function ($join) use($request_amenities) {
    $join->on('amenities_hotels.hotel_id', '=', 'hotel.id')
            ->whereIn('amenities_hotels.amenities_id', $request_amenities);
});

CodePudding user response：

It's something like WhereAll you need ...

you have to add whereHas, for every Item in your array.

 $builder = Hotels::query();
        $builder->select('hotels.id', 'hotels' . 'hotels_name');
        $request_amenities = $request->amenities;
        if($request_amenities!=null)
        {
            for ($i = 0; $i < $this->count($request_amenities); $i  ) {
                $builder = $builder->whereHas('amenitiesHotels', function ($query) use ($i, $request_amenities) {
                    $query->where('amenities_hotels.amenities_id', $request_amenities[$i]);
                });
        }

see more about where in all in this question

CodePudding user response：

Hotels model.

// ...

    public function amenities(): BelongsToMany
    {
        return $this->belongsToMany(
            Amenities::class,
            "amenities_hotels"
        );
    }

//  ...

Query to run.

Hotels::select(['hotels.id','hotels'.'hotels_name'])

->whereHas('amenities', function ($query, $request_amenities) {

    $query->whereIn('amenities.id', $request_amenities);

}, '=', count($request_amenities))->get();

The above query tells Laravel to load the hotels that have count($request_amenities) amenities relation records when the amenities relation records are filtered by the given array of IDs.

Resource: "Where Has All" Functionality in Laravel