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:
Airport
table whose iata_code
matches favorite.departure_iata_code
(there should be only one)Airport
table whose iata_code
matches favorite.destination_iata_code
(there should be only one)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?
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).
@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.