Admin Api - Allow _sync upserts to reset/overwrite relations

[kingschnulli]

Effort: middle/high

Description:

Currently there is no way to overwrite or reset relations when using the batch/_sync api endpoint. This will always force to first delete all relations, then write them again - this also means you will have to either store or recieve all relationship ids before resetting them.

I would suggest having some sort of special bulk action that deletes all relationships of some kind like:

    "delete-product-categories": {
        "entity": "product_category",
        "action": "delete-all",
        "payload": [
            {
                "productId": "000bba26e2044b98a3ee4a84b03f9551"
            },
            { 
                "productId": "5deed0c33b2a4866a6b2c88fa215561c"
            }
        ]
    }

This would need only one part of the related primary key, not all of them.

Even better would be some kind of flexible interface to define the keys to resolve a relation when running any kind of bulk operation like:

    "delete-products-containing-name-acme": {
        "entity": "product",
        "action": "delete",
        "filter": [
            {
                "type": "contains",
                "field": "name",
                "value": "acme"
            }
        ]
    },
    "set-some-fields-for-all-products-containing-name-acme": {
      "entity": "product",
      "action": "upsert",
      "payload": {
           "field": "value",
      },
      "filter": [
        {
            "type": "contains",
            "field": "name",
            "value": "acme"
        }
     ]
   }

Benefits

Highly improved performance and developer experience for 3rd Party ERP Systems when syncing data to/from shopware

[AndreasA]

I would actually like to have such an option on upsert / update of the entity repository to remove all non provided entitiies (related to the foreign key). Doctrine ORM it would be called orphanRemoval if I remember correclty. But although it is similar, it is probably a different issue, therefore I created #3124

[OliverSkroblin]

Hey you both,

I implemented the feature with this commit: 30e574c

Hope this helps you in future projects.

[OliverSkroblin]

        $operations = [
            new SyncOperation('delete-mapping', 'product_category', SyncOperation::ACTION_DELETE, [], [
                ['type' => 'or', 'queries' => [
                    ['type' => 'equals',    'field' => 'productId', 'value' => $ids->get('p1')]
                ]],
            ]),
            new SyncOperation('new-mapping', 'product_category', SyncOperation::ACTION_UPSERT, [
                ['productId' => $ids->get('p1'), 'categoryId' => $ids->get('c1')],
                ['productId' => $ids->get('p1'), 'categoryId' => $ids->get('c2')],
                ['productId' => $ids->get('p1'), 'categoryId' => $ids->get('c3')],
            ]),
        ];

First operation deletes all mappings for a single product, the second operation re-creates it.

You can modify the payload to use equalsAny for multiple products or by category or other conditions

[AndreasA]

@OliverSkroblin Nice. However, it is not the same as removing not provided relations. It does remove all relations and re-inserts them which is not the best regarding SQL performance / indes management and if someone retrieves the data during the delete (except if everything is in a transaction) they would get no relations. Also how would one do this with the repository directly?

[OliverSkroblin]

The sync api handles everything within one transaction, yes.
You can also use a NOT-IN query to enforce that only the not new assigned categories are deleted

        $operations = [
            new SyncOperation('delete-mapping', 'product_category', SyncOperation::ACTION_DELETE, [], [
                [
                    ['type' => 'equals', 'field' => 'productId', 'value' => $ids->get('p1')]
                    ['type' => 'nand', 'queries' => [
                        ['type' => 'equalsAny', 'field' => 'categoryId', 'value' => [$ids->get('c1'), $ids->get('c2'), $ids->get('c3')]
                    ]]
                ],
            ]),
            new SyncOperation('new-mapping', 'product_category', SyncOperation::ACTION_UPSERT, [
                ['productId' => $ids->get('p1'), 'categoryId' => $ids->get('c1')],
                ['productId' => $ids->get('p1'), 'categoryId' => $ids->get('c2')],
                ['productId' => $ids->get('p1'), 'categoryId' => $ids->get('c3')],
            ]),
        ];

[AndreasA]

Ok. that is better. still a bit more complicated than doctrine/orm orphan removal but not that much more complicated and it requires less overhead from the framework.

Can something similar be done directly with the repository? I guess the syncService can be used in that case?

[OliverSkroblin]

Hi @AndreasA,

sorry for the late reply. I was on vacation. The repository can't be used for that, since multiple repository calls are made internally.

As for doctrine/orm, you are right, although I don't think orphan removal is the reason (that would be when you delete the product), that is just how doctrine works with associations.

[OliverSkroblin]

Implemented with this commit

[kingschnulli]

@OliverSkroblin was this documented somewhere in the meantime?