Synopse SQLite select rows for 1:N relationship

I'm playing with the Synopse's SQLite implementation, but I'm stucked with the following piece of code. In the form constructor I create a database model where there are two tables Task and Comment and one table TaskComments with relationship 1:N for task comments. I can add the rows into the TaskComments table (Button1.OnClick event add one task and two comments for it) but I don't know how to get comments for this task back.

Can anyone suggest me how to get the N rows for a certain row (how to get comments for the task, in this case) ?

unit SynopseSQLiteTestUnit;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, SynCommons, SQLite3, SQLite3Commons, StdCtrls;

type
  TTask = class(TSQLRecord)
  private
    FTaskName: RawUTF8;
    FTaskCreated: TDateTime;
  published
    property TaskName: RawUTF8 read FTaskName write FTaskName;
    property TaskCreated: TDateTime read FTaskCreated write FTaskCreated;
  end;

  TComment = class(TSQLRecord)
  private
    FCommentText: RawUTF8;
    FCommentCreated: TDateTime;
  published
    property CommentText: RawUTF8 read FCommentText write FCommentText;
    property CommentCreated: TDateTime read FCommentCreated write FCommentCreated;
  end;

  TTaskComments = class(TSQLRecordMany)
  private
    FTask: TTask;
    FComment: TComment;
  published
    property Task: TTask read FTask;
    property Comment: TComment read FComment;
  end;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Memo1: TMemo;
    Memo2: TMemo;
    Memo3: TMemo;
    procedure FormCreate(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    FDatabase: TSQLRestClientURI;
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var
  SQLModel: TSQLModel;
begin
  SQLModel := TSQLModel.Create([
    TTask,
    TComment,
    TTaskComments
  ]);
  FDatabase := TSQLRestClientDB.Create(SQLModel, SQLModel, ChangeFileExt(Application.ExeName,'.db3'), TSQLRestServerDB);
  TSQLRestClientDB(FDatabase).Server.CreateMissingTables(0);
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  Task: TTask;
  TaskID: Integer;
  Comment: TComment;
  CommentID: Integer;
  TaskComments: TTaskComments;
begin
  Task := TTask.Create;
  Comment := TComment.Create;
  TaskComments := TTaskComments.Create;

  try
    Task.TaskName := StringToUTF8('Task Name');
    Task.TaskCreated := Now;
    TaskID := FDatabase.Add(Task, True);

    Comment.CommentText := StringToUTF8('Comment Text 1');
    Comment.CommentCreated := Now;
    CommentID := FDatabase.Add(Comment, True);

    TaskComments.ManyAdd(FDatabase, TaskID, CommentID);

    Comment.CommentText := StringToUTF8('Comment Text 2');
    Comment.CommentCreated := Now;
    CommentID := FDatabase.Add(Comment, True);

    TaskComments.ManyAdd(FDatabase, TaskID, CommentID, True);

  finally
    FreeAndNil(Task);
    FreeAndNil(Comment);
    FreeAndNil(TaskComments);
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
var
  Task: TTask;
  Comment: TComment;
  TaskComments: TTaskComments;
begin
  Memo1.Clear;
  Memo2.Clear;
  Memo3.Clear;

  // here I want to select task with ID = 1, that's fine
  Task := TTask.CreateAndFillPrepare(FDatabase, 'ID = 1');
  // here I want to select all comments, that's fine
  Comment := TComment.CreateAndFillPrepare(FDatabase, '');
  // here I want to create the task comments, ok
  TaskComments := TTaskComments.Create;

  try
    // here I'm filling the memo boxes with the task and all comments, ok
    while Task.FillOne do
      Memo1.Lines.Add(UTF8ToWideString(Task.TaskName));
    while Comment.FillOne do
      Memo2.Lines.Add(UTF8ToWideString(Comment.CommentText));

    // here I'm trying to get all comments for task with ID = 1
    // but the FillOne function returns always False, what means, that
    // I don't get any row fetched
    TaskComments.FillMany(FDatabase, 1);
    while TaskComments.FillOne do
      Memo3.Lines.Add(UTF8ToWideString(TaskComments.Task.TaskName) + '; ' + UTF8ToWideString(TaskComments.Comment.CommentText));

  finally
    FreeAndNil(Task);
    FreeAndNil(Comment);
    FreeAndNil(TaskComments);
  end;
end;

end.

Many thanks

Solution

You should have posted this on the official mORMot forum, which is not sleeping like other marmots these days... but it's very nice seeing such a question in SO!

First of all, some general notes:

You should better use UTF8ToString instead of UTF8ToWideString function;
If you create your objects instances, you'd better use nested try..finally blocks: e.g. if the TComment.CreateAndPrepare constructor fails and raise an exception, you will never reach the FreeAndNil(Task) code, so you'll leak memory;
Take care, using FreeAndNil() is very dangerous those days in the Delphi community - you may be anathemized!
The main FSQLModel should be made public, and live during all database time;
The TSQLRestClientDB 3d parameter (server model) should be nil;
A FormDestroy is needed to release the memory, but it is not the main point here;

About your code, in fact, as stated by the documentation, a TSQLRecordMany sub-class shall have at least two published properties, named Source and Dest, by convention:

by default, only two TSQLRecord (i.e. INTEGER) fields must be created, named "Source" and "Dest", the first pointing to the source record (the one with a TSQLRecordMany published property) and the second to the destination record ...

in all cases, at leat two 'Source' and 'Dest' published properties must be declared as TSQLRecord children in any TSQLRecordMany descendant because they will always be needed for the 'many to many' relationship

Then, it should work as expected:

  TTaskComments = class(TSQLRecordMany)
  private
    FSource: TTask;
    FDest: TComment;
  published
    property Source: TTask read FSource;
    property Dest: TComment read FDest;
  end;

And note that the FillMany() method only fills the Source and Dest as IDs, so you can't here directly get Source.TaskName or Dest.CommentText. You'll have to use instead e.g. the DestGetJoined method to retrieve the needed fields. See the documentation about that method, or read the TestMany procedure in TTestSQLite3Engine._TSQLRestClientDB method of the SQLite3.pas unit.

You may also take a look at the "Automatic JOIN query" new feature (in the 1.16 trunk): it will make your query just for you. See this article.