How to implement sorting in Room and how do I test it?

Question

I'm currently trying to implement sorting in Room. Here's my code:

Dao

@Query("SELECT * FROM model ORDER BY " +
        "CASE WHEN :isAsc = true THEN :field END ASC, " +
        "CASE WHEN :isAsc = false THEN :field END DESC ")
fun sortList(field: String, isAsc: Boolean?): Flow<List<Model>>

Repo

@WorkerThread
suspend fun sort(field: String, isAsc: Boolean?):  Flow<List<Model>>{
    return dao.sortList(field, isAsc)
}

ViewModel

fun sort(field: String, isAscending: Boolean? = true) = viewModelScope.launch {
    repo.sort(field, isAscending)
}

As a side question, is my code possible? Can I make the order by field a parameter as well? I want to have only one function for sorting, if possible.

This is how I currently test it.

dao.insert(instance1)
//repeat this a couple of times

val tempLst = arrayListOf<Model>()
tempLst.add(instance1)
//repeat this the same times as the dao.insert

val sortedLst = tempLst.sortedBy { it.schedDate }

val latch = CountDownLatch(1)
val job = async(Dispatchers.IO) {
    dao.sortList("schedule_date", true).collect {
        assertThat(sortedLst).isEqualTo(it)
        latch.countDown()
    }
}
latch.await()
job.cancelAndJoin()

Now, I searched for how to test sorting but can't seem to find any. Is sorting testable? How do you test it? Also, for some reason, that test doesn't finish. I hope you can help me with that as well.

MikeT · Accepted Answer · 2023-03-22T11:13:28.060

Is sorting testable?

Yes see below.

Can I make the order by field a parameter as well?

Yes, BUT see below.

How do you test it?

as below,

Also, for some reason, that test doesn't finish.

your issue is probably because it is running an another thread. again see below

How I have tested you actual query:-

First created the DB code NOTE using .allowMainThreadQueries AND without Flow as per :-

@Entity
data class Model(
    @PrimaryKey
    val modelId: Long?=null,
    val col1: String,
    val col2: String,
    val col3: String
)

@Dao
interface TheDAOs {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(model: Model): Long

    @Query("SELECT * FROM model ORDER BY " +
            "CASE WHEN :isAsc THEN :field END ASC, " +
            "CASE WHEN :isAsc THEN :field END DESC ")
    fun sortList(field: String, isAsc: Boolean?): /*Flow<*/List<Model>/*>*/
}

Note true/false removed as they were only introduced in more recent API's, they are not needed. see SQLiteException on some Android 9, 10 devices when using CASE, WHEN TRUE

:-

@Database(entities = [Model::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getTheDAOs(): TheDAOs

    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance=Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries() /*<<<<<<<<<< FOR TESTING >>>>>>>>>>*/
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Then in an activity, coded:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: TheDAOs
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getTheDAOs()

        dao.insert(Model(modelId = 10, col1 = "ABC", col2="MNO", col3 = "XYZ"))
        dao.insert(Model(modelId = 20, col1 = "XYZ", col2="ABC", col3 = "MNO"))
        dao.insert(Model(modelId = 30, col1 = "MNO", col2="XYZ", col3 = "ABC"))

        for (m in dao.sortList("col1",true)) {
            Log.d("DBSORT_RUN1","ID is ${m.modelId} COL1=${m.col1} COL2=${m.col2} COL3=${m.col3}")
        }
        for (m in dao.sortList("col2",true)) {
            Log.d("DBSORT_RUN2","ID is ${m.modelId} COL1=${m.col1} COL2=${m.col2} COL3=${m.col3}")
        }
        for (m in dao.sortList("col1",false)) {
            Log.d("DBSORT_RUN3","ID is ${m.modelId} COL1=${m.col1} COL2=${m.col2} COL3=${m.col3}")
        }
    }
}

The result written to the log:-

2023-03-22 21:10:34.027 D/DBSORT_RUN1: ID is 10 COL1=ABC COL2=MNO COL3=XYZ
2023-03-22 21:10:34.027 D/DBSORT_RUN1: ID is 20 COL1=XYZ COL2=ABC COL3=MNO
2023-03-22 21:10:34.027 D/DBSORT_RUN1: ID is 30 COL1=MNO COL2=XYZ COL3=ABC


2023-03-22 21:10:34.030 D/DBSORT_RUN2: ID is 10 COL1=ABC COL2=MNO COL3=XYZ
2023-03-22 21:10:34.030 D/DBSORT_RUN2: ID is 20 COL1=XYZ COL2=ABC COL3=MNO
2023-03-22 21:10:34.030 D/DBSORT_RUN2: ID is 30 COL1=MNO COL2=XYZ COL3=ABC


