mORMot Open Source

cutec-chris

Member

Registered: 2020-01-09

Posts: 6

Retriving (and storing) JSON from One-to-Many relation

Hello,

iam starting to learn mORMot at time
And ill try to move my old Business Logic to mormot at time.
Ive managed to connect to one of my existing Database, and retriving Tables via TSQLRestServerDB/TSQLHttpServer.
Now ill try to retrive an nested record from an standard master/detail relation in the database.

  TOption = class(TBaseDBDataSet)
  private
    FOption,FValue : RawUTF8;
    fREF_ID : TRecordReference;
  public
    class procedure DefineFields(aDataSet : TDataSet);override;
  published
    property REF_ID: TRecordReference read fREF_ID write fREF_ID;
    property OPTION : RawUTF8 index 60 read FOption;
    property VALUE : RawUTF8 write FValue;
  end;
  TUser = class;
  TOptions = class(TSQLRecordMany)
  private
    FOption: TOption;
    FUser: TUser;
  public
    function GetOption(aSection, aIdent, DefaultValue: string): string;
    procedure SetOption(aSection,aIdent, Value : string);
  published
    property Source : TUser read FUser;
    property Dest : TOption read FOption;
  end;
  TUser = class(TBaseDBDataset)
  private

But ill get always only the first level of the relation.
Is this not implemented ? Or do i have to set an Option to TSQLRestServerDB ?
When its not implemented, whers the best way to start ?
Do i have to retrive the Data manually ? (What function can i override to do so ?)

best regards
Christian

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Retriving (and storing) JSON from One-to-Many relation

Nothing special to do.

Just check what is written in the documentation about this at https://synopse.info/files/html/Synopse … ml#TITL_58

cutec-chris

Member

Registered: 2020-01-09

Posts: 6

Re: Retriving (and storing) JSON from One-to-Many relation

Ive read this stuff 4-5 times, maybe my english isn't not good enough but i don't find answers there to the above questions.

Does TSQLRestServerDB has the ability to return nested Json for one-to-many mapping ? It just dont shows the options field to me in an request.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Retriving (and storing) JSON from One-to-Many relation

Yes, use PrepareFillMany - see https://synopse.info/files/html/Synopse … #TITLE_106

We need some more code to see what you did.
But please follow the forum rules (https://synopse.info/forum/misc.php?action=rules), and post huge piece of code not directly in the forum, but in a separated site like https://gist.github.com/

cutec-chris

Member

Registered: 2020-01-09

Posts: 6

Re: Retriving (and storing) JSON from One-to-Many relation

I at time use only the predefined functions and the classes provided by mORMot

  SQLite3 := TSQLite3LibraryDynamic.Create;
  FDB := TSQLRestServerDB.Create(Model, ':memory:');
  FDB.CreateMissingTables(0,[itoNoAutoCreateGroups, itoNoAutoCreateUsers,itoNoCreateMissingField]);
  HttpServer := TSQLHttpServer.Create('8085', [FDB],'+', useBidirSocket);

The model looks like in the first post (i hope this amount of code is ok)
When i retrive now http://localhost:8085/promet/user/1000262/
Ill get only the record of the TUser dataset but i expect to get an nested JSON with TUser and TOptions nested.

cutec-chris

Member

Registered: 2020-01-09

Posts: 6

Re: Retriving (and storing) JSON from One-to-Many relation

The Question is how can i tell TSQLHttpServer/TSQLRestServerDB that it please should use PrepareFillMany internally to build the JSON.
And is the JSON Generator able to build an nested JSON and also is it able to react to an PUT statement of teh nested Option part correct.

Or do i have to use an own HTTPServer Implementation that manually calls PrepareFillMany and builds the JSON on its own and vice versa.

Last edited by cutec-chris (2020-01-10 11:24:45)

cutec-chris

Member

Registered: 2020-01-09

Posts: 6

Re: Retriving (and storing) JSON from One-to-Many relation

When i see it correct, i cant do what i want.
I dont see Functions to encode JSON from an one-to many Relation.
So i have to use an own HTTPServer Implementation that manually calls PrepareFillMany and builds the JSON on its own and vice versa.

Right ?

Alternatively i have to get the detail data from Client side for every table with an separate call. Am i right ?

ehkhalid

Member

Registered: 2018-05-16

Posts: 12

Re: Retriving (and storing) JSON from One-to-Many relation

if you use predefined routes in mormot, it's normal that calling http://localhost:8085/promet/user/1000262/ will return only the data of the table user, you have to write your own code and use PrepareFillMany to retrieve the full data, see https://synopse.info/files/html/Synopse … ml#TITL_49, or better use interface based service

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Retriving (and storing) JSON from One-to-Many relation

Don't expect much from the built-in REST/ORM routes. It was meant for basic CRUD operations, especially in debug mode, and shouldn't be used in production (from my POV).

As ehkhalid wrote, switch to a n-Tier model, and write an interface-based service for instance, returning RawJSON from whatever request you expect for a given busines logic.