sqlsqliteandroid-room

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:

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>
    

    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
    

    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 such each Airport (dep/dest) will be built by an independent underlying. query.