How to fetch the N rows from 1:N relationship in TSQLRecord ?

TLama · 2012-03-29 20:42:29

Hi Arnaud,

I'm really new to mORMot and I would like to ask you, how to fetch the N rows from 1:N relationship using FillMany.
I know this must be answered zillion times here already, but it's probably hidden somewhere deep inside of the posts.
Let's assume I have the following DB Model:

type
  TSQLTaskReceiverList = class;
  TSQLTask = class(TSQLRecord)
  private
    FTitle: RawUTF8;
    FDescr: RawUTF8;
    FReceivers: TSQLTaskReceiverList;
  public
    property Title: RawUTF8 read FTitle write FTitle;
    property Descr: RawUTF8 read FDescr write FDescr;
    property Receivers: TSQLTaskReceiverList read FReceivers;
  end;

  TSQLReceiver = class(TSQLRecord)
  private
    FFirstName: RawUTF8;
    FLastName: RawUTF8;
  published
    property FirstName: RawUTF8 read FFirstName write FFirstName;
    property LastName: RawUTF8 read FLastName write FLastName;
  end;

  TSQLTaskReceiverList = class(TSQLRecordMany)
  private
    FSource: TSQLTask;
    FDest: TSQLReceiver;
  published
    property Source: TSQLTask read FSource;
    property Dest: TSQLReceiver read FDest;
  end;

Now I wanted to get the list of receivers like it's stated in the PDF manual, by using FillMany:

When a TSQLRecordMany published property exists in a TSQLRecord, it is initialized automatically during TSQLRecord.Create constructor execution into a real class instance.
This TSQLRecordMany instance is indeed available to access directly the pivot table records, via FillMany then FillRow, FillOne and FillRewind methods to loop through records

So I've tried to fetch one task record, then FillMany its ReceiverList and tried to access the first receiver record (in real I need to fetch all task receivers, but here I've tried to do it with the only one)

begin
var
  I: Integer;
  TaskID: Integer;
  SQLTask: TSQLTask;
begin
  TaskID := 2;
  // fetch the task with the specified ID
  SQLTask := TSQLTask.CreateAndFillPrepare(FDatabase, 'ID=?', [], [TaskID]);
  try
    // fill the local variable with task data
    if SQLTask.FillOne then
    begin
      // here I've tried to use FillMany, it returns correctly 2 rows to be fetched
      SQLTask.ReceiverList.FillMany(FDatabase, TaskID);
      // I hoped the FillOne here will fill up the SQLTask.ReceiverList record, but it doesn't
      if SQLTask.ReceiverList.FillOne then
      begin
        // here I'm getting the access violation, what am I doing wrong ?
        ShowMessage(UTF8ToString(SQLTask.ReceiverList.Dest.FirstName) +
          UTF8ToString(SQLTask.ReceiverList.Dest.LastName));
      end;
    end;
  finally
    SQLTask.Free;
  end;
end;

What am I doing wrong that I'm getting the access violation when FillOne returned True and FillMany returned correct count of rows to be fetched ?
Or even better, is there a more efficient way to get those task receivers without creating new TSQLTaskReceiverList nor TSQLReceiver instances ?

Sorry for being so (T)Lame
Thanks a lot!

ab · 2012-03-30 07:24:26

FillMany will only set the IDs, not the object in Dest.

If you want to have a true object in Dest, you'll have to use FillPrepareMany() instead of FillMany().

See http://blog.synopse.info/post/2011/12/0 … JOIN-query and the associated documentation.

TLama · 2012-03-30 13:38:20

It tented me to look for a function containing Prepare word, but I was using the latest stable version, where this is not included. Now I know I have to keep my eyes opened, because the development of this project is really very active. Thanks for the response and for the whole project!

ab · 2012-03-30 14:55:48

Yes, there are indeed a lot of useful functions in the trunk.

If not explicitly noticed as such, the trunk is also very stable - all regression tests are performed before each commit.

Some undocumented features may be not working, but if it is documented, it is working.

mORMot Open Source

#1 2012-03-29 20:42:29

How to fetch the N rows from 1:N relationship in TSQLRecord ?

#2 2012-03-30 07:24:26

Re: How to fetch the N rows from 1:N relationship in TSQLRecord ?

#3 2012-03-30 13:38:20

Re: How to fetch the N rows from 1:N relationship in TSQLRecord ?

#4 2012-03-30 14:55:48

Re: How to fetch the N rows from 1:N relationship in TSQLRecord ?

Board footer