Laravel中如何高效关联并筛选products与product_details数据表？

一、正确建立模型关联 #

两张表是一对多关系（一个产品对应多条详情记录），在Laravel Eloquent中按以下方式定义关联：

1. Products 模型（app/Models/Products.php） #

namespace App\Models;

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

class Products extends Model
{
    protected $table = 'products';
    // 若sapcode是该表主键，添加此行；否则忽略
    protected $primaryKey = 'sapcode';

    // 定义与产品详情的一对多关联
    public function details(): HasMany
    {
        return $this->hasMany(Product_details::class, 'sapcode', 'sapcode');
    }
}

2. Product_details 模型（app/Models/Product_details.php） #

namespace App\Models;

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

class Product_details extends Model
{
    protected $table = 'product_details';

    // 定义与产品的反向关联
    public function product(): BelongsTo
    {
        return $this->belongsTo(Products::class, 'sapcode', 'sapcode');
    }
}

二、高效筛选的最优方案 #

你当前的代码将大量数据拉取到PHP内存中统计筛选，在210万行的product_details表下性能极差。最优方案是把统计逻辑放到数据库层面执行，利用SQL的分组、聚合或Eloquent关联筛选特性，减少数据传输和内存占用。

方案1：JOIN + GROUP BY + HAVING（性能最优） #

通过关联查询+分组统计，一次获取符合所有筛选条件的产品：

$sliced = array_values(array_slice($req_val, 3));
$threshold = count($sliced);

$products = Products::where('status', 1)
    ->where('s_part', $req_val[0])
    ->where('main_category', $req_val[1])
    ->where('sub_category', $req_val[2])
    ->join('product_details', 'products.sapcode', '=', 'product_details.sapcode')
    ->where(function ($query) use ($sliced, $keys) {
        foreach ($sliced as $index => $value) {
            $key = $keys[$index];
            $query->orWhere(function ($subQuery) use ($key, $value) {
                $subQuery->where('product_details.name', $key)
                         ->where('product_details.value', $value);
            });
        }
    })
    ->groupBy('products.sapcode')
    // 统计匹配的不同属性数量，确保满足所有筛选条件
    ->havingRaw('COUNT(DISTINCT product_details.name) >= ?', [$threshold])
    ->select('products.*')
    ->get();

return response()->json($products);

方案2：Eloquent whereHas（更符合ORM风格） #

利用关联查询的whereHas方法，直接筛选出满足条件的产品，代码更简洁：

$sliced = array_values(array_slice($req_val, 3));
$threshold = count($sliced);

$products = Products::where('status', 1)
    ->where('s_part', $req_val[0])
    ->where('main_category', $req_val[1])
    ->where('sub_category', $req_val[2])
    ->whereHas('details', function ($query) use ($sliced, $keys) {
        foreach ($sliced as $index => $value) {
            $key = $keys[$index];
            $query->orWhere(function ($subQuery) use ($key, $value) {
                $subQuery->where('name', $key)->where('value', $value);
            });
        }
    }, '>=', $threshold) // 要求匹配的详情记录数≥筛选条件数量
    ->get();

return response()->json($products);

三、性能优化关键建议 #

1. 添加索引：
   • 给product_details表创建联合索引：CREATE INDEX idx_details_sapcode_name_value ON product_details(sapcode, name, value);，大幅提升关联和筛选速度。
   • 给products表创建联合索引：CREATE INDEX idx_products_filter ON products(status, s_part, main_category, sub_category);，优化基础条件的查询效率。
2. 避免二次查询：直接一次性获取产品的所有字段，不要先查sapcode再发起二次查询。
3. 可选：数据预处理：如果常用的筛选属性固定，可以考虑将product_details的属性转成宽表（把name作为列、value作为值），但需要维护同步逻辑，适合查询性能要求极高的场景。

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