$filter in an expand doesn't work about cds-pg HOT 7 CLOSED

Hello.
We have encountered the same error. We analyzed it and realized that it is related to the way SAP CDS handles references. For example in your SQL statement above you filter breweries by name using association:

a.brewery.name

This SQL statement is not supported by PostgreSQL. It is suitable for SAP Hana, but for PostgreSQL we should process the association with INNER JOIN.

Please see below the code from CDS that is responsible for creation of this dot-association:

As CDS is closed source, I can't propose to change it to more widely supported INNER JOIN construction.

One of the ideas is to revise SQL queries within cds-pg by replacing dot-associations with inner joins. We are not sure this is the best solution.

What do you think? Do you have any other ideas?

from cds-pg.

For better analysis of the issue please run your project with the environment variable:

DEBUG=cds-pg

set to get the generated SQL shown.

from cds-pg.

Sorry for the delay.
This are the logs shown in the console:

[cds] - GET /beershop-admin/Beers?$filter=brewery/name eq ''&$expand=brewery
[cds-pg] - sql > SELECT a.ID AS "a_ID", a.name AS "a_name", a.abv AS "a_abv", a.ibu AS "a_ibu", a.brewery_ID AS "a_brewery_ID", b.ID AS "b_ID", b.name AS "b_name", $1 AS "b_IsActiveEntity", $2 AS "b_HasActiveEntity", CASE WHEN B_DRAFTS.DRAFTADMINISTRATIVEDATA_DRAFTUUID IS NOT NULL THEN TRUE ELSE FALSE END AS "b_HasDraftEntity" FROM BeershopAdminService_Beers a LEFT JOIN BeershopAdminService_Breweries b ON ( b.ID = a.brewery_ID ) LEFT JOIN BeershopAdminService_Breweries_drafts b_drafts ON ( b_drafts.ID = b.ID ) WHERE a.brewery.name = $3 ORDER BY a.ID ASC LIMIT 1000 OFFSET 0
[cds-pg] - values > [ true, false, '' ]

from cds-pg.

Hi @negativename,

when needed we need to adjust the SQL in cds-pg. So it would be great if you can contribute this.

from cds-pg.

Hello,
I've created a pull request

from cds-pg.

Hello everyone
This looks like a great fix, is it possible to merge the PR asap? We have a project on CAP with PostgreSQL and this would be very helpful, if expanded $filters are possible, since we have many occurrences in our project which we tested locally on SQLite.

from cds-pg.

unfortunately the tests in #335 still fail - if you need this asap, would you mind taking a look at the failing tests and eventually contribute the fix onto the PR?

from cds-pg.