V8-archive: M2M relation with directus_collections cant be saved

[glappsi]

Bug Report

Steps to Reproduce

1. Create a collection with a primary key
2. Create another collection
3. In the first collection create a M2M relation (lets call it tables) with automatic generated junction table. Choose directus_collections as related collection.
4. Edit table 1, select a table in in field tables and save.

Expected Behavior

Should be saved successfully.

Actual Behavior

Cannot generate the SQL query with the following error:

{"error":{"code":9,"message":"Failed generating the SQL query.","query":"UPDATE `directus_fields` SET `collection` = 'immoscout', `field` = 'id', `datatype` = 'INT', `unique` = '', `primary_key` = '1', `auto_increment` = '1', `default_value` = NULL, `note` = '', `signed` = '', `id` = '1978', `type` = 'integer', `sort` = '0', `interface` = 'primary-key', `hidden_detail` = '1', `hidden_browse` = '1', `required` = '1', `options` = NULL, `locked` = '0', `translation` = NULL, `readonly` = '', `width` = '4', `validation` = NULL, `group` = NULL, `length` = '10' WHERE `id` = '1978'"}}

Technical Details

Using the docker image directus/api:latest (from today) with a mysql mariadb.

[bjgajjar]

Currently Select Existing in M2M will pass the whole object to the backend instead of the only ID. So every single element of the payload will be considered as updatable data.

This issue occurs as directus_collections table contains field object too and inside fields object we have some keys which are not stored in DB [i.e; datatype]

As I mentioned above, because of the whole object got from the payload, the system will try to update the whole object. But this field does not exist in the system. That's why getting an error.

@rijkvanzanten - API is expecting only those data in payload which need to be updated in M2M and O2M. Can we move this to APP?

[rijkvanzanten]

@bjgajjar that's what the app does. The OP here uses Docker and is presumably on an outdated version.

Also, I'm not sure if a relationship to directus_collections will work in the first place, as that would mean that the API will have to create / remove actual tables when saving items

[benhaynes]

I agree that this is likely not an issue since it's reported from Docker (very old).

But a M2M should work to directus_collections since only the junction table would be updated... right?

[rijkvanzanten]

But a M2M should work to directus_collections since only the junction table would be updated... right?

If you create a new item in the M2M interface, it will attempt to create a new record in directus_collections

[benhaynes]

Ahh yes... 👍

[glappsi]

@bjgajjar that's what the app does. The OP here uses Docker and is presumably on an outdated version.

Also, I'm not sure if a relationship to directus_collections will work in the first place, as that would mean that the API will have to create / remove actual tables when saving items

I pulled the image today, so the image is not outdated, but maybe the app inside the image.

The last point is why I would like to hide the Add new button, but that is not possible.

[bjgajjar]

@rijkvanzanten

that's what the app does.

Right now, M2M interface is passing whole object for Select Existing.

I'm not sure if a relationship to directus_collections will work in the first place, as that would mean that the API will have to create / remove actual tables when saving items

Currently, we don't have functionality which can create / remove the table when adding the new item. The new entry will be added to the directus_collections table but that table will not be created in DB. The same issue will be occur for directus_fields, directus_activities and directus_relations table.

IMHO, we should find out the way to add directus_collections, directus_fields, directus_activities and directus_relations tables as M2M or ristrict them to add it as M2M related collection.

[rijkvanzanten]

The second is a better short-term solution. Allowing the creation of fields and collections through a M2M in a regular collection sounds like something that needs way more thinking and time to develop

[bjgajjar]

@rijkvanzanten - May I know the proper location[API/APP] where we should add the condition to restrict these 4 tables?

[bjgajjar]

Fixed in https://github.com/directus/api/pull/1261 and https://github.com/directus/app/pull/2028