DataMgr 2.0 Documentation: Save Many-to-Many Relationships

Save Many-to-Many Relationships

Saving many-to-many relationships can be a tedious task. From the perspective of saving data, the easiest thing to do is save a list in the table (especially since checkboxes return lists). Unfortunately, that is also the hardest to deal with everywhere else. It can be tedious to add and remove records as needed for every relationship.

DataMgr makes the process of adding and removing records in a join table as easy as saving a list.

For this example, I have three tables: The "employees" table with a primary key of "employee_id", a "groups" table with a primary key of "group_id", and an "employees2groups" table to store the relationship between them with a joint primary key consisting of two fields" "employee_id" and "group_id" which relate to the fields of the same name in the other two tables (this part is required for DataMgr to work here).

Each employee can have multiple groups and each group can have multiple employees.

For this example. Form.groups contains a list of "group_id" values that relate to the employee with an id equal to the value of Form.id:

<cfset Application.DataMgr.SaveRelationList("employees2groups","employee_id",Form.id,"group_id",Form.groups)>

This would delete records from the "employees2groups" table for the given employee_id that have values for "group_id" that aren't in the Form.groups list. It will add records to the table for values in the Form.groups list that do not exist. It will not effect existing records that should continue to exist.