One-to-many relationship not ordered as expected

Question

Question

Laura Cavanaugh · Oct 26, 2022

#Databases #Data Model #Object Data Model #ObjectScript #SQL #Caché

Hello all; I am using a one-to-many relationship. I have a Claim (one), in a relationship with Lines (many), but the Claim is storing a list of Lines IDs, rather than the Lines storing the Claim Id. This seems upside down, and not what I expected.

Class Claim

{
    Relationship ClaimLineRel as ClaimLine [Cardinality = many, Inverse = Claim];
}

Class ClaimLine
{
    Relationship Claim as Claim [ Cardinality = one, Inverse = ClaimLineRel, OnDelete = cascade];
    Index ClaimIndex on Claim;
}

But the storage globals for Claim show data in the ClaimLine spot:

^ClaimD(1)=$lb("",$lb(2,3)... etc.)

while the ClaimLine storage has nothing int he Claim spot:

^ClaimLineD(2) = $lb("","",... etc)

How can I force the relationship to use the Claim as the "parent' without using a parent-child relationship?

Id like to be able to use the SQL:

Select Claim->property from ClaimLine

Thanks,

Laura

Product version: HealthShare 2019.1

Discussion (3)1

Log in or sign up to continue

Robert Cemper · Oct 26, 2022

set claim=##class(Claim).%OpenId(claimnum)  ; or similar
set line=##class(ClaimLine).%OpenId(linenum) ; or similar
do claim.ClaimLineRel.Insert(line)

now your query should work

1 0

score 0 · Answer 1 · 2022-10-26T11:10:39-04:00

I can't use

Select claim.ClaimNumber, line.ProcedureCode from Claim
JOIN ClaimLine line ON claim.ID = line.Claim

because there is no line.Claim value.

score 0 · Answer 2 · 2022-10-28T14:26:31-04:00

Ah, I should mention that this is happening in code that I can't access. I can't really tel what's going on except by looking in the database after it's all "said and done". I can try that with one particular row in the database, but it's happening upstream, in othercode, and I'm wondering now if it's because the other code is possibly saving the Claim object differentlly.

However, this is a good idea. I'll play arond with a couple of objects that are already in the database, and se if I can save them to be "right side up", and then "upside down" again.