Laravel 5.6 Eloquent 实现分组取最后一行及价格查询需求

Hey there! Let's break down how to solve both of these requirements using Laravel 5.6's Eloquent ORM. First, we'll make sure our models have the right relationships set up, then tackle each task one by one.

Model Relationships Setup #

First, define the core relationships between your three models to make querying easier:

Store.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Store extends Model
{
    // A store has many price records
    public function prices()
    {
        return $this->hasMany(Price::class);
    }

    // A store sells many products through price records
    public function products()
    {
        return $this->belongsToMany(Product::class, 'prices')
                    ->withPivot('value', 'created_at');
    }
}

Product.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    // A product has many price records across stores
    public function prices()
    {
        return $this->hasMany(Price::class);
    }

    // A product is sold in many stores through price records
    public function stores()
    {
        return $this->belongsToMany(Store::class, 'prices')
                    ->withPivot('value', 'created_at');
    }
}

Price.php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Price extends Model
{
    // A price belongs to one product
    public function product()
    {
        return $this->belongsTo(Product::class);
    }

    // A price belongs to one store
    public function store()
    {
        return $this->belongsTo(Store::class);
    }
}

A) Display Current Price of a Specified Product Across All Stores #

Since we retain historical prices by adding new records, the "current" price for a product in a store is the most recent created_at entry. Here's how to fetch this:

$targetProductId = 1; // Replace with your desired product ID

// Fetch latest price per store for the target product
$currentPrices = Price::where('product_id', $targetProductId)
    ->select('prices.*')
    ->join(
        DB::raw('(SELECT store_id, MAX(created_at) as latest_created FROM prices WHERE product_id = ? GROUP BY store_id) as latest_prices'),
        function ($join) use ($targetProductId) {
            $join->on('prices.store_id', '=', 'latest_prices.store_id')
                 ->on('prices.created_at', '=', 'latest_prices.latest_created')
                 ->addBinding($targetProductId);
        }
    )
    ->with('store') // Eager load store details to avoid N+1 queries
    ->get();

// Example output formatting
foreach ($currentPrices as $price) {
    echo "Store: {$price->store->name} | Current Price: {$price->value}\n";
}

If you prefer a more Eloquent-native approach using subqueries:

$latestPriceSubquery = Price::whereColumn('prices.store_id', 'latest_prices.store_id')
    ->where('prices.product_id', $targetProductId)
    ->selectRaw('MAX(created_at)')
    ->groupBy('store_id');

$currentPrices = Price::where('product_id', $targetProductId)
    ->where('created_at', $latestPriceSubquery)
    ->with('store')
    ->get();

B) Display All Products with Their Lowest Price and Corresponding Store Name #

This requires finding the minimum price per product, then associating it with the store(s) that offer that price. Note: If multiple stores have the same lowest price for a product, this will return all relevant stores.

Option 1: Eloquent Collection Mapping (Readable for Small Datasets)

$lowestPrices = Product::select([
    'products.id',
    'products.name',
    DB::raw('MIN(prices.value) as lowest_price'),
])
->join('prices', 'products.id', '=', 'prices.product_id')
->groupBy('products.id', 'products.name')
->get()
->map(function ($product) {
    // Fetch all stores with this product's lowest price
    $storeNames = Store::join('prices', 'stores.id', '=', 'prices.store_id')
        ->where('prices.product_id', $product->id)
        ->where('prices.value', $product->lowest_price)
        ->pluck('name')
        ->implode(', '); // Join multiple store names with commas

    $product->store_names = $storeNames;
    return $product;
});

// Example output
foreach ($lowestPrices as $item) {
    echo "Product: {$item->name} | Lowest Price: {$item->lowest_price} | Store(s): {$item->store_names}\n";
}

Option 2: Single Query (More Efficient for Large Datasets)

Use a subquery to get the minimum price per product, then join back to prices and stores:

$lowestPrices = DB::table('products')
    ->select([
        'products.id',
        'products.name',
        'prices.value as lowest_price',
        'stores.name as store_name'
    ])
    ->join('prices', 'products.id', '=', 'prices.product_id')
    ->join('stores', 'prices.store_id', '=', 'stores.id')
    ->join(
        DB::raw('(SELECT product_id, MIN(value) as min_price FROM prices GROUP BY product_id) as min_prices'),
        function ($join) {
            $join->on('prices.product_id', '=', 'min_prices.product_id')
                 ->on('prices.value', '=', 'min_prices.min_price');
        }
    )
    ->get();

This returns one row per product-store pair with the lowest price. If multiple stores match, you'll get multiple rows for that product.

内容的提问来源于stack exchange，提问作者Daniel Groppo