How to query a Room db from a composable to get item details

Question

Having an application with Room and Compose, I'm on a composable that needs to retrieve data from the database for painting it. It is a list of favorite flights, and favorite only haves the IDs (iata) of the departure and destination airports. My composable haves the favorite but needs to do selects from airport table to recover all the data for each airport and be able to paint it. Is a list, so it need to do it for a lot of items.

Which is the correct approach? I thought was creating a function on the viewmodel that returns the result of the query but then I noticed that this whould return a Flow and not a object instance, also, the composable doesn't have the viewmodel, so I can't call functions of the viewmodel. Which is the best way to solve this?

The database entities:

@Entity
data class Airport(
    @PrimaryKey
    val id: Int,
    @ColumnInfo(name = "iata_code")
    val iataCode: String,
    val name: String,
    val passengers: Int
)

@Entity
data class Favorite(
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    @ColumnInfo(name = "departure_code")
    val departureCode: String,
    @ColumnInfo(name = "destination_code")
    val destinationCode: String
)

The composable:

    LazyColumn(
        modifier = Modifier.padding(8.dp)
    ) {
        items(uiState.favorites) { favorite ->
            //TODO here i need to get two airports using the ids (iata), so I need to query room, once I have them, I can use them to paint the details of both airports of the favorite flight.
        }
    }

The viewmodel function to recover the airport by iata:

fun getAirportByIata(iata: String): Flow<Airport> {
        return flightRepository.getAirport(iata)
    }

Answer 1

Please have a look at the documentation about Room Relationships.

Instead of first retrieving the favorites, and then in a second database operation fetching the airports belonging to them, you can create a new data class like FavoritesAndAirports and let Room automatically join the two tables for you. Then you end up having all data needed already at one place:

items(uiState.favoritesAndAirports) { favoriteAndAirports ->
    // access favorites with their airports
}

In your case, you could model the two airports belonging to one favorite by using two one-to-one relationships. Create a data class like this:

data class FavoriteAndAirports(
    @Embedded val favorite: Favorite,
    
    @Relation(
         parentColumn = "departure_code",
         entityColumn = "iata_code"
    )
    val departureAirport: Airport
    
    @Relation(
         parentColumn = "destination_code",
         entityColumn = "iata_code"
    )
    val destinationAirport: Airport
)

I am assuming here that departure_code and destination_code are pointing towards the iata_code of the Airport entity.

Then, you can access it in your DAO similar to this:

@Transaction
@Query("SELECT * FROM Favorite")
fun getFavorites(): Flow<List<FavoriteAndAirports>>

Room will now load all Favorites, get the two Airports for each Favorite, and will put all the data into the FavoriteAndAirports instances.
This Flow you then can access from your ViewModel:

class FlightsVM(
    private val flightRepository: FlightRepository
) : ViewModel() {

    val favorites = flightRepository
        .getFavorites()
        .stateIn(viewModelScope, SharingStarted.Lazily, emptyList())

}

Finally, consume it in your Composable:

@Composable
fun FavoritesComposable(flightViewModel: FlightVM = viewModel()) {
    val favorites = flightViewModel.favorites.collectAsStateWithLifecycle()

    LazyColumn(
        //...
    )
}

I hope this is enough to get you started, otherwise please take a deeper look at the documentation I linked, there is good information provided there.