CODEWITHSUNDEEP
phplaraveleloquent
Farshad
Farshad

Reputation: 2000

How to use distinct in relations query in Laravel

I have a Laravel ORM query that I join 2-3 relation together like below :

$data = AccommodationRoom::with('accommodation.city',
    'accommodation.accommodationFacilities', 'accommodation.gallery')
    ->whereHas('roomCapacityHistory', function ($query) use ($from_date, $to_date) {
        $query->whereDate('from_date', '<=', $from_date);
        $query->whereDate('to_date', '>=', $to_date);
    })
    ->whereHas('accommodation', function ($query) use ($searchCity) {
        $query->where('city_id', $searchCity);
    })
    ->limit(10)->get();

return response()->json($data, 200);

On the room model, I check if the capacity model has a date and where the city id is what the user gives to the hotels (accommodation). The problem is that when I get the result, I get repeated hotels for each room. For one search I might get ten hotels the same, so I want to make the accommodations unique and show one result (room) per accommodation.

Upvotes: 0

Views: 436

Answers (2)

Matin Hajatdoost
Matin Hajatdoost

Reputation: 83

You can use groupBy instead of distinct; because you want to group your results based on one column which is for example accommodation_id:

$data = AccommodationRoom::with('accommodation.city',
    'accommodation.accommodationFacilities', 'accommodation.gallery')
    ->SELECT('accommodation_id')
    ->whereHas('roomCapacityHistory', function ($query) use ($from_date, $to_date) {
        $query->whereDate('from_date', '<=', $from_date);
        $query->whereDate('to_date', '>=', $to_date);
    })
    ->whereHas('accommodation', function ($query) use ($searchCity) {
        $query->where('city_id', $searchCity);
    })
    ->groupBy('accommodation_id')->limit(10)->get();

return response()->json($data, 200);

Upvotes: 1

mitch292
mitch292

Reputation: 1

Try to chain the distinct method.

I.e: 

$data = AccommodationRoom::with('accommodation.city',
    'accommodation.accommodationFacilities', 'accommodation.gallery')
    ->whereHas('roomCapacityHistory', function ($query) use ($from_date, $to_date) {
        $query->whereDate('from_date', '<=', $from_date);
        $query->whereDate('to_date', '>=', $to_date);
    })
    ->whereHas('accommodation', function ($query) use ($searchCity) {
        $query->where('city_id', $searchCity);
    })
    ->distinct('VALUE_FOR_HOTEL_HERE')->limit(10)->get();

return response()->json($data, 200);

Upvotes: 0

Related Questions