MySQL按卖家表上限限制查询商品 支持随机选取与分页

前提准备 #

首先在Products模型中定义和Seller模型的关联：

// App/Models/Products.php
public function seller()
{
    return $this->belongsTo(Seller::class, 'seller_id', 'seller_id');
}

核心查询代码（支持随机取数+卖家上限限制+分页） #

要求MySQL版本 >= 8.0（支持窗口函数，性能适合大数据量场景）：

use Illuminate\Support\Facades\DB;

// 单页100条，每个卖家返回商品不超过自身设置的limit，且每个卖家商品随机排序
$products = Products::select('products.*')
    ->join('sellers', 'products.seller_id', '=', 'sellers.seller_id')
    ->selectRaw('ROW_NUMBER() OVER (PARTITION BY products.seller_id ORDER BY RAND()) AS seller_row_num')
    ->whereRaw('seller_row_num <= sellers.limit')
    ->paginate(100);

规则说明 #

• 窗口函数PARTITION BY products.seller_id会按卖家ID对商品分组
• ORDER BY RAND()实现同一卖家下的商品随机排序，可根据需求替换为其他排序字段（如created_at DESC按上架时间倒序）
• whereRaw('seller_row_num <= sellers.limit')保证每个卖家返回的商品数不超过自身设置的展示上限，完全匹配你的示例中卖家1返回1个、卖家2返回3个、卖家3返回2个的要求
• 最终直接调用paginate(100)即可实现100条/页的分页逻辑，和原有分页使用方式完全兼容

低版本MySQL兼容方案（仅适用于小数据量场景） #

如果使用MySQL 5.x不支持窗口函数，可以用集合处理的方式实现：

// 先取出所有符合条件的商品
$products = Products::with('seller')->get()
    ->groupBy('seller_id')
    ->flatMap(function ($sellerProducts) {
        // 每个卖家按自身limit截取，这里用shuffle()实现随机
        return $sellerProducts->shuffle()->take($sellerProducts->first()->seller->limit);
    });
// 手动生成分页对象
$page = request()->get('page', 1);
$perPage = 100;
$paginatedProducts = new \Illuminate\Pagination\LengthAwarePaginator(
    $products->forPage($page, $perPage),
    $products->count(),
    $perPage,
    $page
);

内容的提问来源于stack exchange，提问作者B L Praveen