CAP - Filter not working on expanded entity

[WouterLemaire]

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

[WouterLemaire]

I also want to add that this does work when it is deploy to a HANA database. This only occurs when testing locally.

[martin_voros · Accepted solution]

Hi,

I don't have the answer but I remember I saw exactly this issue during workshop with SAP on CAP. We tried to deploy to HANA and it worked there OK. So it looked like a bug in CAP. Maybe you can try to deploy to HANA to see if you can replicate it there.

[WouterLemaire · Accepted solution]

Thank you for your reply! Indeed, on hana it does work. But we need it locally as well to be able to test the ui.

[martin_voros · Accepted solution]

My point was that if it works on HANA, it should work on SQLite as well. Hence it looks like a bug to me and CAP developers should address it.

[david_kunz2]

Hi,

Path expressions, in this case `AuthorStatus/status`, only work on SAP HANA, not on SQLite.

Best regards,
David

[WouterLemaire]

Any plans to implement this for SQLite as well? Not really a solution if you are not able to test your project without a HANA database locally...

[gregorw]

Hi Wouter,

I would suggest to use a HXE DB only for local development. I use this setup over 9 month now and I'm really happy with it as it is exactly as the production environment. Only that the VM for the DB only requires only 8 GB.

CU
Gregor

[former_member194549]

Hi Wouter,

I can only support Gregor's proposal. I have also been working with a HANA database for 7 months now and I am also happy with it.
Unlike Gregor, my HANA does not run locally on my laptop, but I use a HDI container running on a CF space.

Best regards
Simon

[vfweiss]

Hi Wouter,

We have switched to use an HDI Container on the Cloud as well and connect to that if we need these sorts of queries.

I tried debugging through the entire sql resolving a while back, but couldn't really find an easy fix for it. (Yeah manually changing the a part of the setup in debug, but that's no solution).
david.kunz2 is it perhaps possible to note this somewhere in the documentation or do you have any plans like Wouter asked to resolve this? It's a recurring question so far. 🙂
https://answers.sap.com/questions/13016370/cloud-application-programming-model-sqlit-order-an.html
https://github.com/sapmentors/cap-community/issues/22

What I ran into for trying to manually fix this, was the following:
In the buildJoinQueries in expandCQNToJoin.js (@sap/cds-runtime/lib/cds-sql/expand) first the where was constructed and after that the join, which caused the where to not be properly mapped to what's joined.
I didn't have much time to delve deeper into this though.

Kind regards,

Vincent