cant add item to collection with many to many and many to one relationships

[beac0n]

Bug Report

Steps to Reproduce

1. create a collection with many to many and many to one relationships
2. try to add a new item

Expected Behavior

item should be created

Actual Behavior

error:
{"error":{"code":9,"message":"Failed generating the SQL query.","query":"INSERT INTO users (name, email, role, user_id, locations) VALUES ('test', 'test@test.test', 'test', '2', NULL)"}}

were locations is the many-to-many relationship
and user_id is the many-to-one relationship

where locations is the many

Other Context & Screenshots

this error does NOT occure in the docker image version 2.0.8
this error does occure in the docker image version 2.0.9 and 2.0.10

Technical Details

used the docker image with mysql database

[wellingguzman]

Hey @beac0n, can you share a database dump we can use to reproduce this issue?

What are the relationship set to in directus_relations for the locations field?

[beac0n]

directus_relations table:

the junction table is users_to_locations

id,collection_many,field_many,collection_one,field_one,junction_field
1,directus_activity,action_by,directus_users,NULL,NULL
2,directus_activity_seen,user,directus_users,NULL,NULL
3,directus_activity_seen,activity,directus_activity,NULL,NULL
4,directus_collections_presets,user,directus_users,NULL,NULL
5,directus_collections_presets,group,directus_groups,NULL,NULL
6,directus_files,uploaded_by,directus_users,NULL,NULL
7,directus_files,folder,directus_folders,NULL,NULL
8,directus_folders,parent_folder,directus_folders,NULL,NULL
9,directus_permissions,group,directus_groups,NULL,NULL
10,directus_revisions,activity,directus_activity,NULL,NULL
11,directus_user_roles,user,directus_users,roles,role
12,directus_user_roles,role,directus_roles,users,user
13,directus_users,avatar,directus_files,NULL,NULL
14,directus_fields,collection,directus_collections,fields,NULL
15,booking_templates,thumbnail,files,NULL,NULL
16,booking_templates,template,files,NULL,NULL
17,booking_template_inputs,booking_template_id,booking_templates,inputs,NULL
18,bookings_to_locations,booking_id,bookings,locations,location_id
19,bookings_to_locations,location_id,locations,NULL,booking_id
20,bookings_to_files,booking_id,bookings,files,file_id
21,bookings_to_files,file_id,files,NULL,booking_id
22,bookings,booking_template,booking_templates,NULL,NULL
23,booking_inputs,booking_id,bookings,inputs,NULL
24,users,user_id,directus_users,NULL,NULL
25,users_to_locations,user_id,users,locations,location_id
26,users_to_locations,location_id,locations,NULL,user_id
27,users_to_booking_templates,user_id,users,booking_templates,booking_template_id
28,users_to_booking_templates,booking_template_id,booking_templates,NULL,user_id

json to add item:
{"booking_templates":[{"booking_template_id":{"name":"rechts","category":"asdasdasd","duration_calculator":"(data) => data.media.value"}}],"name":"rechts","locations":[{"location_id":{"active":true,"width":123,"height":123}}],"email":"awsd","role":"asd"}

response:
{"error":{"code":9,"message":"Failed generating the SQL query.","query":"INSERT INTO users (booking_templates, name, locations, email, role) VALUES (NULL, 'rechts', NULL, 'awsd', 'asd')"}}

[wellingguzman]

@beac0n Can you send a database example I can use to replicate this?

[beac0n]

@wellingguzman I sent it to you via slack :)