mORMot Open Source

mrbar2000

Member

From: Brazil

Registered: 2016-10-26

Posts: 89

How to make where in column text json

Hello Community,

I'm facing a challenge with my API and would appreciate your help.

1 - In my project, I have the "TAmostra" table, where one of the columns is "Dados", of type "TDados", containing various fields.

  TDados = packed record
    NumeroSolicitacao: RawUTF8;
	DataSolcitacao: TDateTime;
    Exames: RawUTF8;
    Paciente: RawUTF8;
    Posto: RawUTF8;
    Atendente: RawUTF8;
    DataEtiqueta: TDateTime;
    Temperatura: Double;
    Motivo: RawUTF8;
    Rack: RawUTF8;
    Linha: RawUTF8;
    Coluna: Int64;
  end;

  TAmostra = class(TOrm)
  private
    FNumero: RawUTF8;
    FDados: TDados;
  published
    property Numero: RawUTF8 index 40 read FNumero write FNumero;
    property Dados: TDados read FDados write FDados;
  end;
  
  TEvento = class(TOrm)
  private
    FTipo: TTipoEvento;
    FAmostra: TAmostra;
  published
    property Id: 
    property Tipo: TTipoEvento read FTipo write FTipo;
    property Amostra: TAmostra read FAmostra write FAmostra;
  end;  

How can I create a SELECT query to Evento join with Amostra by Dados.DataSolcitacao? SomethingLike:

SELECT A.*
FROM EVENTO A
LEFT OUTER JOIN AMOSTRA B ON (B.ID = A.ID)
WHERE B.AMOSTRA.DADOS.DATASOLICITACAO BETWEEN '01.12.2023' AND '01.01.2024'

if this not is possible, i can transform TDados in TOrm too, add TDados into TAmostra. then how make this retrieve on ORM?

SELECT A.*
FROM EVENTO A
INNER JOIN AMOSTRA B ON (B.ID = A.ID)
INNER JOIN DADOS C ON (C.ID = B.IDSOLCITACAO)
WHERE C.DATASOLICITACAO BETWEEN '01.12.2023' AND '01.01.2024'

I appreciate any guidance or suggestions you can offer.

Best regards,

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: How to make where in column text json

I appreciate any guidance or suggestions you can offer.

In SQLite version 3.38.0 or higher, it can be queried using the following SQL syntax as described in this article (near the end).

With best regards
Thomas