How to define alias to TOrm use on RetrieveDocVariant

mrbar2000 · 2024-02-29 15:38:38

Question 1
var
vAmostras: Variant;
...
vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
'left outer join Solicitacao s on (s.RowId = Amostra.solicitacao)', [], 's.numero, s.paciente, Amostra.*');

>>> SELECT s.numero, s.paciente, amostra.* FROM Amostra left outer join solicitacao s on (s.Id = Amostra.solicitacao)

I whould like define a alias to orm amostra ===> a

vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
'left outer join Solicitacao s on (s.RowId = a.solicitacao)', [], 's.numero, s.paciente, a.*');

>>> mormot cannot execute this select because it don't know about alias A.

i try too:

vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
'a left outer join Solicitacao s on (s.RowId = a.solicitacao)', [], 's.numero, s.paciente, a.*');

but mormot generate this select

>>> SELECT s.numero, s.paciente, a.* FROM Amostra WHERE a left outer join solicitacao s on (s.Id = Amostra.solicitacao)

Question 2

There is some way to pass the raw sql to orm? something like below. Be by ORM or other method do RestORM or other way?

vAmostras := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra,
'SELECT s.numero, s.paciente, a.* FROM Amostra a '+
'left outer join solicitacao s on (s.Id = Amostra.solicitacao) '+
'WHERE s.numero = ?', [12345]);

Last edited by mrbar2000 (2024-02-29 15:44:24)

ab · 2024-03-01 07:43:47

You can execute your select as TOrmTable or JSON, then fill a TDocVariantData array (or a IDocList).

See IRestOrm.ExecuteList and IRestOrm.ExecuteJson.
For instance, IRestOrm.ExecuteList returns a TOrmTable and you can use TOrmTable.ToDocVariant() to retrieve the data.
And IRestOrm.ExecuteJson() returns the JSON in our optimized format, ready to fill via TDocVariantData.InitArrayFromResults().

mrbar2000 · 2024-03-04 19:44:29

Great, I'm testing.

Arnold I think I found a possible bug:

FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', FWhere, TDocVariantData(FWhereParamValues).ToArrayOfConst,
'Amostra.*') <<<<< this doesn't work. I lost a time debugging

FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '', FWhere, TDocVariantData(FWhereParamValues).ToArrayOfConst,
'*') <<<<< this work

Last edited by mrbar2000 (2024-03-04 20:20:12)

mrbar2000 · 2024-03-04 19:49:28

Other thing I'm doing a POST passing filter on body

{{URL}}/amostra/listar
    .Post('/amostra/listar', 'amostraService/listaramostras')

  TFiltro = packed record
    Amostra: RawUTF8;
    EventoTipo: Integer;
    Solicitacao: RawUTF8;
    Paciente: RawUTF8;
    Posto: RawUTF8;
    PeriodoTipo: Integer;
    PeriodoInicial: TDateTime;
    PeriodoFinal: TDateTime;
  end;

function TAmostraService.ListarAmostras(const pFiltro: TFiltro): TServiceCustomAnswer;

Why I have to pass:

[{ "Amostra": "1234567891"}]

instead of

{ "Amostra": "1234567891" }

???

Last edited by mrbar2000 (2024-03-04 19:58:17)

ab · 2024-03-04 20:48:35

I don't understand what you mean by 'I have to pass'.
From where? to what?

If this is from the client, this is as requested by the default mORMot JSON routing/encoding.
See the mORMOt 1 documentation about this. It is accurate enough. https://synopse.info/files/html/Synopse … #TITLE_456
The input parameters are parsed as an array of values, or a JSON object with a {"pFiltro":{...}} value.

mORMot Open Source

#1 2024-02-29 15:38:38

How to define alias to TOrm use on RetrieveDocVariant

#2 2024-03-01 07:43:47

Re: How to define alias to TOrm use on RetrieveDocVariant

#3 2024-03-04 19:44:29

Re: How to define alias to TOrm use on RetrieveDocVariant

#4 2024-03-04 19:49:28

Re: How to define alias to TOrm use on RetrieveDocVariant

#5 2024-03-04 20:48:35

Re: How to define alias to TOrm use on RetrieveDocVariant

Board footer