Skip to Content
0
Dec 30, 2021 at 07:21 PM

How to disable "where exists" subqueries when navigating by navigation property

220 Views

Hi!

Following documentation we want to use implicit sort, defined in the entity.

So here are several observations:

1 - Direct query works: /odata/bucket_balance?asset_id=123456&asset_type=ACCOMMODATION

The result is sorted correctly according to the defined order

2 - Query via association - doesn't work

/odata/assets/123456/ACCOMMODATION/bucket_balance

In this case the order looks like random

3 Query via association but with defined order - works again

/odata/assets/123456/ACCOMMODATION/bucket_balance?$orderby=created

So problem is only with case 2. When I have activated the trace I see that select is like that:

SELECT "source",
    "id",
    "type",
    "created",
    "purchase_id",
    "asset_id",
    "asset_type"
FROM "api.odata.bucket_balance" "T1"
WHERE EXISTS (
        SELECT 1
        FROM "api.odata.assets" "T0"
        WHERE "T0"."asset_id" = ?
            AND "T0"."asset_type" = ?
            AND (
                "T0"."asset_id" = "T1"."asset_id"
                AND "T0"."asset_type" = "T1"."asset_type"
            )
    )
LIMIT 1000 OFFSET 0 with parameters ('LOCALE' = 'en') [ '123456', 'ACCOMMODATION' ]

If I execute such a select from console - it also returns random order, but if we remove exists condition the order is taken from the view which is correct

SELECT "source",
    "id",
    "type",
    "created",
    "purchase_id",
    "asset_id",
    "asset_type"
FROM "api.odata.bucket_balance" "T0"
        WHERE "T0"."asset_id" = ?
            AND "T0"."asset_type" = ?    
LIMIT 1000 OFFSET 0 with parameters ('LOCALE' = 'en') [ '123456', 'ACCOMMODATION' ]

Do you may be know how to deactivate it this existence check? May be some annotation or special cardinality?

Thanks!

P.S.: it would be also nice to hear from devs if it's a bug or not.