Laravel+MySQL实现产品与多语言嵌套属性关联方案咨询

一、数据库表设计 #

1. 产品表 (products) #

存储产品基础信息：

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

2. 语言表 (languages) #

存储支持的语言列表，用code作为唯一标识（对应示例中的english、arabic）：

CREATE TABLE languages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL COMMENT '比如en、ar',
    name VARCHAR(50) NOT NULL COMMENT '比如English、Arabic',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

3. 产品语言关联表 (product_language) #

作为多对多关联的中间表，存储每个产品对应语言的interface、subtitles属性：

CREATE TABLE product_language (
    product_id INT UNSIGNED NOT NULL,
    language_id INT UNSIGNED NOT NULL,
    interface BOOLEAN NOT NULL DEFAULT FALSE,
    subtitles BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (product_id, language_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
);

二、Laravel 模型关联配置 #

1. Product 模型 #

在app/Models/Product.php中定义与Language的多对多关联，指定中间表及额外字段：

<?php

namespace App\Models;

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

class Product extends Model
{
    protected $fillable = ['name', 'price'];

    public function languages(): BelongsToMany
    {
        return $this->belongsToMany(Language::class, 'product_language')
            ->withPivot('interface', 'subtitles')
            ->withTimestamps();
    }
}

2. Language 模型 #

在app/Models/Language.php中定义反向关联：

<?php

namespace App\Models;

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

class Language extends Model
{
    protected $fillable = ['code', 'name'];

    public function products(): BelongsToMany
    {
        return $this->belongsToMany(Product::class, 'product_language')
            ->withPivot('interface', 'subtitles')
            ->withTimestamps();
    }
}

三、查询并格式化嵌套语言数据 #

1. 基础查询与集合格式化 #

在控制器中查询产品时，加载关联语言并格式化为嵌套结构：

<?php

namespace App\Http\Controllers;

use App\Models\Product;
use Illuminate\Http\Response;

class ProductController extends Controller
{
    // 查询单个产品
    public function show($id)
    {
        $product = Product::with('languages')->findOrFail($id);

        $formattedLangs = $product->languages->mapWithKeys(function ($lang) {
            return [
                $lang->code => [
                    'interface' => $lang->pivot->interface,
                    'subtitles' => $lang->pivot->subtitles
                ]
            ];
        });

        $responseData = [
            'id' => $product->id,
            'name' => $product->name,
            'price' => $product->price,
            'langs' => $formattedLangs
        ];

        return response()->json($responseData, Response::HTTP_OK);
    }

    // 查询所有产品
    public function index()
    {
        $products = Product::with('languages')->get();

        $formattedProducts = $products->map(function ($product) {
            $langs = $product->languages->mapWithKeys(function ($lang) {
                return [
                    $lang->code => [
                        'interface' => $lang->pivot->interface,
                        'subtitles' => $lang->pivot->subtitles
                    ]
                ];
            });

            return [
                'id' => $product->id,
                'name' => $product->name,
                'price' => $product->price,
                'langs' => $langs
            ];
        });

        return response()->json($formattedProducts, Response::HTTP_OK);
    }
}

2. 使用API资源（优雅格式化） #

创建ProductResource统一处理数据格式：

<?php

namespace App\Http\Resources;

use Illuminate\Http\Request;
use Illuminate\Http\Resources\Json\JsonResource;

class ProductResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'price' => $this->price,
            'langs' => $this->languages->mapWithKeys(function ($lang) {
                return [
                    $lang->code => [
                        'interface' => $lang->pivot->interface,
                        'subtitles' => $lang->pivot->subtitles
                    ]
                ];
            })
        ];
    }
}

控制器中直接调用资源：

public function show($id)
{
    $product = Product::with('languages')->findOrFail($id);
    return new ProductResource($product);
}

public function index()
{
    $products = Product::with('languages')->get();
    return ProductResource::collection($products);
}

四、数据写入示例 #

1. 给现有产品关联语言及属性 #

$product = Product::find(1);
// 关联英文，设置interface=true，subtitles=false
$product->languages()->attach(
    Language::where('code', 'en')->first()->id,
    ['interface' => true, 'subtitles' => false]
);
// 关联阿拉伯文，设置interface=false，subtitles=true
$product->languages()->attach(
    Language::where('code', 'ar')->first()->id,
    ['interface' => false, 'subtitles' => true]
);

2. 创建产品时同时关联语言 #

$product = Product::create(['name' => 'Example Product', 'price' => 99.99]);

$langIds = [
    Language::where('code', 'en')->first()->id => ['interface' => true, 'subtitles' => false],
    Language::where('code', 'ar')->first()->id => ['interface' => false, 'subtitles' => true]
];

$product->languages()->attach($langIds);

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