Left join, "not in"

BIZ TMS Aurelius
1

Hi, Wagner
Consider the Schema:
 
    TAccess = class
  private
    FId: Integer;
    FAccessDate: Nullable<TDateTime>;
    FPersonId: Proxy<TPerson>;
  public
    property Id: Integer read FId write FId;
    property AccessDate: Nullable<TDateTime> read FAccessDate write FAccessDate;
    property PersonId: TPerson read GetPersonId write SetPersonId;
  end;
 
  TPerson = class
  private
    FId: Integer;
    FName: Nullable<string>;
  public
    property Id: Integer read FId write FId;
    property Name: Nullable<string> read FName write FName;
  end;

How to get all "Person" not in "Access" bettween dates?
something like this in SQL:

SELECT P.ID, P.Name
FROM Person P
left join Access A
  on A.PerrsonId=P.Id
  and (A.AccessDate between '19.07.2016, 00:00:00.001' and '19.07.2016, 23:59:59.000')
where A.Id is null                       

Thanks, Wagner


2

That SQL you put will bring duplicated person objects, won't it?

If that's not a problem for you, you can simply start the Aurelius query by Find<TAccess> and then access the Person object for each TAccess.
Another option is to create a bidirecional association by adding a AccessList: TList<TAccess> property in TPerson (in lazy mode to keep performance) and then create an alias in the query:

Find<TPerson>.CreateAlias('AccessList', 'a')

3

Hi,
I was hoping you would suggest something like
(Pseudo-code):
Manager.Find<TPerson>
                          .Select ...
                          .Left Join ... between .... 
                          .Where(Id is null)
                          .List;
But I do not even know if it does makes sense.
i will uses IDBStatement
thanks, :)