RELATE ONE([Table_Name]) syntax and thread-safe

Hi

We have one case in generic coding where we use RELATE ONE($pTable->) which fetches related records from all tables.

However, for some reason the RELATE ONE command is not thread-safe (presumably because of the alternative syntax that allows some (fairly outdated UI, in my opinion) to be shown)

Is there a workaround to generically load all related one records for a table?

Thanks

Best regards

Keith

Hi Keith,

Have you considered using ORDA? It is thread-safe and accessing parent (One) record or children (Many) record is super easy.

Add

Have you considered using ORDA?

Yes, however this is used in the middle of a mountain of existing code. I’ll take a look at using the Create entity selection command to try and push this API output section over to ORDA. At least that may not involve an entire application rewrite.

The most easy (and fastest) way could be a simple query.
RELATE ONE is internally only doing a query.
The benefit is, you do not need to know the impacted fields. But if you know (and I assume you do), you can also run a manual query with same speed, preemptive, and no need to “convert” selection to entity selection, which is another network request.

Hi Thomas

Thanks for replying.

The RELATE ONE syntax I’m talking about is the one using [Table] where it loads the related records for all related tables. This is useful in generic code because we can then generically reference related values from all related tables in our API output.

Regards

Keith

I understand. As this is the “most expensive” version I tried to avoid that as much as possible. But I understand for a generic approach.

I’m not sure ORDA will help you to get a selection back in 4D classic mode, as ORDA is optimized for lazy loading.

If you fail doing so, a possible approach could be to export your structure to project to get the catalog.xml file, store that in resource folder or in a record for deployment.
Then read the file to learn about all relations from a table.
And then do a query for every relation.
This way you can create a thread safe wrapper command.
I know, this is quite some work, only wanting to mention that it is possible

For reading that file I created a proof of concept, but never finished the work. But the part about relations should work, at least for my tested structures…
You find example “readings” at:

for your need take this part:

$relations:=$structure.getRelationInfos(New object:C1471("fromTable_name";"Auftrage";"toTable_name";"Kunden"))
$relations:=$structure.getRelationInfos(New object:C1471("Table_name1";"Auftrage";"Table_name2";"Kunden"))  // any direction
$relations:=$structure.getRelationInfos(New object:C1471("Table_name";"Auftrage"))  // any direction
$relations:=$structure.getRelationInfos(New object:C1471("fromTable_name";"Kunden"))
$relations:=$structure.getRelationInfos(New object:C1471("fromTable_name";"A@"))
$relations:=$structure.getRelationInfos(New object:C1471("toTable_name";"Auftrage"))

“Kunden” is the name of the table in this example.
$relations is a collection describing each relation, easy to parse and use…

The code above shows how to get that object.
You need to copy some methods from that component into your structure (to convert the XML into an object and then the parsing methods.

Hopefully that helps you…

Hi Thomas,
strange you say that… In the “before orda world”, I very often use this syntax because, when links are set just before with SET FIELD RELATION, it’s the better way I found to get all the wanted “parents” of a current record. For example, with this structure:
[invoice_line] -> [invoice] -> [customer] -> [country]
[invoice_line] -> [product] -> [productCategory]
if I need all the “parents” of an invoice_line (FK_name = foreign key of table [name]) :

set field relation([invoice_line]FK_invoice;automatic;do not modify)
set field relation(invoice]FK_customer;automatic;do not modify)
set field relation([customer]FK_country;automatic;do not modify)
set field relation([invoice_line]FK_product;automatic;do not modify)
set field relation([product]FK_productCategory;automatic;do not modify)
relate one([invoice_line])

That’s not generic at all, it’s just fast, one request instead of five, very appreciated on wan…

As Keith said,

I’d strongly prefer the UI syntax to be deprecated and the command to be tread safe, or split in 2 commands…

Interesting point! I wonder how the network request is different compared to the “proxy by query” approach.

I’m not too worried about the network request side because in my case the code is already executing on the server. My guess would be the ActivateManyToOneS request.

A loooong time ago, I seem to recall there was a 4D command called ACTIVATE LINK or something like that, so it’s possible this naming has persisted.

Unless I miss something, I’d say it exists already with set field relation, but better: ActivateManyToOneS I want. To illustrate, with that example from doc: the blue one or the pink one?

Hi Keisuke,
very interesting…
I feel like a newbie: how do you get that json?
What do you mean with “proxy by query”?

I don’t know, the file is published on 4D’s GitHub repository.

Sorry, that was lazy writing on my part. I meant, emulation of the RELATE ONE command by introspection and multiple calls of QUERY on related fields.

1 Like

Hi

I’ve written a routine for this. It currently doesn’t cope with passing in a list of the relations or tables required, but could be adapted to do that. If anyone has a moment, I’d appreciate a code review. I’m not sure what process 4D uses to establish all relations, the routine currently uses the first related field for each table.

  // Relate_One_Table
  //

C_POINTER($pTable;$1)
C_LONGINT($nTable;$nField;$nOneTable;$nOneField;$nLastField;$nPos)
C_POINTER($pField;$pOneTable;$pOneField)

