Updating Many-To-Many Relations

Many-to-many relations require an additional table to store the relation, known as an association table (or junction table). When Delia executes an update statement involving a many-to-many relation, it needs to update both the main table and the association table for the relation.

Let's consider Customer having a many-to-many relation with Address:

type Customer struct {
  id int primaryKey, 
  name string, 
  relation addr Address optional many 
} end
type Address struct {
  id int primaryKey,
  city string, 
  relation cust Customer optional many 
} end

When we update a Customer record, Delia may need to multiple SQL statements to perform the update.

Single Value

update Customer[55] {wid: 333, addr:100}

This statement specifies that Customer 55 has one address relation, to Address 100. Delia needs to ensure that the association table is updated so that there is a single row for Customer 55.

Therefore, Delia needs to delete records if there are multiple records for Customer 55. And it needs to insert a record if it doesn't exist. Delia does this automatically.

Multiple Value

update Customer[55] {wid: 333, addr:[100,101]}

This statement specifies that Customer 55 has two address relations, to Addresses 100 and 101. Delia needs to ensure that the association table is updated so that there is are two rows for Customer 55.

Again, Delia may need to delete or insert records to ensure this result.

Association Table CRUD

The syntax shown above sets the entire relation. This can be difficult if the relation is large, with thousands of values. For example, if there were 1000 addresses for a Customer and you wanted to delete one address from the relation you would need an update statement with 999 values for addr.

update Customer[55] {wid: 333, addr:[100,101,....]} //999 address values

Delia provides a simpler way. You can do insert,update, and delete on individual values in a many-to-many relation.

insert

Add the insert keyword before the relation fieldname, and Delia inserts the value(s) into the assocation table.

update Customer[55] {wid: 333, insert addr:[444]}  

Delia adds a single record:

update

Add the update keyword before the relation fieldname, and Delia updates the value(s) in the assocation table. In this case, the list of values must be pairs of values. The first value in a pair is the existing foreign key value, and the second value is the new value to store. For example, to change the relation from Address 100 to Address 444, do this:

update Customer[55] {wid: 333, update addr:[444,455]}  

The row with (55,444) is updated to this:

delete

Add the delete keyword before the relation fieldname, and Delia deletes the value(s) in the assocation table. It removes a single row:

update Customer[55] {wid: 333, delete addr:[102]}