I have been messing around with nested CDTs recently, in particular One-to-Many Master-Detail relationships. This is maintained on the database level via a foreign key on the child table. An example structure is shown below:
CREATE TABLE `Parent`( `id` INT PRIMARY KEY, `name` VARCHAR(200) NULL);CREATE TABLE `Child`( `id` INT PRIMARY KEY, `parentId` INT NOT NULL, `name` VARCHAR(200), FOREIGN KEY(`parentId`) REFERENCES `Parent`(`id`));
I am able to get the CDTs setup so that I can retrieve data from the database and it comes through via querying the Parent with no problems. I can also save the Parent as long as all Parent and Child columns are not null and valid.
However, I am unable to save the Parent if any of the children have a 'parentId' that is NULL. I find this a little odd given that the relationship is already defined in the Parent CDT that the join exists. The bigger issue is if both the Parent and Child have null IDs, I can't persist them due to an org.hibernate.PropertyValueException, i.e. the Child can't be saved because its Parent reference is NULL. The former problem can be resolved by simply populating the parentId before persisting, but the latter requires two persists from Appian's end to resolve.
The obvious fix is to save these one at a time: parent then child. However, has anyone managed to find a way to save these in one call to Write to Data Store Entity or am I required to persist twice?
Discussion posts and replies are publicly visible
I was able to get this to work two ways: One way was to make the parent cdt refer to the child cdt (nested cdt) and setup a JOIN COLUMN for the list of child cdt. Unfortunately, doing it this way involves removing the parentId NOT NULL constraint because of the way Hibernate does things under the hood. Hibernate will write the parent entry first, the child entries without a foreign key entry next, and finally it will update the child entries to the correct parent id reference. This sequence will fail with the non-null constraint. You can read more about this scenario and Hibernate here. The second way I got this to work was to have the child cdt refer to the parent cdt (another nested cdt). I also had to make sure the cascade type was ALL in the child CDT's reference to the parent CDT. Then in the process, the parent CDT has to be prepped and the child CDT has to refer to the parent CDT. The write to datastore will be using the child data store entity. Hope this helps.