Shopware 6自定义管理端关联表批量删除问题求助

需要在触发onClickSync()按钮操作前，删除filter_object表及其关联表filter_link中的数据。filter_link表包含product_id和object_id两个外键。目前通过循环按ID删除的方式，仅能删除filter_object表数据，未处理关联的filter_link数据，且数据量大时删除速度极慢，寻求优化方案。

import template from './sw-vehicles-list.html.twig';

const { Component } = Shopware;
const { Criteria } = Shopware.Data;

Component.register('sw-vehicles-list', {
    template,
    inject: ['repositoryFactory'],
    data() {
        return {
            repository: null,
            showAddButton: true,
            isLoading: false,
            object: null,
        };
    },
    metaInfo() {
        return {
            title: this.$createTitle()
        };
    },
    computed: {
        filterObjectRepository() {
            return this.repositoryFactory.create('filter_object');
        },
        filterLinkRepository() {
            return this.repositoryFactory.create('filter_link');
        },
        productRepository() {
            return this.repositoryFactory.create('product');
        },
    },
    created() {
        this.object = this.repositoryFactory.create('filter_object');
        this.link = this.repositoryFactory.create('filter_link');
    },
    methods: {
        async onClickSync() {

            this.isLoading = true;
            await this.removeData();

            this.repository.search(new Criteria(), Shopware.Context.api).then((result) => {
                if (result.length) {
                    var i;
                    var manufacturer = [];
                    for (i = 0; i < result.length; i++) {
                        manufacturer.push(result[i]['manufacturer']);
                    }
                    var manufacturerFilter = Array.from(new Set(manufacturer));

                    var j;
                    for ( j = 0; j < manufacturerFilter.length; j++) {
                        // some code                       
                    }
                }
            });
        },

        removeData() {
            return this.filterObjectRepository.search(new Criteria(), Shopware.Context.api).then((result) => {
                if (result.length) {
                    var i;
                    for (i = 0; i < result.length; ++i) {
                        this.filterObjectRepository.delete(result[i]['id'], Shopware.Context.api).then(this.loadObject);
                    }
                    return null;                  
                }
            });
        },

        loadObject() {
            this.filterObjectRepository.search(new Criteria(), Shopware.Context.api).then((result) => {
                this.result = result;
            });
        },

    }
});

方案一：数据库外键级联删除（最优解） #

直接在数据库层面配置外键的级联删除规则，当删除filter_object中的数据时，自动删除filter_link中关联的记录，无需额外代码处理，效率最高。

• 操作步骤：
  1. 找到filter_link表中object_id对应的外键约束
  2. 修改该约束，添加ON DELETE CASCADE规则
  3. 之后只需删除filter_object数据，关联的filter_link会被数据库自动清理

方案二：批量删除替代循环单删（代码层面优化） #

如果无法修改数据库配置，可通过批量删除操作减少请求次数，同时先清理关联表再清理主表：

修改removeData方法为异步批量处理：

async removeData() {
    // 1. 获取所有filter_object的ID
    const filterObjects = await this.filterObjectRepository.search(new Criteria(), Shopware.Context.api);
    if (!filterObjects.length) return;

    const objectIds = filterObjects.map(item => item.id);

    try {
        // 2. 批量删除filter_link中关联的数据
        const linkCriteria = new Criteria();
        linkCriteria.addFilter(Criteria.equals('object_id', objectIds));
        const filterLinks = await this.filterLinkRepository.search(linkCriteria, Shopware.Context.api);
        const linkIds = filterLinks.map(item => item.id);
        if (linkIds.length) {
            await this.filterLinkRepository.deleteIds(linkIds, Shopware.Context.api);
        }

        // 3. 批量删除filter_object数据
        await this.filterObjectRepository.deleteIds(objectIds, Shopware.Context.api);

        // 重新加载数据
        await this.loadObject();
    } catch (error) {
        console.error('删除数据失败:', error);
        // 可添加错误提示逻辑
    }
}

关键优化点： #

• 使用deleteIds方法批量删除，替代循环单删，大幅减少API请求次数
• 先删除关联表filter_link数据，再删除主表filter_object数据，保证数据一致性
• 用async/await替代嵌套then，代码更清晰且确保删除操作完成后再执行后续逻辑

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