CODEWITHSUNDEEP
laraveleloquentlaravel-5.7
Webtect
Webtect

Reputation: 839

Laravel return eloquent result where it matches all ids not just a couple

I can't wrap my head around this for some reason even though it seems the answer is right in front of me.

I have 3 tables, rentals, amenitiesand amenities_rentals

Amenities are things like air conditioning, staff ect.

The amenities_rentals has rentail_id, amenities_id. This table is basically a reference table that connects rentals to amenities.

In a search a user can narrow down rentals by checking off certain amenities. As of now its giving me all the rentals that have at leas one of the checked off options. I need it to narrow down to only rentals that have ALL of the chosen amenities. This is what I have, this is not only not pulling the info correct but I know there is a better way to do it.

if($request->has('amenities')) 
{
    $rental_ids = AmenitiesRentals::select('rental_id')
                    ->whereIn('amenities_id',$request->amenities)
                    ->distinct()
                    ->get();
    $builder->whereIn('rentals.id', $rental_ids->toArray());
}

The rest of the builder works fine.

Upvotes: 1

Views: 1547

Answers (3)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25936

As Kamal Paliwal suggested, define a BelongsToMany relationship in your Rental model:

public function amenities() {
    return $this->belongsToMany(Amenity::class, 'amenities_rentals', 'rental_id', 'amenities_id');
}

Then use whereHas():

$amenities = $request->amenities;

$rentals = Rental::whereHas('amenities', function ($query) use ($amenities) {
    $query->whereIn('amenities.id', $amenities);
}, '=', count($amenities))->get();

Upvotes: 5

Mohamed El-Refaie
Mohamed El-Refaie

Reputation: 855

You should select rentals that has the count of amenities ids same as the size of amenities array from your request by grouping rentals by amenities count.

Here is an example:

$amenities_ids = $request->amenities;
$amenities_ids_count = count($amenities_ids);

$rental_ids = AmenitiesRentals::selectRaw("COUNT(amenities_id) as amenities_count, rentail_id")
                ->whereIn('amenities_id', $amenities_ids)
                ->groupBy('rentail_id')
                ->having('amenities_count', $amenities_ids_count)
                ->get();

Upvotes: 0

Kamal Paliwal
Kamal Paliwal

Reputation: 1301

You should create a many relationship between rentals and amenities like below:

public function amenities()
{
    return $this->belongsToMany(\AmenityModel::class, 'amenities_rentals', 'rental_id', 'amenities_id');
}

Then while applying filter you can add it in this way:

$amenitiesIds = $request->amenities;
RentalsModel::with('amenities')->whereHas('amenities', function ($q) use ($amenitiesIds) {
    $q->whereIn('amenities_id', $amenitiesIds);
})

Hope this might help you.

Upvotes: 0

Related Questions