#1 2015-07-23 20:49:39

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

Offline

#2 2015-07-24 06:16:24

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
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

Online

#3 2015-07-27 06:26:05

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.

Offline

#4 2015-07-27 07:48:35

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
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/

Online

#5 2015-07-27 08:36:21

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...

Offline

#6 2015-07-27 11:22:41

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: one to many how to

Rec.Owner := Rec.AsTSQLRecord;

Online

#7 2015-08-03 21:56:35

mariomoretti
Member
From: italy
Registered: 2012-01-29
Posts: 88

Re: one to many how to

indeed was very simple! tx

Offline

Board footer

Powered by FluxBB