mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,682

Website

SQLite3: "Many to Many" relationship updates

The TSQLRecordMany implementation has been updated.
TSQLRecordMany now uses TSQLRecord properties, and not TRecordReference any more.
It will result in a more easy table definition, and perhaps a more "standard" table data (IDs are stored, not obscure TRecordReference values).
See http://synopse.info/forum/viewtopic.php?pid=870#p870

New TSQLRecordMany.FillManyFromDest() and TSQLRecordMany.DestGetJoined() methods have been added, to make real world apps easier.

Some dedicated TSQLRecord.FillPrepare() and TSQLRecord.CreateAndFillPrepare() methods have been created, which may be handy if the purpose of to loop through a set of records.

ComingNine

Member

Registered: 2010-07-29

Posts: 294

Re: SQLite3: "Many to Many" relationship updates

It seems that TSQLRecordMany.FillMany and TSQLRecordMany.DestGetJoined provide rather similar functionality: retrieving dest items related to certain source item. Could you help to comment the difference between them ? For example, what are the different situations where either FillMany or DestGetJoined fits better ? 

After playing around a bit, now the difference becomes more clear to me... Sorry for the trouble!

...ThirdPartyDemos\Migajek\synopse-sqlite-demo...

procedure TForm1.LoadTasksForCustomer(const ACustomer: TSQLCustomer; const AList: TStrings);
var
 tasks: TSQLTasks;
 MsgUTF8: RawUTF8;
 Msg: string;
 task: TSQLTask;
 fIds: TIDDynArray;
begin
  // Can be created directly
  tasks := TSQLTasks.Create;
  try
    // How to get Dest IDs
    // tasks.DestGet(globalClient, fIds); // Does not work
    tasks.DestGet(globalClient, ACustomer.ID, fIds); // Works
    // ACustomer.Tasks.DestGet(globalClient, ACustomer.ID, fIds); // Original
    task:= TSQLTask.CreateAndFillPrepare(globalClient, TInt64DynArray(fIds));
    AList.BeginUpdate();
    AList.Clear();
    try
      while task.FillOne do
        AList.AddObject(Format('%s', [UTF8ToString(task.Text)]), Pointer(task.id));
    finally
      AList.EndUpdate();
      FreeAndNil(task);
    end;

    // How to get Pivot instances, and Dest instances later
    tasks.FillMany(globalClient, ACustomer.ID);
    while tasks.FillOne do begin
      // MsgUTF8 := tasks.Dest.Text;
      task := TSQLTask.Create(globalClient, tasks.Dest.ID);
      try
        MsgUTF8 := task.Text;
        Msg := UTF8ToString(MsgUTF8);
        Msg := 'Get Dest through Pivot: ' + UTF8ToString(MsgUTF8);
        OutputDebugString(PChar(Msg));
      finally
        task.Free;
      end;
    end;

    // How to get Dest instances directly
    task := tasks.DestGetJoined(globalClient, '', ACustomer.ID) as TSQLTask;
    try
      while task.FillOne do begin
        MsgUTF8 := task.Text;
        Msg := 'Directly get Dest: ' + UTF8ToString(MsgUTF8);
        OutputDebugString(PChar(Msg));
      end;
    finally
      task.Free;
    end;

  finally
    tasks.Free;
  end;
end;

procedure TForm1.lbTasksClick(Sender: TObject);
var
  tasks: TSQLTasks;
  task: TSQLTask;
  cust: TSQLCustomer;
  clientsIds: TIDDynArray;
  i, j: integer;
  MsgUTF8: RawUTF8;
  Msg: string;
begin
  gbEditTask.Visible:= lbTasks.ItemIndex <> -1;
  if not gbEditTask.Visible then
    exit;

  gbEditTask.Visible:= lbTasks.Items.Objects[lbTasks.ItemIndex] <> nil;
  if not gbEditTask.Visible then
    exit;

  task:= LoadTask(integer(lbTasks.Items.Objects[lbTasks.ItemIndex]));
  cbTaskPriority.ItemIndex:= Ord(task.Priority);
  cbTaskPriority.Tag:= task.ID;

  FillCustomersList(CheckListBox1.Items, true);
  CheckListBox1.Tag:= task.ID;
  // load list of customers assigned to the given task
  cust:= TSQLCustomer.Create();
  try
    cust.Tasks.SourceGet(globalClient, task.ID, clientsIds);
    for i:= low(clientsIds) to high(clientsIds) do
      begin

        // find the client on the list (by ID)
        for j:= 0 to CheckListBox1.Count -1 do
          if Integer(CheckListBox1.Items.Objects[j]) = clientsIds[i] then
              CheckListBox1.Checked[j]:= true;
      end;
  finally
    cust.Free();
    FreeAndNil(task);
  end;

  // Learn about FillManyFromDest
  tasks := TSQLTasks.Create;
  try
    tasks.FillManyFromDest(globalClient, integer(lbTasks.Items.Objects[lbTasks.ItemIndex]));
    while tasks.FillOne do begin
      cust := TSQLCustomer.Create(globalClient, tasks.Source.ID);
      try
        MsgUTF8 := cust.FirstName;
        Msg := UTF8ToString(MsgUTF8);
        Msg := 'Get Source through Pivot: ' + UTF8ToString(MsgUTF8);
        OutputDebugString(PChar(Msg));
      finally
        cust.Free;
      end;
    end;
  finally
    tasks.Free;
  end;
end;

Last edited by ComingNine (2017-11-26 09:45:48)