Change relation one-to-many to many-to-many

[wtconseil]

Hi there

currently, i have in a custom entity a relation "one-to-many" but now... the datamodel needs an update and we should move to a "many to many" relationship.

Is there an "easy" way to change that relation from "one-to-many" to "many to many" ?

I would like to move all my previous data to the new relationships.

Thanks for your advice

[tanya]

Hi,
you need to modify entityDefs manually and move you relation data with sql query.

[wtconseil]

Do you have that kind of sql query ?
Or maybe should i create a new field/relationship to move my data from previous field to new field?

[tanya]

for example you have a relationship contact - tasks.
that's mean one contact could have a few tasks, but one task could have only one contact
that's mean you have in task table a column contact_id

if you set a relationship many-to-many, the new table will be created with name contact_task with columns
id - autoincrementa
contact_id
task_id
deleted (0 by default)

According to this https://www.w3schools.com/sql/sql_in...nto_select.asp , you need something like (didn't test)

INSERT INTO contact_task (task_id, contact_id)
SELECT id, contact_id
FROM task
WHERE deleted=0 AND contact_id IS NOT NULL AND contact_id != ""

But this is only available after you modify your relationship, rebuild the crm and table created with rebuilding

[wtconseil]

thanks tanya that works perfectly !