complex where and order by with calculation

I need a list of Projekt_EINTRAG and have no clue how to build up the statement in aurelius.

PROJEKT_EINTRAG is a detail list from PROJEKT and works together with PROJEKT.

My problem is, I need to calculat fields as an Date expression in the where clause and in the order by clause to get the correct list.

 In Firebird I need follow SQL to get this list:


select pe.ID, pe.PROJEKT_ID, pe.TAGEVORLAUF,
pe.TAGEZEITRAUM, pe.DAUER, pe.BETREFF, pe.ORT, pe.INHALT, pe.KOMMENTAR, pe.COLOR,
pe.ERLEDIGT
from PROJEKT_EINTRAG pe, PROJEKT p
where p.ID = pe.PROJEKT_ID
and (p.datum - pe.TAGEZEITRAUM - pe.TAGEVORLAUF)  <= 
:Datum 
order by p.datum - pe.TAGEZEITRAUM - pe.TAGEVORLAUF


In my class I calculat this date in the property "Datum":

  [Entity]
  [Table('PROJEKT')]
  [Sequence('GEN_PROJEKT_ID')]
  [Id('FID', TIdGenerator.IdentityOrSequence)]
  TPROJEKT = class
  private
    [Column('ID', [TColumnProp.Required])]
    FID: Integer;

    [Column('BEZEICHNUNG', [], 50)]
    FBEZEICHNUNG: Nullable<string>;

    [Column('KOMMENTAR', [TColumnProp.Lazy], 80)]
    [DBTypeMemo]
    FKOMMENTAR: TBlob;

    [Column('DATUM', [])]
    FDATUM: Nullable<TDateTime>;

    [Column('COLOR', [])]
    FCOLOR: Nullable<Integer>;

    [ManyValuedAssociation([TAssociationProp.Required], CascadeTypeAll)]
    [ForeignJoinColumn('PROJEKT_ID', [TColumnProp.Required])]
    [OrderBy('TAGEZEITRAUM')]
    FEintragListe: Proxy<TList<TPROJEKT_EINTRAG>>;
    function GetEintragListe: TList<TPROJEKT_EINTRAG>;

  public
    property ID: Integer read FID write FID;
    property BEZEICHNUNG: Nullable<string> read FBEZEICHNUNG write FBEZEICHNUNG;
    property KOMMENTAR: TBlob read FKOMMENTAR write FKOMMENTAR;
    property DATUM: Nullable<TDateTime> read FDATUM write FDATUM;
    property COLOR: Nullable<Integer> read FCOLOR write FCOLOR;
    property EintragListe: TList<TPROJEKT_EINTRAG> read GetEintragListe;
  end;
  [Entity]
  [Table('PROJEKT_EINTRAG')]
  [Sequence('GEN_PROJEKT_EINTRAG_ID')]
  [Id('FID', TIdGenerator.IdentityOrSequence)]
  TPROJEKT_EINTRAG = class
  private
    [Column('ID', [TColumnProp.Required])]
    FID: Integer;

    [Column('TAGEVORLAUF', [])]
    FTAGEVORLAUF: Nullable<Integer>;

    [Column('TAGEZEITRAUM', [])]
    FTAGEZEITRAUM: Nullable<Integer>;

    [Column('DAUER', [])]
    FDAUER: Nullable<Double>;

    [Column('BETREFF', [], 255)]
    FBETREFF: Nullable<string>;

    [Column('ORT', [], 255)]
    FORT: Nullable<string>;

    [Column('INHALT', [TColumnProp.Lazy], 80)]
    [DBTypeMemo]
    FINHALT: TBlob;

    [Column('KOMMENTAR', [TColumnProp.Lazy], 80)]
    [DBTypeMemo]
    FKOMMENTAR: TBlob;

    [Column('COLOR', [])]
    FCOLOR: Nullable<Integer>;

    [Column('ERLEDIGT', [])]
    FERLEDIGT: Nullable<boolean>;

    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('PROJEKT_ID', [TColumnProp.Required], 'ID')]
    FPROJEKT_ID: Proxy<TPROJEKT>;
    function GetPROJEKT_ID: TPROJEKT;
    procedure SetPROJEKT_ID(const Value: TPROJEKT);
    function GetWocheTagVorlauf: Integer;
    function GetWocheTagZeitraum: Integer;
    function GetWocheVorlauf: Integer;
    function GetWocheZeitraum: Integer;
    procedure SetWocheTagVorlauf(const Value: Integer);
    procedure SetWocheTagZeitraum(const Value: Integer);
    procedure SetWocheVorlauf(const Value: Integer);
    procedure SetWocheZeitraum(const Value: Integer);
    function GetTAGEVORLAUF: Integer;
    function GetTAGEZEITRAUM: Integer;
    procedure SetTAGEVORLAUF(const Value: Integer);
    procedure SetTAGEZEITRAUM(const Value: Integer);
    function GetDAUERAsText: String;
    function CorrectWochenTag(iTage : Integer) : Integer;
    function GetCelltext: String;
    function GetDatum: TDateTime;
    procedure SetGetDatum(const Value: TDateTime);
    function GetDatumVorlauf: TDateTime;
    function GetWarningState: TWarningState;
  public
    procedure InitFromBASISEINTRAG(oPROJEKTBASIS_EINTRAG : TPROJEKTBASIS_EINTRAG);
    property ID: Integer read FID write FID;
    property TAGEVORLAUF: Integer read GetTAGEVORLAUF write SetTAGEVORLAUF;
    property TAGEZEITRAUM: Integer read GetTAGEZEITRAUM write SetTAGEZEITRAUM;
    property DAUER: Nullable<Double> read FDAUER write FDAUER;
    property DAUERAsText: String read GetDAUERAsText;
    property BETREFF: Nullable<string> read FBETREFF write FBETREFF;
    property ORT: Nullable<string> read FORT write FORT;
    property INHALT: TBlob read FINHALT write FINHALT;
    property KOMMENTAR: TBlob read FKOMMENTAR write FKOMMENTAR;
    property COLOR: Nullable<Integer> read FCOLOR write FCOLOR;
    property PROJEKT_ID: TPROJEKT read GetPROJEKT_ID write SetPROJEKT_ID;
    property ERLEDIGT: Nullable<boolean> read FERLEDIGT write FERLEDIGT;
    property CellText: String read GetCelltext;
    property WocheTagVorlauf: Integer read GetWocheTagVorlauf write SetWocheTagVorlauf;
    property WocheTagZeitraum: Integer read GetWocheTagZeitraum write SetWocheTagZeitraum;
    property WocheVorlauf: Integer read GetWocheVorlauf write SetWocheVorlauf;
    property WocheZeitraum: Integer read GetWocheZeitraum write SetWocheZeitraum;
    property Datum: TDateTime read GetDatum write SetGetDatum;
    property DatumVorlauf: TDateTime read GetDatumVorlauf;
    property WarningState : TWarningState read GetWarningState;
  end;

