· May 12, 2021

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

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

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.

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.