Room/SQLite returning two different results in one query

January 8, 2022

So I’ve got the following queries:

//returns min max value my odomoter field must have
@Query("SELECT MAX(odometer) FROM MaintenanceRecord WHERE vehicleId = :maintenanceVehicleId AND " +
        "maintenanceTs < :dateAsLong")
Maybe<Float> getMinAllowedOdometer(long dateAsLong, int maintenanceVehicleId);


//returns the max value my odometer field is allowed to have 
@Query("SELECT MIN(odometer) FROM MaintenanceRecord WHERE vehicleId = :maintenanceVehicleId AND " +
        "CAST(strftime('%s',DATE(maintenanceTs/1000,'unixepoch'))  AS  integer) > " +
        "CAST(strftime('%s',DATE(:dateAsLong/1000,'unixepoch')) AS integer)")
Maybe<Float> getMaxAllowedOdometer(long dateAsLong,int maintenanceVehicleId);

One function returns the min value the odometer column must have, and the other query returns the max value the odometer column is allowed to have.

The issue is that BOTH functions are executed after each other since I need to subscribe to both functions. That’s bad practice, In my honest opinion.

Can I put these two queries in one query and return Maybe<Float, Float> as a result?
My other solution would be running these functions synchronously; instead of Maybe<Float>, I would directly return Float.

>Solution :

You can use conditional aggregation to get both columns in a single query:

SELECT MAX(CASE WHEN maintenanceTs < :dateAsLong THEN odometer END) AS max_odometer,
       MIN(CASE WHEN CAST(strftime('%s', DATE(maintenanceTs / 1000, 'unixepoch')) AS INTEGER) > CAST(strftime('%s', DATE(:dateAsLong / 1000,'unixepoch')) AS INTEGER) THEN odometer END) AS min_odometer
FROM MaintenanceRecord 
WHERE vehicleId = :maintenanceVehicleId;