Laravel Eloquent one to many relationship, dynamic

Question

I have asked a question here yesterday (Laravel Eloquent query build select min value). From that i am updating my query to select rooms.

My Previous query:

$buildquery=Room::

        with(['hotel' => function ($query) {
            $query->where('status', 0);
        }])

        ->with('image')->with('amenities');

        if ($request->filled('location_id')) {

            $buildquery->Where('city', $request->location_id);
            //print_r($request->location_id);
        }

        // If amenities is there add it to query
        if($request->filled('amenities')){
            $amenities = $request->amenities;
            $count = count($amenities);

            $buildquery->withCount(['amenities' => function($query) use ($amenities){
                            $query->whereIn('amenities_id', $amenities);
                        }])
                        ->having('amenities_count', $count);
        }


        // If price is there add it to query
        if ($request->filled('min_price')) {
            $buildquery->whereBetween('price', [$request->min_price, $request->max_price]);    
        }

        $buildquery->Where('astatus', 1)->Where('status', 0);

        //$buildquery->orderBy('price', 'DESC')->take(1);

        $rooms = $buildquery->simplePaginate(20);

My updated uqery:

$rooms = Hotel::with(['room' => function($query) {
            $query->orderBy('price', 'asc')->first();
        },
        'room.image',
        'room.amenities'])
        ->get();

I ran into one problem. If the user selects the amenities, then only i am including the following query,

if($request->filled('amenities')){
                $amenities = $request->amenities;
                $count = count($amenities);

                $buildquery->withCount(['amenities' => function($query) use ($amenities){
                                $query->whereIn('amenities_id', $amenities);
                            }])
                            ->having('amenities_count', $count);
            }

How can i add this to my updated query?,

Tried this way but no luck

$rooms = Hotel::with(['room' => function($query) {
            $query->orderBy('price', 'asc')->first();
        },
        'room.image',
        'room.amenities' => function($query) {
            $query->withCount(['amenities' => function($query) use ($amenities){
                            $query->whereIn('amenities_id', $amenities);
                        }])
                        ->having('amenities_count', $count);
        }])->get();

Answer 1

I think the problem is here:

$query->whereIn('amenities_id', $amenities);

Instead of this you should use:

$query->whereIn('amenities.id', $amenities);

because you are using subquery where you have amenities so to refer them id you should use just id column.

Answer 2

I think, you are looking for 'whereHas' builder function. https://laravel.com/docs/5.5/eloquent-relationships#querying-relationship-existence

$rooms = Hotel::with(['room' => function($query) {
            $query->orderBy('price', 'asc')->first();
        },
        'room.image',
    }])
    ->whereHas('amenities', function ($query) use ($amenities) {
        $query->whereIn('id', $amenities);
    })
    ->get();