How can I get a List from the Server??

BIZ TMS XData
1

I am trying to get a list from the Server that's not just a basic Aurelius list and I just dont know how to do it.
I want to use this simple query that returns a distinct list of IDs:

select distinct p.itemid from p_iteminzet p, items i where p.itemid=i.itemid

I want to get this done with a Serviceinterface across to an XData client. I prefer to get a TList<TItem> (from the items table) across, but a list of integers is also fine.
The Service itself, its Interface and the Client are not the issue. They work ok with standard Aurelius Lists and standard Delphi types.
 I just don't know how to do it with a query like the one above. I read the manuals several times, but everything I try fails. The examples I have don't help much either, they only do the basic stuff. Can you please help me? I'm not far from giving up on XData...

2

What is the problem you get, can you specify?

In the meanwhile, I could guess that you are having problems with the fact the TItem is not mapped? but if you are retrieving it from the database and filling a TItem object, why don't you use Aurelius anyway?
In any case, XData needs Aurelius mapping at least to know how to serialize the objects. You don't need to persist them anywhere, but you just need to tag them with [Entity] and at least [Automapping] attributes.
3

I have both in Aurelius:

  [Entity>
  [Table('items')>
  [Description('')>
  [Sequence('items_itemid_seq')>
  [Id('Fitemid', TIdGenerator.IdentityOrSequence)>
  Titems = class
  private
    [Column('itemid', [TColumnProp.Required>)>
    [Description('')>
    Fitemid: integer;
   .....

   [Entity>
  [Table('p_iteminzet')>
  [Description('')>
  [Sequence('planiteminz_planiteminzid_seq')>
  [Id('Fp_iteminzetid', TIdGenerator.IdentityOrSequence)>
  Tp_iteminzet = class
  private
    [Column('p_iteminzetid', [TColumnProp.Required>)>
    [Description('')>
    Fp_iteminzetid: integer;
    ......
    [Association([>, CascadeTypeAll - [TCascadeType.Remove>)>
    [JoinColumn('itemid', [>, 'itemid')>
    [Description('')>
    Fitemid: Titems;



 When I use this query on a database:

select distinct p.itemid from p_iteminzet p, items i where p.itemid=i.itemid


I get a list of IDs. But I want those IDs on my XData client, using Aurelius and XData. Somehow.
 I tried something like this:
 

 function TRPService.GetIngezetteItems: TList<TItems>;
var
  OM: TObjectManager;
  nTeller: Integer;
  R: TObjectList<TCriteriaResult>;
begin
  OM:=TXDataOperationContext.Current.GetManager;
    // Bepaal welke items zijn ingezet
  R:=OM.CreateCriteria<Tp_iteminzet>
  .CreateAlias('p_iteminzet', 'p')
  .CreateAlias('itemid', 'i')
  .Select(TProjections.ProjectionList
    .Add(TProjections.Prop('p.itemid'))
    .Add(TProjections.Prop('i.code'))
    )
    .RemovingDuplicatedEntities
    .OrderBy('i.code',true).ListValues;
  Try
    >>>>>> Don't know what to do here! How can I get something in result?
  Finally
    R.Free;
  End;
end;


 

4

I don't understand the logic. If you just want info from TItems, why don't you just use


Result := OM.Find<TItems>.OrderBy('code').List;

?
When you create projections like this, you are not returning TItems anymore, it's another type of object (TCriteriaResult)
5

That's just the point! I don't want a list of all Items! I want a list of all the items that are used in a certain period. I know that I get a TCriteriaResult back.
Say I have 350 items. In a certain period I use only 12 of them. I can get their IDs from the database with a simple projection. But how do I get a list of only those items to the XData client??

6

It's different things. You return TItems objects, period.

But then you can filter them:

Result := OM.Find<TItems>.OrderBy('code').<addfilterhere>.List;

why this doesn't fill your requirement.
7

I want to find out which Items are used in a certain period. I can't do
that with a simple filter. I have to use a projection. There is simply
no other way.
If there is, please tell me what I can fill in in your example above to make this query:


8

Sorry, but that SQL doesn't make sense to me!!!

p.itemid is the foreign key for i.itemid. If you are retrieving a distinct list of p.itemid values, that is simply a list of i.itemid values (since i.itemid is unique because it's primary key). You SQL doesn't look much different for me than this one:

select i.itemid from items i

??
Am I overlooking something?

Wagner Landgraf2015-11-14 19:12:39
9

I will describe the process:
I have a table p_iteminzet with a lots of entries. Say 100.000. Every record has an itemid, that points to an item in the second table. The second table is items. That one has 75 items. But not all of these items are used in p_iteminzet. I want to know which ones have been used. So I make a distinct query on p_iteminzet to retrieve the items that are used in p_iteminzet:

select distinct p.itemid from p_iteminzet p, items i where p.itemid=i.itemid



That gives a list of items but NOT necessarely ALL the items!
When I make a query like the one above I get 40 items.
When I make the query you suggested I get 75 items (all of them). I don't want them all, I ONLY want the ones used in p_iteminzet!!

Hope that clears it?

10

Create a ManyValuedAssociation in TItems class pointing to Tp_iteminzet:


[ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll, 'FItemid')]
FItemzetList: Proxy<TList<Tp_iteminzet>>;

Then find TItems objects using distinct but where the association is not null:

Result := OM.Find<TItems>
  .CreateAlias('Itemzetlist', 'p')
  .Where(not TLinq.IsNull('p.p_itemzetid'))
  .RemovingDuplicatedEntities
  .List

More info:

http://www.tmssoftware.biz/business/aurelius/doc/web/index.html?manyvaluedassociation.htm
http://www.tmssoftware.biz/business/aurelius/doc/web/index.html?lazy-loading_associations.htm
http://www.tmssoftware.biz/business/aurelius/doc/web/index.html?removing_duplicated_objects.htm

11

Thank you Wagner, it works!
Gives me a valuable insight in how to work with this kind of relations.