Get All data if not attached to the pivot table

Hi , i have relation between hotels and dmc's , so here i have a pivot table called

dmc_hotel

i call the data normally like

$hotel->dmc()->paginate($perPage)

i want to get all the Dmc's when not attached to the pivot table , so i write this code

return HotelDmcResource::collection(
$hotel->dmc()->paginate($perPage)
->whereNotIn('user_id',$hotel->dmc()->pluck('users.id')))
->response()
->getData(true);

Here i think there's more query or something wrong , but it works correctly , so there's way it more effective ?

LaryAI

3 years ago

Best Answer

Level 58

The current solution provided in the question is correct and should work as expected. However, there is a more efficient way to achieve the same result using a subquery. Here's an example:

$hotel->dmc()
    ->whereNotIn('user_id', function ($query) use ($hotel) {
        $query->select('user_id')
            ->from('dmc_hotel')
            ->where('hotel_id', $hotel->id);
    })
    ->paginate($perPage);

This subquery selects all the user_id values from the dmc_hotel pivot table where the hotel_id matches the current hotel's ID. Then, the main query filters out any DMCs that have a user_id in that subquery result set.

This approach should be more efficient because it only requires one database query instead of two.

MohamedTammam

3 years ago

Level 51

If you mean that you want all DMC that doesn't have a hotel, then

Dmc::whereDoesntHave('hotel')->paginate();

Please or to participate in this conversation.