Filtering on a One-To-Many Relationship

BIZ TMS XData
1

Hi,

How can I filter on a one-to-many relationship?

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll)]
    [ForeignJoinColumn('ProspGUID', [TColumnProp.Required])]
    FProspectsSC: Proxy<TList<TProspectsSC>>;

  [Entity]
  [Table('ProspectsSC')]
  [Id('FGUID', TIdGenerator.Guid)]
  TProspectsSC = class
  private
    [Column('"GUID"', [TColumnProp.Required])]
    FGUID: TGuid;

    [Column('ProspGUID', [])]
    FProspGUID: Nullable<TGuid>;

    [Column('ClassID', [])]
    FClassID: Nullable<Integer>;

    [Column('SubID', [])]
    FSubID: Nullable<Integer>;
  public
    property GUID: TGuid read FGUID write FGUID;
    property ProspGUID: Nullable<TGuid> read FProspGUID write FProspGUID;
    property ClassID: Nullable<Integer> read FClassID write FClassID;
    property SubID: Nullable<Integer> read FSubID write FSubID;
  end;

I am using the filter:

ProspectsSC/ClassID eq 1 and ProspectsSC/SubID eq 4

But all of the expanded ProspectSC's are still listed e.g.

     "ProspectsSC": [
        {
          "$id": 2,
          "GUID": "703F6B16-9172-4DE4-B0F3-A017C1691BB0",
          "ProspGUID": "34AA4C80-6545-4435-8907-7C06C8E1D8F7",
          "ClassID": 1,
          "SubID": 1
        },
        {
          "$id": 3,
          "GUID": "05088E0E-D7D1-4D8F-90ED-3CA5071626C3",
          "ProspGUID": "34AA4C80-6545-4435-8907-7C06C8E1D8F7",
          "ClassID": 1,
          "SubID": 2
        },
        {
          "$id": 4,
          "GUID": "D3CD226D-7AD6-49B9-AB5B-1B35D45AC1CF",
          "ProspGUID": "34AA4C80-6545-4435-8907-7C06C8E1D8F7",
          "ClassID": 1,
          "SubID": 3
        },
       etc.

Thanks,

Ken

2

The filter you use affects how the master (parent) records will be returned.

The child/detail lists will always be retrieved individually in separated SQL statements after the parent/master records.

So you can't filter then, unless you use the Where attribute to predefine a filter at mapping time, and then enable the filter per manager. The process is described in the chapter about global filters.

Another option is simply doing a direct query in TProspectsSC class.

3

Any other ideas?

I am using a 3rd party grid to display the results which uses OData so need to use a select e.g.

  APPS_URL:=EAPBaseURL+'/Prospects?$select=GUID,OwnStatusInt,MaxPrice,ManagingMnem,'+
    'NegotiatorGUID,Clients/NameAndAddressString&$filter='+TSparkleUtils.PercentEncode(S)+
    '&$expand=Clients,ProspectsSC';
4

I don't understand how things related? What does the grid has to do with it?

Any other ideas?

The more flexible way is to create your own service operation.

5

The grid is doing the filter. I think I will pre-process this in a service operation and get a list of GUID's to use that in the filter

1 Like