Shopware多对多关联中间表添加priority字段及写入报错求助

在给产品（products）和FAQ（faqs）的多对多关联中间表添加priority字段后，用VueJS写入该字段时出现以下错误：

Expected command for "xxx" to be "Shopware\Core\Framework\DataAbstractionLayer\Write\Command\UpdateCommand". (Got: Shopware\Core\Framework\DataAbstractionLayer\Write\Command\InsertCommand)

核心原因 #

Shopware的多对多关联默认依赖**复合键（product_id、product_version_id、faq_id）**识别唯一关联，但你的中间表用了独立的id作为主键，导致数据抽象层（DAL）无法判断是更新现有关联还是插入新关联，从而抛出错误。

解决方案 #

1. 调整数据库表结构 #

去掉独立的id主键，改用复合主键（product_id、product_version_id、faq_id），这是Shopware多对多关联的标准设计：

CREATE TABLE `sample_product_faq` (
    `product_id` BINARY(16) NOT NULL,
    `product_version_id` BINARY(16) NOT NULL,
    `faq_id` BINARY(16) NOT NULL,
    `priority` int(11) unsigned NOT NULL DEFAULT 0,
    `created_at` datetime(3) NOT NULL,
    `updated_at` datetime(3) DEFAULT NULL,
    PRIMARY KEY (`product_id`, `product_version_id`, `faq_id`),
    CONSTRAINT `fk.product_faq.faq_id` FOREIGN KEY (`faq_id`)
        REFERENCES `sample_faq` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk.product_faq.product_id` FOREIGN KEY (`product_id`, `product_version_id`)
        REFERENCES `product` (`id`, `version_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

2. 配置实体关联定义 #

在产品实体或FAQ实体的关联定义中，明确指定中间表字段（包括priority），并绑定复合键：

产品实体中添加关联（示例）

// src/Entity/ProductEntity.php
protected function defineFields(): FieldCollection
{
    return new FieldCollection([
        // 其他字段...
        (new ManyToManyField(
            'faqs',
            SampleFaqEntity::class,
            SampleProductFaqAssociationDefinition::class,
            'product_id',
            'faq_id'
        ))->addReferenceField('product_version_id'),
    ]);
}

创建关联定义类

// src/Entity/SampleProductFaqAssociationDefinition.php
class SampleProductFaqAssociationDefinition extends ManyToManyAssociationDefinition
{
    public const ENTITY_NAME = 'sample_product_faq';

    public function getEntityName(): string
    {
        return self::ENTITY_NAME;
    }

    protected function defineFields(): FieldCollection
    {
        return new FieldCollection([
            (new IdField('product_id', 'productId'))->addFlags(new PrimaryKey(), new Required()),
            (new IdField('product_version_id', 'productVersionId'))->addFlags(new PrimaryKey(), new Required()),
            (new IdField('faq_id', 'faqId'))->addFlags(new PrimaryKey(), new Required()),
            new IntField('priority', 'priority'),
            new CreatedAtField(),
            new UpdatedAtField(),
        ]);
    }
}

3. VueJS端写入逻辑调整 #

提交数据时要传递完整的关联信息，让DAL通过复合键判断操作类型：

// 更新产品的FAQ关联及优先级
const updateProductFaqs = async (productId, versionId, faqs) => {
    const payload = {
        id: productId,
        versionId: versionId,
        faqs: faqs.map(faq => ({
            id: faq.id,
            priority: faq.priority,
            productVersionId: versionId
        }))
    };

    await this.$store.dispatch('product/update', payload);
};

4. 刷新实体与缓存 #

执行Shopware命令让配置生效：

bin/console cache:clear
bin/console dal:refresh:entities

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