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.