Thanks in advance

Martin Diehl

Hello Martin,

First you need to fix your association, I believe this is correct:



    [ManyValuedAssociation([TAssociationProp.Required], CascadeTypeAll, 'FPROJEKT_ID')]
    [OrderBy('TAGEZEITRAUM')]
    FEintragListe: Proxy<TList<TPROJEKT_EINTRAG>>;


For your query, you could do something like this:


List := Manager.Find<TPROJEKT_EINTRAG>
  .CreateAlias('PROJEKT_ID', 'p')
  .Where(<expression using Linq['p.Datum'], Linq['TAGEZEITRAUM'], Linq['TAGEVORLAUF']>)
  .OrderBy(<same expression again>)


Now, what you use in expression I'm not sure, as you are subtracting dates and integers?
What does that mean? Integer represent days? In this case you could use a specific SQL statement:


Where(Linq.Sql<TDateTime>('DateAdd((-{TAGEVORLAUF} - {TAGEZEITRAUM}) DAY TO {p.Datum})'))

Hello Wagner

Thanks for the hints.
My main error was, I used in CreateAlias PROJEKT instead of PROJEKT_ID.
Your solution does not work for me, but i got the idea.

This code works now for me


  Liste := Manager.Find<TPROJEKT_EINTRAG>
    .CreateAlias('PROJEKT_ID', 'p')
    .Where(Linq.Sql<TDateTime>('({p.datum} - {TAGEZEITRAUM} - {TAGEVORLAUF}) < ?', SearchDate ))
    .OrderBy(Linq['p.Datum']-Linq['TAGEZEITRAUM']-Linq['TAGEVORLAUF'])
    .List;




Once again, thanks Wagner.
Your answer helps me a lot.

Martin Diehl