A dedicated FillPrepareMany
method has been added to the
TSQLRecord
class, in conjunction with a new
constructor
named CreateAndFillPrepareMany
. This
particular method will:
- Instantiate all Dest
properties of each
TSQLRecordMany
instances - so that the JOINed request will be able
to populate directly those values;
- Create the appropriate SELECT
statement, with an optional WHERE
clause.
Here is the corresponding test included in our regression suite:
Check(MS.FillPrepareMany(aClient, 'DestList.Dest.SignatureTime<>% and id>=? and DestList.AssociationTime<>0 '+ 'and SignatureTime=DestList.Dest.SignatureTime '+ 'and DestList.Dest.Signature<>"DestList.AssociationTime"',[0],[sID[1]]));
Of course, the only useful parameter here is id>=?
which is
used to retrieve the just added relationships in the pivot table. All other
conditions will always be true, but it will help testing the generated SQL.
Our mORMot will generate the following SQL statement:
select A.ID AID,A.SignatureTime A00,A.Signature A01, B.ID BID,B.AssociationTime B02, C.ID CID,C.SignatureTime C00,C.Signature C01 from ASource A,ADests B,ADest C where B.Source=A.ID and B.Dest=C.ID and (C.SignatureTime<>0 and A.id>=:(1): and B.AssociationTime<>0 and A.SignatureTime=C.SignatureTime and C.Signature<>"DestList.AssociationTime")
You can notice the following:
- All declared TSQLRecordMany
instances (only one, renamed
B
in our case) are included in the statement, with all
corresponding Dest
instances (renamed as C
);
- Fields are aliased with short unique identifiers (AID, A01, BID,
B02...
), for all simple properties of every classes;
- The JOIN clause is created (B.Source=A.ID and
B.Dest=C.ID
);
- Our manual WHERE clause has been translated into proper SQL, including the
table internal aliases (A,B,C
) - in fact,
DestList.Dest
has been replaced by C
, the main
ID
property has been declared properly as A.ID
, and
the "DestList.AssociationTime"
text remained untouched, because it
was bounded with quotes.
That is, our ORM did make all the dirty work for you! You can use Delphi-level conditions in your query, and the engine will transparently convert them into a valid SQL statement. Benefit of this will become clear in case of multiple pivot tables, which are likely to occur in real-world applications.
After the statement has been prepared, you can use the standard
FillOne
method to loop through all returned rows of data, and
access to the JOINed columns within the Delphi objects instances:
Check(MS.FillTable.RowCount=length(sID)); for i := 1 to high(sID) do begin MS.FillOne; Check(MS.fID=sID[i]); Check(MS.SignatureTime=MD.fSignatureTime); Check(MS.DestList.AssociationTime=i); Check(MS.DestList.Dest.fID=dID[i]); Check(MS.DestList.Dest.SignatureTime=MD.fSignatureTime); Check(MS.DestList.Dest.Signature=FormatUTF8('% %',[aClient.ClassName,i])); end; MS.FillClose;
Note that in our case, an explicit call to FillClose
has been
added in order to release all Dest
instances created in
FillPrepareMany
. This call is not mandatory if you call
MS.Free
directly, but it is required if the same MS
instance is about to use some regular many-to-many methods, like
MS.DestList.ManySelect()
- it will prevent any GPF exception
to occur with code expecting the Dest
property not to be an
instance, but a pointer(DestID)
value.
Feedback and comments are welcome in our forum - this thread also contains the customer request for this nice feature.