mORMot Open Source

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