How to get last relation in a nested relationship in Laravel 9.x-CodePudding

ServiceCategory has many Service

public function services(): HasMany {
    return $this->hasMany(Service::class, 'category_id');
}

Service has many Price

public function prices(): HasMany {
    return $this->hasMany(ServicePrice::class, 'service_id');
}

Let's say prices table has a price_value column, how do I get the lowest and highest price?

I used this method but every time the query returns a list of ServiceCategory instead of a list of Price.

What I tried:

ServiceCategory::with('services.prices')->get();
// Or Even
ServiceCategory::first()->with('services.prices')->get();

And:

ServiceCategory::has('services')->with('services:category_id')->with(['services.prices' => function ($q) {
    $q->select('price');
}])->get();

Still no chance to only return a collection of Price

CodePudding user response：

I think you're looking for an Has One Of Many.
You can define the highest and lowest price on the Service:

/**
 * Get the service's highest price.
 */
public function highestPrice()
{
    return $this->hasOne(ServicePrice::class)->ofMany('price_value', 'max');
}

/**
 * Get the service's lowest price.
 */
public function lowestPrice()
{
    return $this->hasOne(ServicePrice::class)->ofMany('price_value', 'min');
}

Not sure why you would want a Collection of all the lowest and highest prices but you could use those relations and some Collection functions to create a collection of only the prices

Services::with(['highestPrice', 'lowestPrice'])
    ->map(function(Service $service) {
        return [$service->highestPrice, $service->lowestPrice];
    })
    ->flatten();

CodePudding user response：

Finally after trying every method that eloquent lets me to try I found this solution (I should have thought from the other side):

$category = ServiceCategory::firstOrFail();

$minPrice = ServicePrice::whereHas('service', function ($q) use ($category) {
    $q->whereCategoryId($category->id);
})->select('price')->min('price'); 

// Instead of min() you can use get() to list all prices.

But please let me know if this method is not optimal in terms of performance.