#1 2017-03-01 14:03:25

ASiwon
Member
From: Poland
Registered: 2015-01-30
Posts: 82

Using SQL JOIN to limit retrieved data

Hello,

Is it possible in mORMot to limiting retrieved data with conditions based on SQL JOIN clause. For example I have following model:

  TDocument = class(TSQLRecord)
  strict private
    FDate: TDateTime;
  published
    property Date: TDateTime read FDate write FDate;
  end;

  TPosition = class(TSQLRecord)
  strict private
    FDocumentId: TRecordReference;
    FNumber: Integer;
  published
    property DocumentId: TRecordReference read FDocumentId write FDocumentId;
    property Number: Integer read FNumber write FNumber;
  end;

Now I would like to get positions lists for documents which have for example Date value is from specified range. I would like to achieve SQL query which looks like:

select position.* from Positions join Documents on Documents.Id = Positions.DocumentId and (where clause for Documents table) where (optional where clause for Positions table)

I don't want to get any data from Documents table. I just want to build condition based on Documents to limit data from Positions. I could of course to read identifiers from Documents and then generate long expression list and use it with 'in' operator to get Positions from requested Documents, but I think query which use join clause will be better optimized on SQL server.

best regards
Adam Siwon


best regards
Adam Siwon

Offline

Board footer

Powered by FluxBB