Skip to Content
3
May 29, 2020 at 09:21 PM

CAP - Filter not working on expanded entity

19986 Views

Hi all,

I have a use case where I create a view on top of a table to calculate a field. In the same table I will create an association to the view on top of this table so I can navigate to this calculated field. This works fine except when I try to expand and filter on this association.

I simulated a simplified version of the problem, the datamodel:

Authors is the entity and AuthorStatus is a view on top of this table. AuthorStatus in Authors table is the association between the table and the view:

namespace sap.capire.bookshop;
entity Authors {
  key ID           : Integer;
      name         : String(111);
      AuthorStatus : Association to AuthorStatus on AuthorStatus.ID = ID;
};
entity AuthorStatus as select from Authors as offer {
    ID,
    'test' as status : String(20)
};

Both are exposed in the service layer:

using { sap.capire.bookshop as my } from '../db/schema';
service AdminService @(_requires:'authenticated-user') {
  entity Authors as projection on my.Authors;
  entity AuthorStatus as projection on my.AuthorStatus;
}

When I try to test by expanding the data and filtering on the name of an author, it all works fine:

http://localhost:4004/admin/Authors?$expand=AuthorStatus&$filter=name eq 'Wouter'

When I try to filter on the field "status" in the association, I get the following error:

Url: http://localhost:4004/admin/Authors?$expand=AuthorStatus&$filter=AuthorStatus/status eq 'test'

Error:

When I enable debugging and look at the generated query, I notice it generates wrong SQL:

SELECT a.ID AS "a_ID", a.name AS "a_name", b.ID AS "b_ID", b.status AS "b_status" FROM AdminService_Authors a LEFT JOIN AdminService_AuthorStatus b ON ( b.ID = a.ID ) WHERE a.AuthorStatus.status = 
? LIMIT 1000 [ 'test' ]

I guess it should use the "status" in the where from table "b"?

Could this be a bug or am I doing something that's not allowed?

Thank you in advance!

Kr, Wouter

Attachments

cap-bug.png (16.4 kB)
cap-bug-2.png (7.9 kB)