Does a 1:n relationship keep the order of insertion?

Question

Question

Martin Staudigel · May 12, 2021

Hello Community,

we read data from an Oracle database. The desired order is created by an 'order by' in the sql statement. The individual elements of the result set are converted into objects and inserted into a parent object using 'insert' on a property, which implements a one-to-many relationship with the result objects. Later, we iterate over the objects in a for loop and process the contents. At this point the order is apparently no longer identical to the order in which the elements were inserted.

The fundamental question at this point: Is the described relationship aware of the concept of sequence and also reliably assures it? An implementation in use relies on this, but recently the order of the results in the cache object is no longer identical to the sorting of the SQL select. Is it possible that this has only worked by luck over the years?

Regards and thanks for your support,

Martin Staudigel

Product version: Caché 2018.1

$ZV: Cache for UNIX (SUSE Linux Enterprise Server for x86-64) 2018.1.3 (Build 414_0_19402U) Mon Nov 18 2019 22:54:54 EST [HealthShare Modules:Core:15.032.9026 + Linkage Engine:15.032.9026]

Discussion (5)2

Log in or sign up to continue

Robert Cemper · May 12, 2021

if you use Relationship many As obj [ Cardinality = many, Inverse = one ];
you create a managed pointer from many->one with no sequence of insert.
Though it may be kept if new "many" elements are inserted in sequence with ascending IDs.
Later (manual?) add to the relationship of already exisiting "many" may break this.

To bypass this limitation I see 2 possible solutions:
- you add a property (auto-incremented, insert sequencer) to "many" to keep the insert sequence
e.g. Property InsertSequence As %Integer [ InitialExpression = {$increment(^InSeq)} ];
which is rather brute force, but available to manual adjustment for existing data

- you add to "one" side: Property ManyList As %ListOfObjects;
and add your many with Insert() function at and the end of the individual list.
The advantage of this approach is to have the freedom to change the sequence at your needs
And it also allows a kind of m:n relation as you are not limited to add your many to a unique one.
This is not my preferred solution and requires some coding erfort.

0 0

Arun Tiriveedhi · May 13, 2021

We had a similar issue with the object insertion to a parent object.

The way we were able to ensure the sequence, was saving the parent object on each insert. There is an overhead and not the most efficient process.

Since we were saving the same object without closing it, no additional parents are created.

0 0

score 0 · Answer 1 · 2021-05-12T05:37:10-04:00

Thanks for your reply,

a look into the table of the 'many' data objects confirms that the wrong order is already reflected in the ID of these objects. Since the ID is assigned in ascending order at the time of creation, I don't quite understand why it doesn't reflect the order of the result set.

Anyway, with the hints I can continue the search and make adjustments if necessary to resolve the dependency on implicitly assigning an ascending Object ID.

Regards,
Martin

score 1 · Answer 2 · 2021-05-17T09:11:39-04:00

If controlling order is important, and you don't need the bi-directionality of 1-M relationships, you can use a list or an array collection instead.

score 0 · Answer 3 · 2021-05-18T06:08:42-04:00

Finally, Arun's suggestion to write the data objects to the database on the many side using %Save() at the time of creation worked. Regarding performance, this did not result in any significant losses.Thanks to all for the helpful comments and remarks.