$pTable:=$1

ARRAY POINTER($apTables;1)
ARRAY POINTER($apTablesDone;0)
$apTables{1}:=$pTable

$bExit:=False
Repeat 
	$pTable:=$apTables{1}
	$nTable:=Table($pTable)
	ARRAY POINTER($apFields;0)
	ARRAY POINTER($apOneTables;0)
	ARRAY POINTER($apOneFields;0)
	$nLastField:=Get last field number($pTable)
	For ($nField;1;$nLastField)
		If (Is field number valid($nTable;$nField)=True)
			GET RELATION PROPERTIES($nTable;$nField;$nOneTable;$nOneField)
			If ($nOneTable#0)
				$pOneTable:=Table($nOneTable)
				$nPos:=Find in array($apTablesDone;$pOneTable)
				If ($nPos<=0)
					$nPos:=Find in array($apOneTables;$pOneTable)
					If ($nPos<=0)
						$pField:=Field($nTable;$nField)
						$pOneField:=Field($nOneTable;$nOneField)
						APPEND TO ARRAY($apFields;$pField)
						APPEND TO ARRAY($apOneTables;$pOneTable)
						APPEND TO ARRAY($apOneFields;$pOneField)
					End if 
				End if 
			End if 
		End if 
	End for 
	
	For ($nField;1;Size of array($apFields))
		$pField:=$apFields{$nField}
		$pOneTable:=$apOneTables{$nField}
		$pOneField:=$apOneFields{$nField}
		QUERY($pOneTable->;$pOneField->=$pField->)
		APPEND TO ARRAY($apTables;$pOneTable)
		APPEND TO ARRAY($apTablesDone;$pOneTable)
	End for 
	
	DELETE FROM ARRAY($apTables;1)
Until (Size of array($apTables)=0)

Thanks for any help

Was not the objective to get the “one record” as far as the link takes?

I expect the code would have to be recursive.

Yes, I was thinking the same: why the father and not the grandfather, then the grand-grandfather, etc. ? But then I suppose the following code could do the job as well, no?

SET AUTOMATIC RELATIONS(true; false)
//do the stuff with table x
SET AUTOMATIC RELATIONS(false; false)

I suppose the 1st field is used in case of many foreign keys to the same table. But I don’t find it in the doc: heads or tails.

Hi.

Thanks for replying. Yes, indeed that is the idea.

I tried using recursion and it covered the same 23 tables in the relation chains, but took a different relation path (because recursive goes off down the first relation chain exhaustively) than the above code. With the above code, the RELATE ONE command is different somewhere but I can’t fathom what mechanism 4D uses for deciding the path.

Results for the recursive version in my test case was 3 different records retrieved for 3 tables.
Results for the above version in my test case was 1 different record retrieved for 1 table.

Neither are the same as RELATE ONE, but the above code is a nearer match.

Also doing some time trials for 1000 records (all executed server side):-

RELATE ONE x 1000 iterations takes a total of 282 milliseconds

Above code x 1000 iterations takes approx 12000 milliseconds

I’ve just completed a change around to build a list of relations first into IP arrays which a new method can then use to iterate over. Speed is improved with that but still takes 2800 milliseconds for 1000 iterations.

Of course, I can’t use IP arrays with thread-safe either, but I figure I could refactor that to use one shared collection per table or something like that.

I’m really struggling here to try and work out an alternative solution.

This code may help to understand what 4D does, I hope. I wrote it last year, I needed to understand what relations where activated “generically” during refactoring.

  //Rel_pushPull { (relation_c) -> coll
  //push-pull de l'état des relations (manuels/auto)
  //pas de paramètre : $0 retourne l'état des liens
  //$1 passé = état dans lequel remettre les liens
  //exemple :
  //  C_COLLECTION($relation_c)
  //  $relation_c:=Rel_pushPull   //get
  //    .............
  //  Rel_pushPull ($relation_c)  //set as it was in get

  //© Arnaud * 24/06/2019

C_COLLECTION($0;$1;$relation_c)

C_OBJECT($relation_o)
C_LONGINT($i_l;$j_l;$tableDest_l;$champDest_l;$discri_l;$aller_l;$retour_l)
C_BOOLEAN($aller_b;$retour_b)
C_POINTER($fld_p)

If (Count parameters=0)  //get
	$relation_c:=New collection
	For ($i_l;1;Get last table number)
		If (Is table number valid($i_l))
			For ($j_l;1;Get last field number($i_l))
				If (Is field number valid($i_l;$j_l))
					GET RELATION PROPERTIES($i_l;$j_l;$tableDest_l;$champDest_l;$discri_l;$aller_b;$retour_b)
					If ($tableDest_l>0) & ($champDest_l>0)  //est un départ de lien
						$fld_p:=Field($i_l;$j_l)
						GET FIELD RELATION($fld_p->;$aller_l;$retour_l)
						$relation_c.push(New object("table";$i_l;"champ";$j_l;"aller";$aller_l;"retour";$retour_l))
					End if 
				End if 
			End for 
		End if 
	End for 
	$0:=$relation_c
Else   //set
	$relation_c:=$1
	For each ($relation_o;$relation_c)
		  //$fld_p:=Field($relation_o.table;$relation_o.champ)
		  //$fld_p:=Field((Num($relation_o.table));(Num($relation_o.champ)))
		  //$fld_p:=Field(Int(Num($relation_o.table));Int(Num($relation_o.champ)))
		$i_l:=$relation_o.table
		$j_l:=$relation_o.champ
		$fld_p:=Field($i_l;$j_l)
		SET FIELD RELATION($fld_p->;$relation_o.aller;$relation_o.retour)
	End for each 
End if 
  //_

By the way, when implementing recursive introspection, one must detect a “circular firing squad” situation, where a relation comes back to the original table via several other tables.

OK, so having tried recursion it didn’t offer anything more than my original method and it went a different (and incorrect) path to RELATE ONE

I found my original difference with RELATE ONE and it was a relation to a longint key with a value of 0 (anomalous data in this case). I’ve now corrected for that in the original code and I get the same result as RELATE ONE.

Due to the poor performance of my original attempt here is my latest. It consists of a need to build a relations structure in memory first. For this I’m using a process variable object that needs to be setup. I could use a shared object built at startup, but I’d have to re-work to avoid storing pointers.

The result still isn’t as quick as RELATE ONE, but it will have to suffice given I’ve already spent a lot of time on this.

  // Structure_Relations_Collections
  //
C_LONGINT($nTable;$nField;$nToTable;$nToField)
C_POINTER($pTable;$pField)

C_OBJECT(oStructureRelations)
C_COLLECTION($oColl)
oStructureRelations:=New object

For ($nTable;1;Get last table number)
	If (Is table number valid($nTable)=True)
		$pTable:=Table($nTable)
		$bThisTableColl:=False
		$cTableKey:="TableRel_"+String($nTable)
		For ($nField;1;Get last field number($nTable))
			If (Is field number valid($nTable;$nField)=True)
				$pField:=Field($nTable;$nField)
				GET RELATION PROPERTIES($pField;$nToTable;$nToField)
				If ($nToTable>0)
					If ($bThisTableColl=False)
						$bThisTableColl:=True
						$oColl:=New collection
					End if 
					$oColl.push(New object("FromField";$pField;"ToTable";Table($nToTable);"ToField";Field($nToTable;$nToField)))
				End if 
			End if 
		End for 
		
		If ($bThisTableColl=True)
			oStructureRelations[$cTableKey]:=$oColl
		End if 
		
	End if 
End for 
  // Relate_One_Table_Collections
  //

C_POINTER($pTable;$1)
C_LONGINT($nQueryTableCount;$0)
C_LONGINT($nTable;$nPos)
C_POINTER($pField;$pOneTable;$pOneField)
C_TEXT($cTableKey)
C_COLLECTION($oColl)
C_OBJECT($oRelation)

$pTable:=$1

ARRAY POINTER($apTables;1)
ARRAY POINTER($apTablesDone;0)
$apTables{1}:=$pTable

Repeat 
	$pTable:=$apTables{1}
	$nTable:=Table($pTable)
	$cTableKey:="TableRel_"+String($nTable)
	If (oStructureRelations[$cTableKey]#Null)
		$oColl:=oStructureRelations[$cTableKey]
		
		For each ($oRelation;$oColl)
			$pOneTable:=$oRelation.ToTable
			$nPos:=Find in array($apTablesDone;$pOneTable)
			If ($nPos<=0)
				APPEND TO ARRAY($apTablesDone;$pOneTable)
				
				If (Records in selection($pTable->)=0)
					REDUCE SELECTION($pOneTable->;0)
				Else 
					$pOneField:=$oRelation.ToField
					$pField:=$oRelation.FromField
					QUERY($pOneTable->;$pOneField->=$pField->)
				End if 
				$nQueryTableCount:=$nQueryTableCount+1
				APPEND TO ARRAY($apTables;$pOneTable)
			End if 
		End for each 
		
	End if 
	DELETE FROM ARRAY($apTables;1)
Until (Size of array($apTables)=0)

$0:=$nQueryTableCount

Hi Keith,
I’m not trying to make you spend more time, first :slight_smile: . Just thinking from the doc:

RELATE ONE has two forms.
The first form, RELATE ONE (manyTable), establishes all automatic Many-to-One relations for manyTable in the current process. This means that for each field in manyTable that has an automatic Many-to-One relation, the command will select the related record in each related table.

Makes me say that, if one is willing to mimic exactly what RELATE ONE do, it’s just a matter of testing any N->1 relation (with get field relation) found in the source table. If it’s found as Automatic, query the parent. Then, if that parent also has a relation N->1 found as automatic, query again. And so on. Hence the recursive code.

I’ve been wondering if it could be interesting to write something similar to “translate” legacy code making such propagations to orda, BTW: find with orda what was found by Automatic, but it seems it’s more clever to use explicit relations instead… On a reverse way (orda to legacy), for printing commands, maybe…