如何在Laravel Eloquent中获取指定列表关联产品的价格/尺寸最值

Got it, let's break this down step by step. First, we need to make sure our Eloquent model relationships are configured properly, then we can use a few efficient approaches to fetch those min/max values.

Step 1: Configure Model Relationships #

First, let's define the relationships between your three models. Note: List is a reserved keyword in PHP, so I'd recommend renaming your model to something like TodoList or ShoppingList to avoid conflicts. For this example, I'll use List but keep that caveat in mind.

List Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class List extends Model
{
    protected $table = 'Lists'; // Match your table name

    // A List has many Requests
    public function requests(): HasMany
    {
        return $this->hasMany(Request::class);
    }
}

Request Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;

class Request extends Model
{
    protected $table = 'Requests';

    // A Request belongs to one Product
    public function product(): BelongsTo
    {
        return $this->belongsTo(Product::class);
    }

    // A Request belongs to one List
    public function list(): BelongsTo
    {
        return $this->belongsTo(List::class);
    }
}

Product Model #

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;

class Product extends Model
{
    protected $table = 'Products';

    // A Product has many Requests
    public function requests(): HasMany
    {
        return $this->hasMany(Request::class);
    }
}

Step 2: Fetch Min/Max Values (3 Approaches) #

Approach 1: Use withAggregate (Clean Eloquent Syntax) #

This is the most "Eloquent-native" way, using Laravel's withAggregate method to calculate aggregates directly on the relation chain. It keeps your code readable and avoids raw SQL where possible.

$listId = X; // Replace with your target List ID

$listStats = List::whereHas('requests.product') // Optional: Filter out Requests without a Product
    ->withAggregate('requests.product', 'price', 'max')
    ->withAggregate('requests.product', 'price', 'min')
    ->withAggregate('requests.product', 'size', 'max')
    ->withAggregate('requests.product', 'size', 'min')
    ->find($listId);

// Access the results
$maxPrice = $listStats->product_price_max;
$minPrice = $listStats->product_price_min;
$maxSize = $listStats->product_size_max;
$minSize = $listStats->product_size_min;

Approach 2: Use Joins + Raw Aggregates (Best for Large Datasets) #

If you're dealing with a lot of data, joining tables directly and calculating aggregates at the database level is more efficient (it avoids loading extra records into memory).

$listId = X;

$stats = List::where('id', $listId)
    ->join('Requests', 'Lists.id', '=', 'Requests.list_id')
    ->join('Products', 'Requests.product_id', '=', 'Products.id')
    ->whereNotNull('Requests.product_id') // Optional: Exclude Requests with no Product
    ->selectRaw('
        MAX(Products.price) as max_price,
        MIN(Products.price) as min_price,
        MAX(Products.size) as max_size,
        MIN(Products.size) as min_size
    ')
    ->first();

// Access results
$maxPrice = $stats->max_price;
$minPrice = $stats->min_price;
$maxSize = $stats->max_size;
$minSize = $stats->min_size;

Approach 3: Use Collection Methods (Small Datasets Only) #

If your dataset is small, you can load all related Products into a collection and use Laravel's collection methods to calculate min/max. Note: This is not recommended for large datasets, as it loads all related records into memory.

$listId = X;

$list = List::with('requests.product')->find($listId);

// Extract all Products from Requests, filter out any nulls
$products = $list->requests->pluck('product')->filter();

// Calculate aggregates
$maxPrice = $products->max('price');
$minPrice = $products->min('price');
$maxSize = $products->max('size');
$minSize = $products->min('size');

Key Notes #

• Always use whereHas('requests.product') or whereNotNull('Requests.product_id') if there's a chance some Requests don't have an associated Product—this ensures your aggregates don't include null values.
• For performance, prefer Approach 1 or 2 over Approach 3 when working with large datasets.

内容的提问来源于stack exchange，提问作者Abanoub Samaan