Laravel Eloquent match to all parameters of join

0
votes

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);
});
2
laraveleloquentlaravel-7
It's a bit hard to say without knowing what $builder is. But you can take a look at whereHas – SuperDJ
builder is the query builder. Starts like $builder = Hotels::query(); $builder->select('hotels.id','hotels'.'hotels_name') – Webtect
does this give you the desired result? $hotels = Hotel::select(['id','hotels_name'])->whereHas('amenities', function($query) use($request){return $query->whereIn('id', $request->amenities);})->get()? Adding this as a comment as I don't know the relevant names of relations, tables or columns. – SuperDJ
i get Call to undefined method App\Hotels::AmenitiesHotel() which leaves me to believe I would need to set up a relationship within that Model? – Webtect

2 Answers

0
votes

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

0
votes

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