I have 2 models:

Product Model which has many prices:

public function prices()
{
    return $this->hasMany(Price::class)->orderBy('price', 'DESC');
}

Price model which belongs to Product:

public function product()
{
    return $this->belongsTo(Product::class);
}

There are some other relationships too. I have a query like this:

    $query = Product::query();
    $query->where('brandId', $brand->id);
    $query->where('termId', $termId);
    $query->when($productType, function ($q) use ($productType){
        $q->with('productAttributes')->whereHas('productAttributes', function($query) use ($productType){
            $query->where(['attributeId'=> 5, 'attributeDataId'=>$productType]);
        });
    });

I want to query the product price too.

The main problem is that I want to check ONLY the first price to see if it is less than a given number or not.

This query checks ALL prices (and it's not what I want):

    $query->when($maxPrice, function ($q) use ($maxPrice){
        $q->with('prices')->whereHas('prices', function($query) use ($maxPrice){
            $query->where('price', '<=', $maxPrice);
        });
    });

CodePudding user response：

You could use a subquery where clause for this:

->where(function ($query) {
    $query->select('price')
        ->from('prices')
        ->whereColumn('products.id', 'prices.product_id')
        ->orderByDesc('price')
        ->limit(1);
}, '<=', $maxPrice)

Your query would look something like:

$query = Product::query()
    ->where('brandId', $brand->id)
    ->where('termId', $termId)
    ->when($productType, function ($q) use ($productType) {
        $q->with('productAttributes')
            ->whereHas('productAttributes', function ($query) use ($productType) {
                $query->where(['attributeId' => 5, 'attributeDataId' => $productType]);
            });
    })
    ->when($maxPrice, function ($q) use ($maxPrice) {
        $q->with('prices')->where(function ($query) {
            $query->select('price')
                ->from('prices')
                ->whereColumn('products.id', 'prices.product_id')
                ->orderByDesc('price')
                ->limit(1);
        }, '<=', $maxPrice);
    });