mORMot Open Source

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 90

TOrm with ChildObjects

I Have 2 tables in my database

Person (id, name)
Phone (id, idPerson, number)

1) How can i map this in mormot?

  TPerson = class;

  TPhone = class(TOrm);
  private
    FIdPerson: TPerson;
    FNumber: RawUTF8;
  published
    property Number: RawUTF8 index 25 read FNumber write FNumber;
    property IdPerson: TPerson read FIdPersonwrite FIdPerson;
   

  TPerson = class(TOrm)
  private
    FNome: RawUTF8;
  published
    property Nome: RawUTF8 index 100 read FNome write FNome;
    property Phones: ???????? read FPhones   <<<<  that property type can i use here? (need persist in 2 separated tables)
  end;

2) How I can call retrieve on orm and bring some person (id=10 for sample) and all your Phones?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: TOrm with ChildObjects

Look in the mORMot 1 doc about this part of the ORM.
In mORMot 2, TSQLRecordMany is just called TOrmMany.

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 90

Re: TOrm with ChildObjects

I see all documentation. But TOrmMany is to pivottable, and i dont have source and dest in this case.
I what just a property returning list of objects TPhone into person!
Or I dont understand very well how to use!

Remerber that Tperson 1 - (0..n) Tphone (on database are different tables)

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: TOrm with ChildObjects

I don't really understand your description. The problem is that there are several ways to solve it. If it is necessary for you to have the phone numbers in a separate table, you can use one of the following options (there are even more possibilities):

type
  TOrmPersonID = type TID;

  TOrmPhone = class(TOrm)
  private
    FPersonID: TOrmPersonID;
  published
    property PersonID: TOrmPersonID
      read FPersonID write FPersonID;

  TOrmPerson = class(TOrm)
  public
    property Phones: TOrmPhoneObjArray

procedure TOrmPerson.GetAllPhones(out pmoList: TOrmPhoneObjArray);
const
  _SQL_WHERE = '(PersonID=?)';
begin
  RestServer.Server.RetrieveListObjArray(pmoList, TOrmPhone, _SQL_WHERE, [IDValue]);
end;

Or another option:

type
  TOrmPerson = class(TOrm)
  private
    FPhones: TIDDynArray;
  public
    function AddPhone(const pmPhoneID: TID): Integer;
    procedure DeletePhone(const pmPhoneID: TID);
  published
    property Phones: TIDDynArray
      read FPhones;

function TOrmPerson.AddPhone(const pmPhoneID: TID): Integer;
begin
  Result := AddInt64Once(TInt64DynArray(FPhones), pmPhoneID);
end;

procedure TOrmPerson.DeletePhone(const pmPhoneID: TID);
var
  idx: Integer;
begin
  idx := Int64ScanIndex(Pointer(FPhones), Length(FPhones), pmPhoneID);
  if idx >= 0 then
    DeleteInt64(TInt64DynArray(FPhones), idx);
end;

It is easier if you save the phone numbers in the people table as a list (TObjectList<TPhone>), without creating a Phone table. If you have been working with databases for a while, you have to get used to this technique.

With best regards
Thomas

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 90

Re: TOrm with ChildObjects

Thanks. Phone is just a sample.

I will use this strategy for more complex classes. Order and OrderItem for sample.
I dont think that OrderItem shoud be saved into a blob into order table.

Reasons:
1) Will be dificult mount reports. because by default FastReport and others reports generators work with Datasets, then we need get the records master detail in tables.
2) A bussiness inteligence tool that work with database need get OrderItems to make the graphics. Imagine this stored in blobs.
3) How to find something into objects that are store in this field blob?
    Sample1: I want bring all orders that have product "xyz" (where product is into a blob field)
    Sample2: I want sum(orderitem.total) where product "xyz" in a period (where product is into a blob field)
4) I think that exist other reason that do not appear in my mind at the moment.

How u treat this? Store all objects as documents (nosql style)?

Last edited by mrbar2000 (2023-03-07 12:34:00)

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 90

Re: TOrm with ChildObjects

In this sample TBO. What type of TOrmPhoneObjArray? and How I can get a person id=? and the orm bring the person with all phones?

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: TOrm with ChildObjects

