Reputation: 839
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
, amenities
and 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
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
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
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