Tuesday, July 05, 2005
Dealing with in-direct joins
So what happens when your request uses PATIENTS and CHARGES but there is no direct relationship between PATIENTS and CHARGES.
This is resolved with an indirect relationship.
Both of these tables are found in the laUnjoined array in FFBUILDJ.
The starting table is immediately identified as the very first table (in this case, PATIENTS).
So the starting table is PATIENTS and the only thing left in laUnJoined is CHARGES.
The array laIndirect has 19 columns. The first one identifies if the relationship has been completed. The Fourth column is the NAME of the table being searched for.
The fifth column is the table that this table should be linking with.
It then creates an array called laTempJoin that will contain a list of tables that link to that current join (so supposedly).
It starts with CHARGES.
It then finds all tables that HAVE links to that table.
If there are none, it removes it from the lainDirect array.
So now it adds new records into laInDirect for each of these possible link tables.
And then gets rid of the first row so the inDirect array contains a list of all possible relationships to the file.
Now it goes through each row to see if there is already a relationship in place.
This is done by seeing if the table is already linked in the laJoins array. This array holds all of the relationships that already take place.
We're now at line 696 - what do we have?
The starting table and a list of all possible relationships for the files that do not have direct links - but we do have the table they link with.
So now we start by going through the array from last to first. Why last to first?
THAT is the question.
However, it then builds tempJoin1 with the two tables that need to be linked.
So it starts with the linked table (not the one we really want to link with) And tries to build a list of all tables that are linked to that one (the second level of indirection).
And now it builds the inDirect relationship so that inDirect array shows
Desired Child table, First Indirect Table, then Second Indirect table
It updates the second and third columns with the number of relationships required to accomplish it.
Now, it does this with ALL of the indirect arrays first. This may be time consuming because if there is more than one relationship and one of them has lots of other relationships, it still builds them all up.
So in this example, it found three tables that linked to CHARGES: RECRENT, INVENTORY and ACCTSTATUS.
RECRENT had two related tables: PATIENTS and RENTALS.
INVENTORY had INVLOG,LOTS,MATRIX,ORDERS,OTITEMS,PARTSMS,PODATA,RENTALS,RENTALSLOG, TICKETDATA
ACCTSTATUS had BILLNOTES, COMPANY, DOCTORS,HCFA,INSURANCE,NURSELOG,PATIENTS,PAINS and TICKETS
(The correct one in this case SHOULD have been ACCTSTATUS) but the array started with RECRENT (because it was going backwards on line 696)
Now it goes through the new indirect array (beginning to end) (with all the possible second level relationships), and sees if it can find a relationship between the next indirect table and anything in the laJoined array.
If count, it sets llCompleteJoins to .T. and sets the first column of inDirect to .T.
It does NOT exit out of the loop here - so if there were 100 rows in the indirect array, it checks each one. (this can be very time consuming if you're debugging it)
Sorry - did I say time-consuming? I meant, crash-worthy. (do not add the arrays to the watch window when doing this - otherwise you'll be waiting forever)
See the problem is that even though it may find a relationship, it continues to build the indirect relationships for every contention so it can use the "weighted" approach. This allows it to figure out which one to use.
Each additional relationship adds to the "weight". So an indirect join with three tables gets a weight of 2 while a join with 5 tables gets 4.
It then removes all relationships from the array that don't meet the minimum weight and starts with the FIRST ONE. We're playing around with this and seeing if it would work better if it was the last one instead.
Then it builds the relationship into the join string.
Comment
This is resolved with an indirect relationship.
Both of these tables are found in the laUnjoined array in FFBUILDJ.
The starting table is immediately identified as the very first table (in this case, PATIENTS).
So the starting table is PATIENTS and the only thing left in laUnJoined is CHARGES.
The array laIndirect has 19 columns. The first one identifies if the relationship has been completed. The Fourth column is the NAME of the table being searched for.
The fifth column is the table that this table should be linking with.
It then creates an array called laTempJoin that will contain a list of tables that link to that current join (so supposedly).
It starts with CHARGES.
It then finds all tables that HAVE links to that table.
If there are none, it removes it from the lainDirect array.
So now it adds new records into laInDirect for each of these possible link tables.
And then gets rid of the first row so the inDirect array contains a list of all possible relationships to the file.
Now it goes through each row to see if there is already a relationship in place.
This is done by seeing if the table is already linked in the laJoins array. This array holds all of the relationships that already take place.
We're now at line 696 - what do we have?
The starting table and a list of all possible relationships for the files that do not have direct links - but we do have the table they link with.
So now we start by going through the array from last to first. Why last to first?
THAT is the question.
However, it then builds tempJoin1 with the two tables that need to be linked.
So it starts with the linked table (not the one we really want to link with) And tries to build a list of all tables that are linked to that one (the second level of indirection).
And now it builds the inDirect relationship so that inDirect array shows
Desired Child table, First Indirect Table, then Second Indirect table
It updates the second and third columns with the number of relationships required to accomplish it.
Now, it does this with ALL of the indirect arrays first. This may be time consuming because if there is more than one relationship and one of them has lots of other relationships, it still builds them all up.
So in this example, it found three tables that linked to CHARGES: RECRENT, INVENTORY and ACCTSTATUS.
RECRENT had two related tables: PATIENTS and RENTALS.
INVENTORY had INVLOG,LOTS,MATRIX,ORDERS,OTITEMS,PARTSMS,PODATA,RENTALS,RENTALSLOG, TICKETDATA
ACCTSTATUS had BILLNOTES, COMPANY, DOCTORS,HCFA,INSURANCE,NURSELOG,PATIENTS,PAINS and TICKETS
(The correct one in this case SHOULD have been ACCTSTATUS) but the array started with RECRENT (because it was going backwards on line 696)
Now it goes through the new indirect array (beginning to end) (with all the possible second level relationships), and sees if it can find a relationship between the next indirect table and anything in the laJoined array.
If count, it sets llCompleteJoins to .T. and sets the first column of inDirect to .T.
It does NOT exit out of the loop here - so if there were 100 rows in the indirect array, it checks each one. (this can be very time consuming if you're debugging it)
Sorry - did I say time-consuming? I meant, crash-worthy. (do not add the arrays to the watch window when doing this - otherwise you'll be waiting forever)
See the problem is that even though it may find a relationship, it continues to build the indirect relationships for every contention so it can use the "weighted" approach. This allows it to figure out which one to use.
Each additional relationship adds to the "weight". So an indirect join with three tables gets a weight of 2 while a join with 5 tables gets 4.
It then removes all relationships from the array that don't meet the minimum weight and starts with the FIRST ONE. We're playing around with this and seeing if it would work better if it was the last one instead.
Then it builds the relationship into the join string.
Comment