1) Will be dificult mount reports. because by default FastReport and others reports generators work with Datasets, then we need get the records master detail in tables.

FastReport can work with many sources, DataSets are just one of them. Read this article. This type of connection is much more flexible.

3) How to find something into objects that are store in this field blob?

It does not have to be stored in a blob field. It can also be stored as JSON. SQLite, for example, can execute queries directly on these fields. Read this article for more information.

How u treat this? Store all objects as documents (nosql style)?

We don't know your requirements. mORMot can be operated in many ways and is not limited to one. The help is extensive, many questions have already been answered in the forum and there are articles to be found on the internet. The best is to create a small example for each possibility and see if it fits for your own application.

With best regards
Thomas

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: TOrm with ChildObjects

What type of TOrmPhoneObjArray? and How I can get a person id=? and the orm bring the person with all phones?

type TOrmPhoneObjArray = array of TOrmPhone; Class TObjectWithID has the property IDValue. Just try my source code.

With best regards
Thomas

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 90

Re: TOrm with ChildObjects

Hi guys I try. look this sample:

  TPainel = class;

  TPaineis = array of TPainel;

  TProjeto = class(TOrm)
  private
    FPaineis: TPaineis;
  published
    property Paineis: TPaineis read FPaineis;
  end;

  TPainel = class(TOrm)
  private
    FIdProjeto: TProjeto;
  published
    property IdProjeto: TProjeto read FIdProjeto write FIdProjeto;
  end;

when I use this code

function TProjetoService.Busca(pId: Int64): TServiceCustomAnswer;
var
  vProjeto: TProjeto;
  vAuto: IAutoFree;
begin
  vAuto := TOrm.AutoFree([@vProjeto, TProjeto]);
  if not GlobalRepository.Orm.Retrieve(pId, vProjeto) then
    Result := ReturnRecordNotFound
  else
    Result.Content := ObjectToJson(vProjeto);
end;

I receive this:

{
    "RowID": 1,
    "Nome": "Projeto a",
    "DataCriacao": 135770589581,
    "UltimaAlteracao": 135770590233,
    "IdCriador": 5,
    "Paineis": "[{\"RowID\":1,\"Nome\":\"painel a\",\"DataCriacao\":0,\"UltimaAlteracao\":0,\"IdCriador\":5,\"IdProjeto\":1},{\"RowID\":2,\"Nome\":\"painel b\",\"DataCriacao\":0,\"UltimaAlteracao\":0,\"IdCriador\":5,\"IdProjeto\":1},{\"RowID\":3,\"Nome\":\"painel c\",\"DataCriacao\":0,\"UltimaAlteracao\":0,\"IdCriador\":5,\"IdProjeto\":1}]"
}

I whould like this:

{
    "Id": 1,
    "Nome": "Projeto a",
    "IdCriador": 5
    "Paineis": [
        {
	    "Id": 1,
	    "Nome": "painel a",
	    "IdCriador": 5,
	    "IdProjeto":1
	 },
	{
	    "Id": 2,
	    "Nome": "painel b",
	    "IdCriador": 5,
	    "IdProjeto":1
	 },
	{
	    "Id": 3,
	    "Nome": "painel c",
	    "IdCriador": 5,
	    "IdProjeto":1
	}
    ]
}

1) How orm method i should be use to RETRIEVE the object TProjeto with the array Paineis filled? There are some?

2) This model create 2 tables Painel and Projeto but fill just Projeto and create a string field into Projeto table to store all TPanel as json. How I can store projeto and painel in diferent tables, but yet so make the same RETRIEVE above?

Last edited by mrbar2000 (2023-03-08 13:12:07)

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: TOrm with ChildObjects

2) This model create 2 tables Painel and Projeto but fill just Projeto and create a string field into Projeto table to store all TPanel as json. How I can store projeto and painel in diferent tables, but yet so make the same RETRIEVE above?

Look closely at my source code from Post #4 and extend your service function with the one shown there for loading TPaineis here. You will get the desired result only with a DTO. A direct generation from the ORM is not possible with your specifications.

With best regards
Thomas

Last edited by tbo (2023-03-08 17:00:24)