2023-03-22 21:10:34.033 D/DBSORT_RUN3: ID is 10 COL1=ABC COL2=MNO COL3=XYZ
2023-03-22 21:10:34.033 D/DBSORT_RUN3: ID is 20 COL1=XYZ COL2=ABC COL3=MNO
2023-03-22 21:10:34.033 D/DBSORT_RUN3: ID is 30 COL1=MNO COL2=XYZ COL3=ABC

They have not been sorted as expected (they are all the same)

However, perhaps consider:-

@Query("SELECT * FROM model ORDER BY  " +
        "CASE WHEN :isAsc AND :field='col1' THEN col1 END ASC, "
        + "CASE WHEN NOT :isAsc AND :field='col1' THEN col1 END DESC, "
        + "CASE WHEN :isAsc AND :field='col2' THEN col2 END ASC, "
        + "CASE WHEN NOT :isAsc AND :field='col2' THEN col1 END DESC, "
        + "CASE WHEN :isAsc AND :field='col3' THEN col3 END ASC, "
        + "CASE WHEN NOT :isAsc AND :field='col3' THEN col3 END DESC "
)
fun sortListV2(field: String, isAsc: Boolean?): /*Flow<*/List<Model>/*>*/

Resulting in (when sortListV2 is used):-

2023-03-22 21:20:49.457 D/DBSORT_RUN1: ID is 10 COL1=ABC COL2=MNO COL3=XYZ
2023-03-22 21:20:49.457 D/DBSORT_RUN1: ID is 30 COL1=MNO COL2=XYZ COL3=ABC
2023-03-22 21:20:49.457 D/DBSORT_RUN1: ID is 20 COL1=XYZ COL2=ABC COL3=MNO


2023-03-22 21:20:49.460 D/DBSORT_RUN2: ID is 20 COL1=XYZ COL2=ABC COL3=MNO
2023-03-22 21:20:49.460 D/DBSORT_RUN2: ID is 10 COL1=ABC COL2=MNO COL3=XYZ
2023-03-22 21:20:49.460 D/DBSORT_RUN2: ID is 30 COL1=MNO COL2=XYZ COL3=ABC


2023-03-22 21:20:49.462 D/DBSORT_RUN3: ID is 20 COL1=XYZ COL2=ABC COL3=MNO
2023-03-22 21:20:49.462 D/DBSORT_RUN3: ID is 30 COL1=MNO COL2=XYZ COL3=ABC
2023-03-22 21:20:49.462 D/DBSORT_RUN3: ID is 10 COL1=ABC COL2=MNO COL3=XYZ

the expected results

An alternative is to utilise an @RawQuery, which can be simpler BUT is not checked at compile time e.g.

@RawQuery
fun sortListRawQuery(query: SimpleSQLiteQuery): List<Model>
fun sortListV3(field: String, isAsc: Boolean): List<Model> {
    var ascDesc = "ASC"
    if (!isAsc) ascDesc = "DESC"
    return sortListRawQuery(SimpleSQLiteQuery("SELECT * FROM model ORDER BY ${field} ${ascDesc};"))
}

And using sortListV3 then :-

2023-03-22 22:01:14.133  D/DBSORT_RUN1: ID is 10 COL1=ABC COL2=MNO COL3=XYZ
2023-03-22 22:01:14.133  D/DBSORT_RUN1: ID is 30 COL1=MNO COL2=XYZ COL3=ABC
2023-03-22 22:01:14.133  D/DBSORT_RUN1: ID is 20 COL1=XYZ COL2=ABC COL3=MNO


2023-03-22 22:01:14.135  D/DBSORT_RUN2: ID is 20 COL1=XYZ COL2=ABC COL3=MNO
2023-03-22 22:01:14.135  D/DBSORT_RUN2: ID is 10 COL1=ABC COL2=MNO COL3=XYZ
2023-03-22 22:01:14.135  D/DBSORT_RUN2: ID is 30 COL1=MNO COL2=XYZ COL3=ABC


2023-03-22 22:01:14.138  D/DBSORT_RUN3: ID is 20 COL1=XYZ COL2=ABC COL3=MNO
2023-03-22 22:01:14.138  D/DBSORT_RUN3: ID is 30 COL1=MNO COL2=XYZ COL3=ABC
2023-03-22 22:01:14.138  D/DBSORT_RUN3: ID is 10 COL1=ABC COL2=MNO COL3=XYZ

Of course you can wrap the results in Flows as required.

Oh, thanks for all the info. I'll try this out – rminaj Mar 22 '23 at 10:44 — rminaj, Mar 22 '23 at 10:44

How to implement sorting in Room and how do I test it?

1 Answers1