Cloud Application Programming Model sqlite order and/or filter on association

[tomver]

Hi,

I am working as an software engineer intern at Dimensys (Netherlands) with the Cloud Application Programming Model.

My problem is that I want to filter and/or order my collection by an assocation.

I have the following example (it is a consumer app that I made with a tutorial).

The data model (schema.cds) is as follows:

entity Books {
  key ID : Integer;
  title  : String;
  stock  : Integer;
  author : Association to Authors;
}

entity Authors {
  key ID : Integer;
  name   : String;
  books  : Association to many Books on books.author = $self;
}

entity Orders : cuid {
  book     : Association to Books;
  quantity : Integer;
}

Now I want to call the following url (which I created following the OData documentation):

http://localhost:4004/catalog/Books?$expand=author&$orderby=author/name

But get the following error:

[2020-04-06T07:53:05.579Z | ERROR | 1276421]: SQLITE_ERROR: no such column: author.name

Am I doing something wrong, or is there no way of filtering and ordering an collection by their associations (in sqlite)?

I also found this:

https://github.com/sapmentors/cap-community/issues/22

with the following line in the changelog of cds-sql 1.24.0

- `where` and `orderBy`clauses containing navigations in combination with expand are correctly translated to SQL

Kind regards,

Tom

[johannesvogel · Accepted solution]

Hi Tom,

at the moment there is no generic way of sorting/filtering on associations using sqlite. Only option would be to implement it yourself using custom handlers.

The mentioned changelog is unfortunately only true for SAP HANA.

Best regards,

Johannes