mORMot Open Source

erick

Member

Registered: 2015-09-09

Posts: 155

confusion about joined records

Sorry, some basic questions here.

type
  // -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  TSQLparents = class(TSQLRecord)
  private
      fname: RawUTF8;
  published
      property pnt_name: RawUTF8 read fname write fname;
  end;

  // -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
  TSQLkids = class(TSQLRecord)
  private
      fname: RawUTF8;
      fbirthdate: TDateTime;
      fparent1: TRecordReference;

  published
      property kid_name: RawUTF8 read fname write fname;
      property kid_birthdate: TDateTime read fbirthdate write fbirthdate;
      property kid_parent1: TRecordReference read fparent1 write fparent1;
  end;
...
The database looks fine, with two tables.

I can read it fine with:

// -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
procedure ShowChildren(Database: TSQLRest);
var
    kid: TSQLkids;
    parent: TSQLparents;
    parentname: string;
    wildcard: RawUTF8;

begin

    writeln('ShowChildren');
    writeln('============');

    wildcard := StringToUTF8('%');

    kid := TSQLkids.Create(Database, 'kid_name LIKE ? ', [wildcard]);
    parent := TSQLparents.Create;
    try
        if kid.id > 0 then
        begin

            // load the parent record... always check if it exists
            if Database.Retrieve(kid.kid_parent1, parent, false) then
                parentname := parent.pnt_name
            else
                parentname := 'N/A';

            writeln('  ID=', kid.id, ' Name= ', kid.kid_name, ' Parent1= ', parentname);

        end;
    finally
        kid.Free;
        parent.Free;
    end;
end;

But I'm forced to do the extra retrieve( kid, kid_parent1, parent, false )  is there a way to inherently
get that information

BTW, I've got a large legacy database to support, I cannot change the structure.

According to the mORMot web pages I can specify joins like:

aProd := TSQLProduct.CreateAndFillPrepareMany(Database,
   'Owner=? and Categories.Dest.Name=? and (Sizes.Dest.Name=? or Sizes.Dest.Name=?)',[],
   ['mark','for boy','small','medium']);
if aProd<>nil then
try
   while aProd.FillOne do
     //  here e.g. aProd.Categories.Dest are instantied (and Categories.Source=aProd)
     writeln(aProd.Name,' ',aProd.Owner,' ',aProd.Categories.Dest.Name,' ',aProd.Sizes.Dest.Name);
   //  you may also use aProd.FillTable to fill a grid, e.g.
   //  (do not forget to set aProd.FillTable.OwnerMustFree := false)
finally
   aProd.Free; //  will also free aProd.Categories/Sizes instances
end;

but what is aProd . Categories . Dest . Name
there is no definition which shows what Categories and Dest are defined as.  I'm assuming

TSQLDest = class( TSQLrecord )
  Name : RawUTF8
end

TSQLCategories = class( TSQLRecord )
  Dest : TSQLdest ;
end

TSQLProduct = class( TSQLRecord )
  Categories : TSQLCategories;
end;

but doesn't that mean it all gets stored in one big TSQLproduct table, not across multiple tables.

Any clues would help.

Erick

erick

Member

Registered: 2015-09-09

Posts: 155

Re: confusion about joined records

Okay, I think I've figured it out.  Trying the above and dumping the database confirmed that the entries were in separate tables like I wanted.