Creating composite data class from multiple Room/SQL queries (or one complex one)

I have two tables/entities:

@Entity(tableName = "airport", indices = [Index(value = ["iata_code"], unique = true)])
data class Airport(
    @PrimaryKey(autoGenerate = true) val id: Int,
    @ColumnInfo(name = "iata_code") val iataCode: String,
    @ColumnInfo(name = "name") val name: String,
    @ColumnInfo(name = "passengers") val passengers: Int
)
@Entity(tableName = "favorite")
data class Favorite(
    @PrimaryKey(autoGenerate = true) val id: Int,
    @ColumnInfo(name = "departure_iata_code") val departureCode: String,
    @ColumnInfo(name = "destination_iata_code") val destinationCode: String,
)

I need to generate a transformation from Favorite using details from Airport to produce this:

data class FavoriteRoute(
    val departure: Airport,
    Val destination: Airport
)

To do this, I want create a function that accepts a favorite: Favorite parameter, and:

selects rows from Airport table whose iata_code matches favorite.departure_iata_code (there should be only one)
selects rows from Airport table whose iata_code matches favorite.destination_iata_code (there should be only one)
returns a FavoriteRoute entity constructed from the results of the selects above (presumably a list of one)

I've tried using the @Relation operator, but it seems to be confused by the multiple references to the same column in Airport. I might have got the syntax wrong.

I've also tried doing this in my model using separate simple queries on the Airport table, but haven't managed to process a flow in my model.

It seems there should be a way to do this just with @Query? What would that look like?

Solution

I suspect that you haven't used @Relation correctly.

The following, albeit it including the parent Favorite, works:-

data class FavoriteRoute(
    @Embedded
    val favorite: Favorite,
    @Relation(entity = Airport::class, entityColumn = "iata_code", parentColumn = "departure_iata_code")
    val departure: Airport,
    @Relation(entity = Airport::class, entityColumn = "iata_code", parentColumn = "destination_iata_code")
    val destinatation: Airport
)

Demo

Using the above, your classes, DAO's for insertion of some data and the DAO:-

@Transaction
@Query("SELECT * FROM favorite")
fun getFavoriteRoute(): List<FavoriteRoute>

i.e. all FavoriteRoutes

An then the activity code:-

    db = TheDatabase.getInstance(this)
    dao = db.getAllDAOs()

    dao.insert(Airport(1,"iata_1","A1",100))
    dao.insert(Airport(2,"iata_2","A2",200))
    dao.insert(Airport(3,"iata_3","A3",300))
    dao.insert(Airport(4,"iata_4","A4",400))

    dao.insert(Favorite(100,"iata_3","iata_4"))
    dao.insert(Favorite(200,"iata_1","iata_2"))
    dao.insert(Favorite(300,"iata_2","iata_3"))

    val favs=dao.getFavoriteRoute()


    for (fr in dao.getFavoriteRoute()) {
        Log.d("DBINFO","FR ID is ${fr.favorite!!.id} FR_FROM is ${fr.favorite.departureCode} FR_TO is ${fr.favorite.destinationCode}" +
                "\n\tAP_FROM ID is ${fr.departure!!.id} AP_FROM CDE is ${fr.departure.iataCode} AP_FROM name is ${fr.departure.name} AP_FROM pass = ${fr.departure.passengers}" +
                "\n\tAP_TO ID is ${fr.destinatation!!.id} AP_TO CDE is ${fr.destinatation.iataCode} AP_TO name is ${fr.destinatation.name} AP_TO pass = ${fr.destinatation.passengers}")
    }
}

Results in the log including:-

D/DBINFO: FR ID is 100 FR_FROM is iata_3 FR_TO is iata_4
        AP_FROM ID is 3 AP_FROM CDE is iata_3 AP_FROM name is A3 AP_FROM pass = 300
        AP_TO ID is 4 AP_TO CDE is iata_4 AP_TO name is A4 AP_TO pass = 400
D/DBINFO: FR ID is 200 FR_FROM is iata_1 FR_TO is iata_2
        AP_FROM ID is 1 AP_FROM CDE is iata_1 AP_FROM name is A1 AP_FROM pass = 100
        AP_TO ID is 2 AP_TO CDE is iata_2 AP_TO name is A2 AP_TO pass = 200
D/DBINFO: FR ID is 300 FR_FROM is iata_2 FR_TO is iata_3
        AP_FROM ID is 2 AP_FROM CDE is iata_2 AP_FROM name is A2 AP_FROM pass = 200
        AP_TO ID is 3 AP_TO CDE is iata_3 AP_TO name is A3 AP_TO pass = 300

as expected (i.e. 3-4, 1-2 and 2-3)

You mention the issue of ambiguous names. Using @Relation (which requires the parent for the relationship to be built) then Room circumvents any such issue as it does not use a JOIN but instead, when extracting the data for the relation executes a query to get ALL the related data (as you say as the iata_code is unique then just the single related data).

As a single object is specified, as opposed to List, in the relationship, even if many rows were extracted, just 1 would be used.
- perhaps note the very simple query just accessing the Favorite, Room does the rest.
- saying that sometimes using @Relation and it's get ALL can be problematic (e.g. if trying to filter the related data).

As such each Airport (dep/dest) will be built by an independent underlying. query.