How do I return the results of two queries using one @Query statement?

I have a database of items with a single table. Each item has a due date (saved as a long in the Room database) or no due date (saved as -1 in the database). I would like to have a query that returns all items with due dates in ascending order and then return all of the remaining items, sorted by a timestamp that is saved in the database. The timestamp represents the calendar date and time when the item was originally saved to the Room database.

Here is an example of the output I expect, using a U.S. calendar for the due dates:

1. 8/17/2022 (August 17, 2022 due date)
2. 8/19/2022 (due date)
3. 12/15/2022 (due date)
4. 5601 timestamp (no due date)
5. 4200 timestamp (no due date)
6. 1150 timestamp (no due date)

The below query in the Dao returns the expected results of the first part of the query, the ascending due dates. So how do I append the below query with the second part where I also return the items that have no due dates and show their timestamps in descending order? I tried multiple ways to use UNION, UNION ALL, etc. with no luck.

@Query("SELECT * FROM cards WHERE cardDuedatentime !=-1 ORDER BY cardDuedatentime ASC")

CodePudding user response：

First sort by the boolean expression cardDuedatentime = -1 to get all the rows with no due date at the bottom of the resultset.
Then use conditional sorting with a CASE expression to sort the rows with no due date descending and the rows with a valid due date ascending:

SELECT *
FROM cards
ORDER BY cardDuedatentime = -1,
         CASE WHEN cardDuedatentime = -1 THEN -timestamp ELSE cardDuedatentime END;

If you want only 1 column in the results:

SELECT CASE WHEN cardDuedatentime = -1 THEN timestamp ELSE cardDuedatentime END time
FROM cards
ORDER BY cardDuedatentime = -1,
         CASE WHEN cardDuedatentime = -1 THEN -timestamp ELSE cardDuedatentime END;

See the

Two CTE's (Common Table Expressions, aka temporary tables) were used, each to extract one of the sets of data importantly sorting them independently. They are then combined via the UNION and not sorted (as the sort affects the complete set of data).

• Note how the data has purposefully been inserted so that they are not appropriately sorted.

Here's

Whilst with the first simpler SELECT, with the additional data, the result (WRONG) would be :-

• i.e. for the C row as -3 is not equal to -1 then it will be as if it were a valid date,

• so < 0 treats it as an invalid date so it is include in the set of invalid dates;

• However, with < 0 allowing the -3 to in the invalid date set, the second sort, on the cardDueDatentime would place -3 before the -2 and before the -1 so the max function will for values less than -1 make them -1 and hence -3 becomes -1 (as with all the other invalid dates) so the third sort field is then the applicable sort field within the set of invalid dates.

  • this could be useful if you for some reason wanted to have different sets/types of invalid dates but not affect the query.