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.
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>/*>*/
}
:-
@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
Flows as required.