CAP: How can I add custom query options in my custom handler?

[robbewuyts]

I need to retrieve all entries from my HANA DB entity with temporal aspect, so also the 'invalid' ones. I was hoping a CQN query like this should work, but only the 'valid' entries are retrieved from the database:

    public async getTemporalFactors(id:Array<string>){
        const service = await cds.connect.to('CO2SC3Service');
        const cqn = {
            SELECT: {
                from: { ref: ['TransportFactors'] },
                'sap-valid-from': [{ val: '1970-01-01' }]
            }
        };
        return await service.run(cqn);
    }

I tried this because I noticed you can add these parameters for external oData services: August 2022 | cap≽ire (cloud.sap)

Ideally I want to change the req.query parameter in my on Read handler, to add the sap-valmid-from parameter.

[robbewuyts]

In case anyone else has the same problem, you can select older/invalid entries by suppressing the temporal aspect using annotations. Somewhere in the change logs I found this:

• Generation of temporal WHERE clause can be suppressed by annotating the validFrom/validTo elements of the projection with false or null.

So by using a projection like this, I can finally retrieve all entries:

entity TransportFactors as projection on co2.TransportFactors {
        *,
        @(cds.valid.from: false)
        validFrom,
         @(cds.valid.to: false)
        validTo
    }

Would've been better if this was mentioned in the temporal documentation... 😒

Another way is selecting directly from the database instead of a service, in that case the annotations from the temporal aspect are not taken into account

[robbewuyts]

.

[Willem_Pardaens]

Temporal entities rely on specific columns to calculate the 'valid-at' and 'valid-from' entries (see: https://cap.cloud.sap/docs/guides/temporal-data). This would imply you can create your own standard query to select the desired entries like this:

SELECT.from(TransportFactors).where({ validFrom: { '>=': '1970-01-01' } })

assuming the 'valid-from' temporal column is called validFrom in your entity.

[robbewuyts]

This would only fetch 'valid' entries (so where validFrom <= now and validTo >= now), and filter those where validFrom >= 1/1/1970.

To me it seems that the the annotations on this temporal aspect make sure on DB level that only valid entries are fetched, unless you use this url parameter. 

I hope I'm wrong here 🙂