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