mORMot Open Source

milesyou

Member

Registered: 2015-03-04

Posts: 21

ABout 'One to many'

TSQLMyFileInfo = class(TSQLRecord)
  private
    FMyFileDate: TDateTime;
    FMyFileSize: Int64;
  published
    property MyFileDate: TDateTime read FMyFileDate write FMyFileDate;
    property MyFileSize: Int64 read FMyFileSize write FMyFileSize;
  end;
  TSQLMyFile = class(TSQLRecord)
  private
    FSecondOne: TSQLMyFileInfo;
    FFirstOne: TSQLMyFileInfo;
    FMyFileName: RawUTF8;
  published
    property MyFileName: RawUTF8 read FMyFileName write FMyFileName;
    property FirstOne: TSQLMyFileInfo read FFirstOne write FFirstOne;
    property SecondOne: TSQLMyFileInfo read FSecondOne write FSecondOne;
  end;

I use your  code of document, I Find query error, no show results

    RJoin := TSQLMyFile .CreateAndFillPrepareJoined(Database, '', [], []);
    try
    while RJoin.FillOne do
    begin
   
    end;
    RJoin.FillClose;
    finally
    RJoin.Free;
    end;

I discovered after analysis:
in  left Join   
  At the same time, the emergence of a table 'MyFileInfo'

I Hope you Fix it

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: ABout 'One to many'

This is my modified

in  RegisterTableForRecordReference funtion:

 if Props.Props.JoinedFieldsTable<>nil then begin
    W := TTextWriter.CreateOwnedStream;
    try
      W.AddShort('SELECT ');
      for j := 0 to high(Props.Props.JoinedFieldsTable) do
      with Props.Props.JoinedFieldsTable[j].RecordProps do begin
      if j>0 then
         W.Add('%.RowID as `%.RowID`,',[Props.Props.JoinedFields[J-1].Name,Props.Props.JoinedFields[J-1].Name])
      else
        W.Add('%.RowID as `%.RowID`,',[SQLTableName,SQLTableName]);

        for f := 0 to High(SimpleFields) do
          if SimpleFields[f].SQLFieldType<>sftID then
          begin
            if j>0 then
             W.Add('%.% as `%.%`,',[Props.Props.JoinedFields[J-1].Name,SimpleFields[f].Name,
              Props.Props.JoinedFields[J-1].Name,SimpleFields[f].Name])
            else
            W.Add('%.% as `%.%`,',[SQLTableName,SimpleFields[f].Name,
              SQLTableName,SimpleFields[f].Name]);
          end;
      end;
      W.CancelLastComma;
      W.AddStrings([' FROM ',aTableName]);
      for f := 1 to high(Props.Props.JoinedFieldsTable) do
        with Props.Props.JoinedFieldsTable[f].RecordProps do
          W.Add(' LEFT JOIN % as % ON %.%=%.RowID',[
            SQLTableName,Props.Props.JoinedFields[f-1].Name,aTableName,Props.Props.JoinedFields[f-1].Name,Props.Props.JoinedFields[f-1].Name]);
      W.SetText(Props.SQL.SelectAllJoined);
    finally
      W.Free;
    end;
  end;

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: ABout 'One to many'

I Test Ok above code,pls check it

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: ABout 'One to many'

I tried to fix TSQLModelRecordPropertiesSQL.SelectAllJoined computed SQL statement, following your proposal.

Please try http://synopse.info/fossil/info/e73e854e99

Thanks for the patch!