Q: Get whereBetween on the lowest relationship table column I've got some product with deals. Every product has several deals. I managed to get the whereBetween, but only on all associated deals. I would like to only return products where the lowest deal matches the whereBetween.
So get products where the lowest associated deal price is between the min & max given price.
Here is my code so far:
$products = Product::when($request->filled('brand'), function ($query)use($request) {
    $brandRequest = $request->input('brand');
    $brandReqArray = explode(',', $brandRequest);
    $query->whereHas('brands', function($q) use ($brandReqArray)  {
        $q->whereIn('slug', $brandReqArray);
    });
})->when($request->filled('gender'), function ($query)use($request) {
    $genderRequest = $request->input('gender');
    $genderReqArray = explode(',', $genderRequest);
    $query->whereHas('genders', function($q) use ($genderReqArray)  {
        $q->whereIn('slug', $genderReqArray);
    });
})->when($request->filled('price'), function ($query)use($request) {
    $priceRequest = $request->input('price');
    $priceReqArray = explode(';', $priceRequest);
    $query->whereHas('deals', function($q) use ($priceReqArray)  {
        $q->whereBetween('price', $priceReqArray);
    }); 
})->with(['deals'  => function ($query) {
    $query->orderBy('price','asc');
}])->paginate(24);

$input = $request->only(['brands', 'gender', 'price']);
$products->appends($input);


A: Try this and let me know, is that what you wanted?
    $products = Product::when($request->filled('brand'), function ($query)use($request) {
    $brandRequest = $request->input('brand');
    $brandReqArray = explode(',', $brandRequest);
    $query->whereHas('brands', function($q) use ($brandReqArray)  {
        $q->whereIn('slug', $brandReqArray);
    });
})->when($request->filled('gender'), function ($query)use($request) {
    $genderRequest = $request->input('gender');
    $genderReqArray = explode(',', $genderRequest);
    $query->whereHas('genders', function($q) use ($genderReqArray)  {
        $q->whereIn('slug', $genderReqArray);
    });
})->when($request->filled('price'), function ($query)use($request) {
    $priceRequest = $request->input('price');
    $priceReqArray = explode(';', $priceRequest);
    $query->whereHas('deals', function($q) use ($priceReqArray)  {
        $q->whereBetween('price', $priceReqArray)
        ->whereNotExists(function ($q)use($priceReqArray) {
            $q->select(DB::raw(1))
                ->from('deals')  
                //->whereNull('deals.deleted_at')                     
                ->whereNotBetween('price', $priceReqArray)
            ->whereRaw('product.id = deals.productId');
        }) 
        ->orderBy('price','asc')->limit(1);
    })
})->paginate(24);

