mORMot Open Source

mariomoretti

Member

From: italy

Registered: 2012-01-29

Posts: 88

one to many how to

I've two tsqlrecord classes : the second having the first as property.

class1 = class(tsqlrecord)
protected
  fDescription : rawutf8;
published
  property Description : Rawutf8 read fDescription write fDescription;
end;

class2 = class(tsqlrecord)
protected
  fDescription : rawutf8;
  fOwner : class1;
published
  property Description : Rawutf8 read fDescription write fDescription;
  property Owner : class1 read fOwner write fOwner;
end;

class 1 contains some owners.
class 2 contains some children for every owner.

Now i know a class1 instance (Pippo) and i want to display in a grid all class2 children whose Owner is Pippo.

If I code : (aRest is a rest client .., aTable a SQLTable ecc. .)

          aWhere := 'Owner=' + inttostr(Pippo.ID);
          aTable := aRest.List([Class2],
                                       'Description',
                                       aWhere);

aTable (as I expected ) , is empty.

I've change filter in these ways :

            aWhere := 'Owner=' + inttostr(Pippo.recordreference(aRest.model));

aTable is empty.

            aWhere := 'OwnerID=' + inttostr(Pippo.ID); 

catch an sqlite exception. So do :

            aWhere := 'Owner.ID=' + inttostr(Pippo.ID); 

Please , may you help me to do this SIMPLE query ?

Tx very much.

Mario

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: one to many how to

Use parametrized query, with ?, and without using IntToStr (which returns a string, while a RawUTF8 is expected, BTW).

See http://synopse.info/files/html/Synopse% … l#TITLE_61

mariomoretti

Member

From: italy

Registered: 2012-01-29

Posts: 88

Re: one to many how to

Hi AB,

sorry but i can't retrieve the data.

After a long period  i'm yet a beginner, so,  i've used a sample project : Sample 02 -Embedded Sqlite3 ORM with these changes :

in Sampledata :

  TSQLSampleRecord = class(TSQLRecord)
  private
    fQuestion: RawUTF8;
    fName: RawUTF8;
    fTime: TModTime;

            fOwner : tSQLSampleRecord;   

  published
    property Time: TModTime read fTime write fTime;
    property Name: RawUTF8 read fName write fName;
    property Question: RawUTF8 read fQuestion write fQuestion;

    	property Owner : tSQLSampleRecord read fOwner write fOwner;

  end;

In Unit1 :

procedure TForm1.AddButtonClick(Sender: TObject);
var Rec [ins], Rec1[/ins]: TSQLSampleRecord;
[ins]    Ok : Boolean;[/ins]
begin
  Rec := TSQLSampleRecord.Create;
  try
    // we use explicit StringToUTF8() for conversion below
    // a real application should use TLanguageFile.StringToUTF8() in mORMoti18n
    Rec.Name := StringToUTF8(NameEdit.Text);
    Rec.Question := StringToUTF8(QuestionMemo.Text);
    if Database.Add(Rec,true)=0 then
      ShowMessage('Error adding the data') else begin


             Rec.Owner := Rec;
             Database.Update(Rec, 'Owner');
             Rec.Free;
             Rec := tSQLSampleRecord.Create(Database, 1);
             Rec1 := tSQLSampleRecord.CreateAndFillPrepare(Database, 'Owner=?',[Rec]);
             Ok := Rec1.Fillone;


      NameEdit.Text := '';
      QuestionMemo.Text := '';
      NameEdit.SetFocus;
    end;
  finally
    Rec.Free;
  end;
end;

Well : this are fields values after  Database.Update :

ID = 1 , Owner = 33247792 : I think  this is normal owing to refrerences and so on…..but , deleting database and reexecuting the code , Owner's value changes everytime, also inserting same values in name and question (???).

However : Fillone returns Ok = False.

The Sql generated by ORM is :

Select ID, Time, Name, Question, Owner from SampeRecord where Owner=:(''tSQLSampleRecord''):

So I've changed query with

Rec1 := tSQLSampleRecord.CreateAndFillPrepare(Database, 'Owner=?',[Rec.ID]);

and deleted database.

Now ID=1 and Owner = 35869232

Fillone returns OK = False.

The Sql generated by ORM is :

Select ID, Time, Name, Question, Owner from SampeRecord where Owner=:(''1''):

AB, please, don't say me to read you huge documentation or posts : at this point  we can assume that  I'm not able to understand  them !

My you correct the lines i've changed to let them  to work as expected ?

Tx very much.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: one to many how to

No, you are storing a pointer, not the ID, in the Owner property.

Please read http://synopse.info/files/html/Synopse% … ml#TITL_70
and https://tamingthemormot.wordpress.com/2 … tionships/

mariomoretti

Member

From: italy

Registered: 2012-01-29

Posts: 88

Re: one to many how to

AB,

i'm confused.

i've asked you to fix my sample and not to refer to documents i've already read many an many times...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: one to many how to

Rec.Owner := Rec.AsTSQLRecord;

mariomoretti

Member

From: italy

Registered: 2012-01-29

Posts: 88

Re: one to many how to

indeed was very simple! tx