I have a table in the database with multiple linked tables. The data in these separate tables is stored outside the main table because it is optional. There will only ever be a maximum of one linked record in each of the linked tables for each record in the main table.
To simplify linkage, the linked tables have the same primary key ID as the record in the main table
Primary table - Samples
Primary Key - ASN - integer
other fields
Secondary table - Moistures
Primary Key - ASN - integer
other fields
Secondary table - Turbidities
Primary Key - ASN - integer
other fields
// plus three more secondary tables
So "the old way", I do a select thus:
select * from Samples s
left join Moistures m on m.ASN = s.ASN
left join Turbidities t on t.ASN = s.ASN
// plus three more left joins
where s.ASN = :param
So far, so good with Aurelius reading a sample - it creates basically the same SQL.
My entity classes are set up thus
[Entity]
[Table('SAMPLES')]
[UniqueKey('SAMPLE_ID')]
[Sequence('ASN_GEN')]
[Id('FASN', TIdGenerator.IdentityOrSequence)]
TSamples = class
private
[Column('ASN', [TColumnProp.NoUpdate])]
FASN: integer;
........
[Association([], CascadeTypeAll)]
[JoinColumn('ASN', [])]
FSampling: TSAMPLES_SAMPLING;
[Association([], CascadeTypeAll)]
[JoinColumn('ASN', [])]
FLipids: TSAMPLES_LIPIDS;
[Association([], CascadeTypeAll)]
[JoinColumn('ASN', [])]
FMoisture: TSAMPLES_MOISTURES;
[Association([], CascadeTypeAll)]
[JoinColumn('ASN', [])]
FSubClient: TSAMPLES_SUB_CLIENTS;
[Association([], CascadeTypeAll)]
[JoinColumn('ASN', [])]
FTurbidity: TSamplesTurbidity;
.............
end;
[Entity]
[Table('SAMPLES_TURBIDITY')]
[Id('FASN', TIdGenerator.None)]
TSamplesTurbidity = class
private
[Column('ASN', [TColumnProp.Required])]
FASN: integer;
[Column('TURBIDITY', [])]
FTURBIDITY: Nullable<double>;
..........
end;
and similar for the other secondary tables
If I need to associate a secondary table, I execute code similar to
procedure TfrmAutoProMain.TestAssociation(Sender: TObject);
var
sample : TSAMPLES;
begin
sample := ObjectManager.Find<TSAMPLES>(186207);
if sample.Turbidity = nil then
sample.Turbidity := TSamplesTurbidity.Create;
sample.Turbidity.ASN := sample.ASN;
sample.Turbidity.Turbidity := 98.76;
ObjectManager.Flush;
end;
However, when I try to do an update of my entity objects, Aurelius tries to update the ASN value in the primary table multiple times and errors out, even if I place a noupdate option in the primary tables ASN field settings. The SQL generated is
UPDATE SAMPLES SET
ASN = :A_ASN,
ASN = :A_ASN,
ASN = :A_ASN,
ASN = :A_ASN,
ASN = :A_ASN
WHERE ASN = :p_5;
The only way I can find to get around this is to include multiple foreign key holders in the primary table and point these to each of the (five) secondary tables. Am I doing something wrong, or is this setup just not possible?
OK, I figured it out. I'd put the noInsert and noUpdate flags on the Sample table key when they should have been on the associations JoinColumn, thus
[Association([], CascadeTypeAll)]
[JoinColumn('ASN', [TColumnProp.NoInsert, TColumnProp.NoUpdate])]
FLipids: TLipids;
Everything seems to work now - still on the learning curve!