Still having problem converting SQL to Aurelius

BIZ TMS Aurelius
1

I still have problems to convert a 'normal' SQL query to an Aurelius one.
For instance, if I have this query:

SELECT orderid, MAX(tijdstip) FROM orderflow
WHERE NOT(status IN ('T999','S999','T998','S998'))
GROUP BY orderid
ORDER BY orderid



how would I convert that to Aurelius?
I tried something like this, but it does not seem to work:

OM.Find<TOrderflow>.Select(TProjections.ProjectionList
                                             .Add(TProjections.Max('tijdstip'))
                                             .Add(TProjections.Group('orderid'))
                                             .AddOrder(TOrder.Asc(TProjections.Prop('orderid')))
                                              ).List;

2

you must ListValues instead of List because you are using projections.

And the Where part you can use SQL expression:

.Add(not TLinq.Sql('{status} IN (''T999'', ''S999'', ''T998'', ''S998'')')
3

You are right, I must use ListValues. However, it still does not work.
I now have this:

        Results := OM.Find<TOrderflow>.Select(TProjections.ProjectionList
                                             .Add(TProjections.Prop('orderid'))
                                             .Add(TProjections.Max('tijdstip'))
                                             .Add(TProjections.Group('orderid'))
                                              )
                                             .Add(not TLinq.Sql('{status} IN (''T999'', ''S999'', ''T998'', ''S998'')'))
                                             .ListValues;


I know it is not correct but could you please tell me which order is correct?

4

I also keep getting another error: UnexpectedFieldType TOrders. That is because orderid is a Foreignkey to the table TOrders. I guess I will have to make an even more complex call then?
The TOrderflow class is like this in Aurelius, how can I perform a query like above on the orderflow table?

  [Entity]
  [Table('orderflow')]
  [Description('')]
  [Sequence('orderflow_seq')]
  [Id('Forderflowid', TIdGenerator.IdentityOrSequence)]
  Torderflow = class
  private
    [Column('orderflowid', [TColumnProp.Required])]
    [Description('')]
    Forderflowid: Integer;
    
    [Column('tijdstip', [])]
    [Description('')]
    Ftijdstip: Nullable<TDateTime>;
    
    [Column('status', [], 7)]
    [Description('')]
    Fstatus: Nullable<string>;
    
    [Association([], [])]
    [JoinColumn('orderid', [], 'orderid')]
    [Description('')]
    Forderid: Torders;
  public
    property orderflowid: Integer read Forderflowid write Forderflowid;
    property tijdstip: Nullable<TDateTime> read Ftijdstip write Ftijdstip;
    property status: Nullable<string> read Fstatus write Fstatus;
    property orderid: Torders read Forderid write Forderid;
  end;

5

I have figured it out! Sorry to have wasted your time. It's too bad I can't delete the last two posts above.
Anyway, the result is:

        Results := OM.Find<TOrderflow>
          .CreateAlias('orderid', 'or')
          .Select(TProjections.ProjectionList
              .Add(TProjections.Prop('or.orderid'))
              .Add(TProjections.Max('tijdstip'))
              .Add(TProjections.Group('or.orderid'))
          )
          .Add(not TLinq.Sql('{status} IN (''T999'', ''S999'', ''T998'', ''S998'')'))
          .OrderBy('or.orderid')
          .ListValues;

6

Is it possible to do a more complex query in Aurelius, one like this? Or is Aurelius not meant for this:



select
  ofl2.orderflowid,
  ofl.*
from
(
select ofl.orderid, MAX(ofl.tijdstip) as tijdstip from
orderflow ofl left join statussen sta on ofl.statusid = sta.statusid where not
(sta.status IN ('T999','S999','T998','S998')) group by ofl.orderid order by
ofl.orderid
) as ofl
left join orderflow ofl2
on ofl.tijdstip=ofl2.tijdstip and
ofl.orderid=ofl2.orderid where ofl.orderid IS NOT NULL
7

Unfortunately currently there is no way to query a sub query (select from a subquery).

8

OK, thank you Wagner. I'll do it in 2 steps then.