Laravel多表关联含GROUP BY时如何获取随机产品记录

你的当前查询存在逻辑冲突：先按seller_registration.id分组，再调用inRandomOrder()，此时的随机是随机筛选卖家而非随机产品（分组会合并同一卖家下的所有产品，仅返回一条）。根据不同需求，调整方案如下：

需求1：随机获取所有符合条件的产品（不限制卖家产品数量） #

直接移除分组操作，让inRandomOrder()直接作用于所有产品记录即可。同时优化部分语法使其更符合Laravel规范：

$products = DB::table('product_management')
    ->join('seller_registration','seller_registration.id','product_management.seller_id')
    ->join('seller_package_management','seller_package_management.id','seller_registration.package_taken_name')
    ->where('product_management.product_name', $product_name)
    ->where('product_management.status', 1)
    ->whereNull('product_management.deleted_at')
    ->whereNull('seller_registration.deleted_at')
    ->where('seller_registration.status', 1)
    ->where([
        ['seller_registration.city','like', "%{$city}%"],
        ['seller_registration.seller_type','like', "%{$seller_type}%"]
    ])
    // ->where('seller_package_management.package_type','Paid') // 按需启用该条件
    ->select([
        'seller_registration.id as sid',
        'seller_registration.company_name',
        'seller_registration.seller_name',
        'seller_registration.company_logo',
        'seller_registration.seller_type',
        'seller_registration.phone',
        'seller_registration.city',
        'seller_registration.pincode',
        'seller_registration.category_name',
        'seller_registration.valid_up_to',
        'product_management.id as pid',
        'product_management.product_name',
        'product_management.product_description',
        'product_management.product_image',
        'product_management.approx_price',
        'product_management.current_price',
        'product_management.deals_in',
        'product_management.moq',
        'product_management.product_specification'
    ])
    // ->orderBy('seller_registration.add_on_package','DESC') // 若无需按套餐排序可删除此行
    ->inRandomOrder()
    ->get();

需求2：每个卖家随机返回一条符合条件的产品 #

如果需要保证每个卖家仅出现一次，且返回的是该卖家下的随机产品，需要用子查询先对每个卖家的产品做随机排序，再分组取第一条：

$products = DB::table(function($query) use ($product_name, $city, $seller_type) {
    $query->from('product_management')
        ->join('seller_registration','seller_registration.id','product_management.seller_id')
        ->join('seller_package_management','seller_package_management.id','seller_registration.package_taken_name')
        ->where('product_management.product_name', $product_name)
        ->where('product_management.status', 1)
        ->whereNull('product_management.deleted_at')
        ->whereNull('seller_registration.deleted_at')
        ->where('seller_registration.status', 1)
        ->where([
            ['seller_registration.city','like', "%{$city}%"],
            ['seller_registration.seller_type','like', "%{$seller_type}%"]
        ])
        // ->where('seller_package_management.package_type','Paid')
        ->selectRaw('*, RAND() as random_order')
        ->orderBy('random_order');
}, 'sub')
->select([
    'seller_registration.id as sid',
    'seller_registration.company_name',
    'seller_registration.seller_name',
    'seller_registration.company_logo',
    'seller_registration.seller_type',
    'seller_registration.phone',
    'seller_registration.city',
    'seller_registration.pincode',
    'seller_registration.category_name',
    'seller_registration.valid_up_to',
    'product_management.id as pid',
    'product_management.product_name',
    'product_management.product_description',
    'product_management.product_image',
    'product_management.approx_price',
    'product_management.current_price',
    'product_management.deals_in',
    'product_management.moq',
    'product_management.product_specification'
])
->groupBy('seller_registration.id')
->orderBy('seller_registration.add_on_package','DESC') // 按需保留
->get();

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