I’m working with Room database in Android and have three data classes representing my tables:
data class Train(id: Int, isFollow: Boolean)
data class TrainStatus(id: Int, trainId: Int, origin: String, destination: String)
data class PreviousTrain(id: Int, trainStatusId: Int, previousTrainStatusId: Int)
In TrainStatus, trainId is a foreign key that references id from the Train class.
PreviousTrain is a junction table where trainStatusId and previousTrainStatusId are foreign keys referencing id from TrainStatus.
I want to query the database and get a response in the following format:
data class Response(
val train: Train,
val trainStatusWithPreviousTrain: List<TrainStatusWithPreviousTrain>
)
data class TrainStatusWithPreviousTrain(
val trainStatus: TrainStatus,
val previousTrain: TrainStatus
)
How can I use the junction table to create a Room query that will give me the data in the Response format?
In short you have a POJO with @Embedded for the parent and with @Relation for the child/children using the associateBy parameter with a Junction to specify the table and respective columns of the junction.
@Embedded will build the embedded object (if the relevant columns exist)@Relation without the associateBy parameter (assuming the correct parent and children columns) will extract ALL of the related children into a list of objects or a single object (first according to the order of the output)associateBy parameter with correctly coded specified parameters, will utilise the junction (associative/mapping/reference and other terms) table to extract the related objects.
@Embedded table. The entityColumn should be the column that maps/references the table with the related children.As a DEMO based upon your code (note Long rather than Int for the id's (as they should really be)) consider:-
The 3 tables i.e. @Entity annotated classes
@Entity
data class Train(
@PrimaryKey
val id: Long?=null,
val isFollow: Boolean
)
@Entity
data class TrainStatus(
@PrimaryKey
val id: Long?=null,
val trainId: Long,
val origin: String,
val destination: String
)
@Entity( indices = [
Index("trainStatusId","previousTrainStatusId")
])
data class PreviousTrain(
@PrimaryKey
val id: Long?=null,
val trainStatusId: Long,
val previousTrainStatusId: Long
)
A POJO for a TrainStatus with it's related Train:-
data class TrainStatusWithTrain(
@Embedded
val trainStatus: TrainStatus,
@Relation(
entity = Train::class,
parentColumn = "trainId",
entityColumn = "id"
)
val train: Train
)
The POJO for a TrainStatus (with it's Train) and it's Previous TrainStatus (with it's Train):-
data class TrainStatusWithPreviousTrainStatus(
@Embedded
val trainStatusWithTrain: TrainStatusWithTrain,
@Relation(
entity = TrainStatus::class,
parentColumn = "id",
entityColumn = "id",
associateBy = Junction(
PreviousTrain::class,
parentColumn = "trainStatusId",
entityColumn = "previousTrainStatusId"
)
)
val previousTrainStatus: List<TrainStatusWithTrain>
)
Some Insert and a Query in an @Dao interface (so the use can be demonstrated):-
@Dao
interface AllDAOs{
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(train: Train): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(trainStatus: TrainStatus): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(previousTrain: PreviousTrain): Long
@Transaction
@Query("SELECT * FROM trainStatus")
fun getAllTrainStatusesWithPreviousTrainStatuses(): List<TrainStatusWithPreviousTrainStatus>
}
A basic @Database annotated abstract class:-
@Database(entities = [Train::class,TrainStatus::class,PreviousTrain::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAOs(): AllDAOs
companion object {
private var instance: TheDatabase?=null
fun getInstance(context: Context): TheDatabase {
if (instance==null) {
instance = Room.databaseBuilder(
context, TheDatabase::class.java,"the_database.db"
)
.allowMainThreadQueries()
.build()
}
return instance as TheDatabase
}
}
}
Finally some activity code (not uses the main thread for brevity):-
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getAllDAOs()
/* Add Some Trains */
val T1Id = dao.insert(Train(isFollow = true))
val T2Id = dao.insert(Train(isFollow = false))
val T3Id = dao.insert(Train(isFollow = true))
/* and some TrainStatuses */
val TS1Id = dao.insert(TrainStatus(trainId = T1Id, origin = "Place1", destination = "PlaceA"))
val TS2Id = dao.insert(TrainStatus(trainId = T1Id, origin = "Place2", destination = "PlaceB"))
val TS3Id = dao.insert(TrainStatus(trainId = T2Id, origin = "Place3", destination = "PlaceC"))
val TS4Id = dao.insert(TrainStatus(trainId = TS3Id, origin = "Nowhere", destination = "Somewhere"))
/* and some PreviousTrains */
dao.insert(PreviousTrain(trainStatusId = TS1Id, previousTrainStatusId = TS2Id))
dao.insert(PreviousTrain(trainStatusId = TS2Id, previousTrainStatusId = TS3Id))
dao.insert(PreviousTrain(trainStatusId = TS3Id, previousTrainStatusId = TS1Id))
/* Extract from the database, writing to the log */
for (tswpts in dao.getAllTrainStatusesWithPreviousTrainStatuses()) {
val sb = StringBuilder()
for (pts in tswpts.previousTrainStatus) {
sb.append("\n\tPREV TS ID is ${pts.trainStatus.id} " +
"TrainsID is ${pts.trainStatus.trainId} " +
" ISFollow is ${pts.train.isFollow} " +
"ORIGIN is ${pts.trainStatus.origin} " +
"DEST is ${pts.trainStatus.destination}")
}
Log.d("DBINFO","TS ID is ${tswpts.trainStatusWithTrain.trainStatus.id} " +
"TRAIN ID is ${tswpts.trainStatusWithTrain.train.id} ISFollow is ${tswpts.trainStatusWithTrain.train.isFollow} " +
"ORIGIN is ${tswpts.trainStatusWithTrain.trainStatus.origin} " +
"DEST is ${tswpts.trainStatusWithTrain.trainStatus.destination} " +
"TS has ${tswpts.previousTrainStatus.size} PREVIOUS TRAINS STATUSES. They are ${sb}")
}
}
}
Result (after first run, subsequent runs not intended for the demo):-
D/DBINFO: TS ID is 1 TRAIN ID is 1 ISFollow is true ORIGIN is Place1 DEST is PlaceA TS has 1 PREVIOUS TRAINS STATUSES. They are
PREV TS ID is 2 TrainsID is 1 ISFollow is true ORIGIN is Place2 DEST is PlaceB
D/DBINFO: TS ID is 2 TRAIN ID is 1 ISFollow is true ORIGIN is Place2 DEST is PlaceB TS has 1 PREVIOUS TRAINS STATUSES. They are
PREV TS ID is 3 TrainsID is 2 ISFollow is false ORIGIN is Place3 DEST is PlaceC
D/DBINFO: TS ID is 3 TRAIN ID is 2 ISFollow is false ORIGIN is Place3 DEST is PlaceC TS has 1 PREVIOUS TRAINS STATUSES. They are
PREV TS ID is 1 TrainsID is 1 ISFollow is true ORIGIN is Place1 DEST is PlaceA
D/DBINFO: TS ID is 4 TRAIN ID is 3 ISFollow is true ORIGIN is Nowhere DEST is Somewhere TS has 0 PREVIOUS TRAINS STATUSES. They are
List<TrainStatus> could just be TrainStatus (with handling code modified to not expect a List) if there is only the one item.The Required Response
If understood correctly (and for the demo assuming all trains) then
Adding the POJO:-
data class TrainWithTrainStatus(
@Embedded
val train: Train,
@Relation(
entity = TrainStatus::class,
parentColumn = "id",
entityColumn = "trainId"
)
val trainStatusWithPreviousTrainStatus: List<TrainStatusWithPreviousTrainStatus>
)
And then introducing another function in the @Dao annotated interface:-
@Transaction
@Query("SELECT * FROM train")
fun getAllTrainsWithTrainStatusetc(): List<TrainWithTrainStatus>
And then adding the activity code:-
/* Train with TrainStatus etc */
for (twtse in dao.getAllTrainsWithTrainStatusetc()) {
val sb = StringBuilder()
for (twts in twtse.trainStatusWithPreviousTrainStatus) {
val sb2 = StringBuilder()
for (pt in twts.previousTrainStatus) {
sb2.append("\n\t\t PTTS ID is ${pt.trainStatus.id} " +
"PTTS ORIGIN is ${pt.trainStatus.origin} " +
"PTTS DEST is ${pt.trainStatus.destination}")
}
sb.append("\n\t TS ID is ${twts.trainStatusWithTrain.trainStatus.id} " +
"ORIGIN is ${twts.trainStatusWithTrain.trainStatus.origin} " +
"DEST is ${twts.trainStatusWithTrain.trainStatus.destination} " +
". There are ${twts.previousTrainStatus.size} PTTS's. They are ${sb2}")
}
Log.d("DBINFO","Train ID is ${twtse.train.id} ISFollow is ${twtse.train.isFollow} " +
". There are ${twtse.trainStatusWithPreviousTrainStatus.size} Trains Statuses With Previous Train Statuses. They are ${sb}")
}
Results in (additionally):-
D/DBINFO: Train ID is 1 ISFollow is true . There are 2 Trains Statuses With Previous Train Statuses. They are
TS ID is 1 ORIGIN is Place1 DEST is PlaceA . There are 1 PTTS's. They are
PTTS ID is 2 PTTS ORIGIN is Place2 PTTS DEST is PlaceB
TS ID is 2 ORIGIN is Place2 DEST is PlaceB . There are 1 PTTS's. They are
PTTS ID is 3 PTTS ORIGIN is Place3 PTTS DEST is PlaceC
D/DBINFO: Train ID is 2 ISFollow is false . There are 1 Trains Statuses With Previous Train Statuses. They are
TS ID is 3 ORIGIN is Place3 DEST is PlaceC . There are 1 PTTS's. They are
PTTS ID is 1 PTTS ORIGIN is Place1 PTTS DEST is PlaceA
D/DBINFO: Train ID is 3 ISFollow is true . There are 1 Trains Statuses With Previous Train Statuses. They are
TS ID is 4 ORIGIN is Nowhere DEST is Somewhere . There are 0 PTTS's. They are