I have these three tables in exposed with ktor. I am trying get data from all of these table where train number is equal to given number. And getting back data in JSON format.
object Schedule: Table("Schedule"){
val number = integer("numb")
.references(Trains.number)
val stnCode = varchar("stnCode", length = 50)
.references(Stations.code)
val stop = bool("stop")
val arrTime = integer("arrTime")
val depTime = integer("depTime")
val dayNum = integer("dayNum")
val km = float("km")
val updatedOn= integer("updatedOn")
override val primaryKey: PrimaryKey = PrimaryKey(number, stnCode, updatedOn)
}
object Trains:Table("Trains"){
val number= integer("num")
val name= varchar("name", length = 50)
val type=integer("type")
val srCode= varchar("srCode", length = 10)
.references(Stations.code)
val srcName= varchar("srcName", length = 30)
val destCode= varchar("destCode", length = 10)
.references(Stations.code)
val destName= varchar("destName", length = 30)
val runDays= integer("runDays")
val classes= varchar("classes", length = 100)
val totalDist= float("totalDist")
val journeyTime= integer("jourTime")
val updatedOn= integer("updatedOn")
override val primaryKey = PrimaryKey(number)
}
object Stations:Table("Stations"){
val name= varchar("name", length = 50)
val code= varchar("code", length = 20)
val lat =varchar("lat", length = 20)
val lng =varchar("lng", length = 20)
val type= integer("type")
val stateName = varchar("stateName", length = 20)
val stateCode= varchar("stateCode", length = 10)
val updatedOn= integer("updatedOn")
override val primaryKey = PrimaryKey(code)
}
How to write result row function to get all the data from these table in this data class?
@Serializable
data class TrainDetails(
val no: Int,//number
val na: String,//name
val typ: Int,//type of train
val srC: String,//source station code
val srNa: String,//source station name
val desC: String,//destination Station Code
val desN: String,//destination Station Name
val rDs: Int,//run days
val cl: String,//classes
val jT:Int,
val dist: Float,
val updOn: Int,//updated on time
//Schedule
val stops:List<Stops>
)
@Serializable
data class Stops(
val no: Int,//train number
val stNa: String,//station name
val stC: String,//station code
val isS: Boolean,//is stop
val aT: Int,//arrival time
val dT: Int,//departure time
val dyNo: Int,//day number
val km: Float,//kilometers
val lt: String,// lat
val lg: String,//long
val stateN: String,//state name
val stateC: String,//state code
val typ: Int,//station type
val updOn: Int//updated on
)
db.dbQuery {
(Trains innerJoin Schedule )
.select((Trains.number eq train) and
(Schedule.number eq train)
).map(::resultRowToTrainSchedule)}
I tried but it is giving me List of TrainDetails. I want trainDetails and list of stops.
how could I achieve it.