How to sort query result by argument of list order?-CodePudding

The query below return all items with id from :ids list. But table and input list have different order. Is there way to add something like pseudocode ORDER BY :ids?

@Query("SELECT * FROM item_table WHERE type = :type AND itemId IN (:ids)")
fun getPagingSource(type: EntityType, ids: List<String>): PagingSource<Int, ItemEntity>

CodePudding user response：

One way to do it is instead of passing the ids as a List to be checked with the operator IN, to pass them as a comma separated string, something like '36,3,14' and use the operator LIKE in the WHERE clause to filter the rows.

Then, in the ORDER BY clause use the string function INSTR() to sort the rows:

SELECT * 
FROM item_table 
WHERE type = :type
  AND ',' || :ids || ',' LIKE '%,' || itemId || ',%'
ORDER BY INSTR(',' || :ids || ',', ',' || itemId || ',');

See a simplified demo.