Retrieve all TOrders with 0 TOrderItems

BIZ TMS Aurelius
1

Hi,


Once again a question :-)

Given the following definition:
//- ENTITY ORDERITEM
  [Entity]
  [Table('ORDERITEM')]
  [Sequence('SEQ_ORDERITEM_ID')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TOrderItem = class
  private
    [Column('ID',[TColumnProp. Unique, TColumnProp.Required ] ) ]
    FId:Integer;

    [Column('AMOUNT',[] ) ]
    FAmount:integer;
    procedure SetAmount(const Value: integer);
  public
    property Amount: integer read FAmount write SetAmount;
    ..
    ..
  end;

//- ENTITY ORDER
  [Entity]
  [Table('ORDER')]
  [Sequence('SEQ_ORDER_ID')]
  [Id('FId', TIdGenerator.IdentityOrSequence)]
  TOrder = class
  private
    [Column('ID',[TColumnProp. Unique, TColumnProp.Required ] ) ]
    FId:Integer;

    [ManyValuedAssociation([], CascadeTypeAll)]
    [ForeignJoinColumn('ORDER_ID', [TColumnProp.Required])]
    FOrderItems: TList<TOrderItem>;
  public
    ..
    ..
  end;

I would like to know how to select all orders with no order items.

TIA and Kind regards




2

We plan to add subqueries to the criteria API that will make this task more object-oriented. Currently, you should use the SQL expression (http://www.tmssoftware.com.br/aurelius/doc/web/index.html?sql_expression.htm). Something like this (not tested):


Result := FManager.Find<TOrder>
  .Where(TExpression.Sql('NOT EXISTS (Select * from ORDERITEM where ORDERITEM.ORDER_ID = {Id}')))
  .List;