SQL to Aurelius

BIZ TMS Aurelius
1

Hi, Wagner
 Considers the following schema:

  [Entity]
  [Table('PerPay')]
  TPerPay = class
  private
    [Column('Id', [TColumnProp.Required])]
    FId: Int64;

    [Column('PPStateId', [TColumnProp.Required])]
    FPPStateId: Int64;

  public
    property Id: Int64 read FId write FId;
    property PPStateId: Int64 read FPPStateId write FPPStateId;
  end;

  [Entity]
  [Table('DebItmD')]
  TDebItmD = class
  private
    [Column('Id', [TColumnProp.Required])]
    FId: Int64;
    
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('PerPayId', [TColumnProp.Required], 'Id')]
    FPerPayId: Proxy<TPerPay>;

    [Association([TAssociationProp.Lazy], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('DebItemId', [], 'Id')]
    FDebItemId: Proxy<TDebItem>;

    [Column('DebItmDStatusId', [TColumnProp.Required])]
    FDebItmDStatusId: Integer;
   
  public
    property Id: Int64 read FId write FId;
    property PerPayId: TPerPay read GetPerPayId write SetPerPayId;
    property DebItemId: TDebItem read GetDebItemId write SetDebItemId;
    property DebItmDStatusId: Integer read FDebItmDStatusId write FDebItmDStatusId;
  end;

  [Entity]
  [Table('DebItem')]
  TDebItem = class
  private
    [Column('Id', [TColumnProp.Required])]
    FId: Int64;
  
    [Column('ItemStateId', [TColumnProp.Required])]
    FItemStateId: Int64;
   
  public
    property Id: Int64 read FId write FId;
    property ItemStateId: Int64 read FItemStateId write FItemStateId;
  end;

Now, considers the following SQL:

SELECT A.Id, A.PPStateId
FROM PerPay A
LEFT JOIN DebItmD L ON (L.PerPayId = A.Id)
LEFT JOIN DebItem M ON (M.Id = L.DebItemId)
WHERE A.PPStateId = 0
  AND ( L.DEBITMDSTATUSID = 0 OR M.ITEMSTATEID=2)
ORDER BY  A.Id

how can I do with aurelius?

Thanks

2

Manager.Find<TDebItmD>

  .CreateAlias('PerPayId', 'P')
  .CeateAlias('DebItemId', 'M')
  .Where((Linq['DebItmStatusId'] = 0) or (Linq['M.ItemStateId'] = 2)
  .List;

You will get a list of TDebItmD objects which have a property PerPayId with the corresponding TPerPay object.
3
Hi, Wagner
  I need a TList<TPerPay>, not TList<TDebItmD>
  thanks

4

because, DebItmD could exist or not

5

In this case create a bidirectional association, adding the other side of the association in TPerPay object:




  [ManyValuedAssociation([], CascadeTypeAllRemoveOrphan, 'FPerPayId')]
  property DebItmDList: TList<TDebItmD > read FDebItmDList write FDebItmDList;

as described here: http://www.tmssoftware.biz/business/aurelius/doc/web/lazy-loading_associations.html

If your relation is 1-1 (which it seems from your SQL) you are all good, if not you can remove duplicates: http://www.tmssoftware.biz/business/aurelius/doc/web/removing_duplicated_objects.html
6

Ok, thanks