如何在Laravel中正确实现过去一周销量Top5产品查询？

我需要获取过去一周销量最高的5款产品。MySQL原生查询可以正常运行：

select op.id_product from order_product as op
inner join `order` as o on o.id_order = op.id_order
where o.purchase_date between date_sub(now(), interval 1 week) and now()
group by op.id_product
order by sum(op.quantity_ordered) desc limit 5;

但转换成Laravel代码后排序结果错误：原生SQL返回的id_product顺序是15 2 3 12 10，Laravel代码输出却是10 15 2 6 12。排查发现日期范围存在问题：Laravel生成的区间是2022-07-11 23:07:10至2022-07-23 23:07:10，而MySQL的date_sub(now(), interval 1 week)返回2022-07-16 23:18:55，用Carbon生成日期也得到同样错误结果。

我的Laravel代码如下：

$now = new DateTime();
$now = $now->format('Y-m-d H:m:s');
$lastWeek = new DateTime();
$lastWeek = $lastWeek->modify('previous week');
$lastWeek = $lastWeek->format('Y-m-d H:m:s');
$orderProductIDs = DB::table('order_product')
    ->select('order_product.id_product')->join('order', 'order.id_order', '=', 'order_product.id_order')
    ->whereBetween('order.purchase_date', [$lastWeek, $now])
    ->groupBy('order_product.id_product')
    ->orderByRaw('sum(order_product.quantity_ordered) desc limit 5')
    ->get();
dd($orderProductIDs);

疑问 #

1. 如何修正日期范围问题？
2. 是否可以用OrderProduct::all()->[...]的模型方式替代DB::table('order_product')->[...]构建查询？

1. 修正日期范围问题 #

问题出在modify('previous week')的逻辑：它会直接跳转到上周一的同一时间，而MySQL的date_sub(now(), interval 1 week)是取当前时间往前推7天的精确时间点。提供两种修正方式：

方法一：用Carbon生成正确区间 #

直接使用Carbon的subWeek()方法生成精确的7天前时间：

use Carbon\Carbon;

$orderProductIDs = DB::table('order_product')
    ->select('order_product.id_product')
    ->join('order', 'order.id_order', '=', 'order_product.id_order')
    ->whereBetween('order.purchase_date', [Carbon::now()->subWeek(), Carbon::now()])
    ->groupBy('order_product.id_product')
    ->orderByRaw('sum(order_product.quantity_ordered) desc')
    ->limit(5)
    ->get();

方法二：直接复用MySQL日期函数（推荐） #

避免手动生成日期带来的时区或格式问题，让数据库直接处理日期逻辑：

$orderProductIDs = DB::table('order_product as op')
    ->select('op.id_product')
    ->join('`order` as o', 'o.id_order', '=', 'op.id_order')
    ->whereRaw('o.purchase_date between date_sub(now(), interval 1 week) and now()')
    ->groupBy('op.id_product')
    ->orderByRaw('sum(op.quantity_ordered) desc')
    ->limit(5)
    ->get();

注意：原Laravel代码把limit 5写在orderByRaw里，虽能运行但不符合规范，应单独调用->limit(5)方法。

2. 使用模型方式构建查询 #

完全可以，先完善模型关联，再通过模型构建查询：

第一步：添加模型关联 #

在OrderProduct模型中添加关联：

// app/Models/OrderProduct.php
namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class OrderProduct extends Model
{
    protected $table = 'order_product';
    protected $primaryKey = 'id_order_product';

    use HasFactory;

    public function order()
    {
        return $this->belongsTo(Order::class, 'id_order', 'id_order');
    }
}

第二步：通过模型查询 #

use App\Models\OrderProduct;
use Carbon\Carbon;

$topProducts = OrderProduct::select('id_product')
    ->whereHas('order', function ($query) {
        $query->whereBetween('purchase_date', [Carbon::now()->subWeek(), Carbon::now()]);
        // 也可以用原生SQL：$query->whereRaw('purchase_date between date_sub(now(), interval 1 week) and now()');
    })
    ->groupBy('id_product')
    ->orderByRaw('sum(quantity_ordered) desc')
    ->limit(5)
    ->get();

控制台命令 #

php artisan make:migration create_product_table
php artisan make:migration create_order_table
php artisan make:migration create_order_product_table
php artisan make:model Product
php artisan make:model Order
php artisan make:model OrderProduct
php artisan make:factory ProductFactory
php artisan make:factory OrderFactory
php artisan make:factory OrderProductFactory
php artisan make:seeder DatabaseSeeder

Product表迁移文件 #

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::create('product', function (Blueprint $table) {
            $table->id('id_product');
            $table->string('name');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('product');
    }
};

Order表迁移文件 #

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::create('order', function (Blueprint $table) {
            $table->id('id_order');
            $table->string('order_number')->unique();
            $table->dateTime('purchase_date');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('order');
    }
};

OrderProduct表迁移文件 #

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up()
    {
        Schema::create('order_product', function (Blueprint $table) {
            $table->id('id_order_product');
            $table->foreignId('id_order')->references('id_order')->on('order')->cascadeOnDelete();
            $table->foreignId('id_product')->references('id_product')->on('product')->cascadeOnDelete();
            $table->integer('quantity_ordered');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('order_product');
    }
};

Product模型 #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Product extends Model
{
    protected $table = 'product';
    protected $primaryKey = 'id_product';

    use HasFactory;
}

Order模型 #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Order extends Model
{
    protected $table = 'order';
    protected $primaryKey = 'id_order';

    use HasFactory;
}

OrderProduct模型 #

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class OrderProduct extends Model
{
    protected $table = 'order_product';
    protected $primaryKey = 'id_order_product';

    use HasFactory;
}

Product工厂 #

<?php

namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;

/**
 * @extends \Illuminate\Database\Eloquent\Factories\Factory<\App\Models\Product>
 */
class ProductFactory extends Factory
{
    public function definition()
    {
        return [
            'name' => fake()->word(),
        ];
    }
}

Order工厂（修正原代码错误） #

<?php

namespace Database\Factories;

use App\Models\User;
use Illuminate\Database\Eloquent\Factories\Factory;

/**
 * @extends \Illuminate\Database\Eloquent\Factories\Factory<\App\Models\Order>
 */
class OrderFactory extends Factory
{
    public function definition()
    {
        $users = User::pluck('id')->toArray();
        return [
            'id_user' => $users ? fake()->randomElement($users) : 1,
            'order_number' => fake()->unique()->uuid(),
            'purchase_date' => fake()->dateTimeBetween('-1 month', 'now'),
        ];
    }
}

DatabaseSeeder（修正原代码关联问题） #

<?php

namespace Database\Seeders;

use App\Models\Product;
use App\Models\Order;
use App\Models\OrderProduct;

use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run()
    {
        $products = Product::factory(100)->create();
        $orders = Order::factory(142)->create();
        
        OrderProduct::factory(300)->create(function () use ($orders, $products) {
            return [
                'id_order' => fake()->randomElement($orders)->id_order,
                'id_product' => fake()->randomElement($products)->id_product,
            ];
        });
    }
}

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