Calling a database query inside a transformation function

I am trying to call a database query inside a transformation map function and I am running to various errors. A simplified version of my project code is given below.

Entities

@Entity(tableName = "category_table")
data class CategoryDatabase(

    @PrimaryKey(autoGenerate = true)
    var categoryId : Long = 0L,

    @ColumnInfo(name="category_name")
    var categoryName : String = ""
)

.

@Entity(tableName = "main_table")
data class MainDatabase(

    @PrimaryKey(autoGenerate = true)
    val mainId : Long = 0L,

    @ColumnInfo(name="category_one")
    val mainCategoryOne : Long = 0L
)

Dao

@Query("SELECT category_name FROM CATEGORY_TABLE where categoryId= :categoryId")
    suspend fun getCategoryNameById(categoryId: Long) : String

Repository

suspend fun getCategoryNameById(categoryId : Long) : String {
        return databaseDao.getCategoryNameById(categoryId)
}

ViewModel

In the view model I queried the main_table and obtained the data. This was stored in readAllData variable which is of the type LiveData<List< MainDatabase >>. The main table contains the category Id. I want to use transformation.map to get the category name. I implemented the following in viewmodel.

suspend fun getName(categoryId: Long) : String{
        val d : Deferred<String> = viewModelScope.async {
            repository.getCategoryNameById(categoryId)
        }
        return d.await()
    }

then in init I added

  init{
    ..
    ..
    ..

listTransformed = Transformations.map(readAllData)
        { list ->
            list.map {
                   viewModelScope.launch {
                                         getName(it.mainCategoryOne)
                   }
            }

        }
    }

I think I need to use the async and await since I need to wait for the result. Which makes getName a suspend function and thus can only launched in a coroutine. However, when launching it like in the code above, I get an error

 Type Mismatch.
 Required: String 
 Found : Job

If I try to call the query without a coroutine, the app crashes since I am attempting a database call from main UI. What would be the correct approach here? Or it there a simpler method to approach this problem?

Thank you

Immediately calling await() on a Deferred means it was pointless to call async in the first place. "Await" means wait for the asynchronous result synchronously, so why did you make it asynchronous in the first place? You can modify your function as follows to get the same behavior without the convoluted stuff:

suspend fun getName(categoryId: Long) : String{
    return repository.getCategoryNameById(categoryId)
}

You can't mix and match LiveData and coroutines like that. Your map lambda function is launching coroutines and returning the resulting coroutine Jobs, so the end result is a LiveData<List<Job>>. A Job doesn't return anything, so it's useless for this purpose. You could use async instead of launch, and then you'd have a LiveData<List<Deferred<String>>> and in the observer you could start a coroutine and call async() on it to unwrap the values. Probably not what you want, since that's convoluted.

Instead, I would convert to a Flow, so you can call suspend functions in your mapping function, and then you can convert it back to a LiveData if you want to stick with using LiveData. In the inner map, you can use async and awaitAll to map the items of the list in parallel. We use coroutineScope { } to do this with child coroutines instead of sibling coroutines. Like this:

  init{
    ..
    ..
    ..

    listTransformed = readAllData.asFlow()
        .mapLatest { list -> 
            coroutineScope {
                list.map { async { getName(it.mainCategoryOne) } }.awaitAll()
            }
        }
        .asLiveData()

Edit: I think I got sidetracked. Since this is a database, it would be more efficient and simpler if you write a DAO function to do this.

Assuming allData is something like this in your DAO:

@Query("SELECT * FROM main_table")
fun getAllData(): LiveData<List<MainDatabase>>

Then you could write another DAO function like this:

// Returns list of category names that are referenced in main_table
@Query("SELECT c.category_name FROM main_table AS m INNER JOIN category_table AS c ON m.category_one=c.categoryId")
fun getAllUsedCategoryNames(): LiveData<List<String>>