android room Database relationships kotlin

Question

I have three tables trainList, Schedule, Stations

@Entity("TrainList")
data class TrainTable(
    @PrimaryKey
    val number:String="",
    val name:String="",
    val sourceName:String="",
    val sourceCode:String="",
    val destinationName:String="",
    val destinationCode:String=""
)
@Entity("Schedule", primaryKeys = ["stationCode","trainNumber"])
data class Sched`your text`uleTable(
    val trainNumber:String="",
    val stationCode:String="",
    val arrivalTime:Int=0,
    val departureTime:Int=0,
)
@Entity("Stations")
data class StationTable(
    @PrimaryKey
    val stationCode:String="",
    val stationName:String="",
    val lat:Double=0.0,
    val lng:Double=0.0
)

how can i link TrainTable to ScheduleTable and schedule to stations, so that when ever i query train Schedule i should get train details from train table and schedule from schedule table and station details corresponding to that station from schedule table.

tables are as follow

Train Table

number	name	SourceName	SourceCode	destName	destCode
12232	XYZC	ABCD	ABC	EFGA	efg
13005	QWER	XYZQ	xyz	DCBA	dca

Schedule Table

trainNumber	stationCode	arrivalTime	departureTime
12232	abc	1510	1510
12232	bce	1540	1545
12232	cde	1610	1640
13005	def	1705	1710
13005	efg	1805	1810

one train could have multiple stops i.e. this table has two primary keys

Station Table

stationCode	stationName	lat	lng
abc	ABCD	15.10	1.510
bcd	BCDE	15.40	15.45
efg	CDEF	16.10	16.40
dca	DEFG	17.05	17.10
xyz	EFGH	18.05	18.10

lets say i query train schedule of 12232

I should get train details from train table i.e. number, name, sourceName, sourceCode and so on, and all the stops of that train from the schedule table and station details corresponding to that stop.

12232 XYZCD ABCD abc(abc, ABCD, 15.10, 1.510) EFGH efg( efg, CDEF, 16.10,16.40) stops abc(abc, ABCD, 15.10, 1.510) 1510 1510 efg( efg, CDEF, 16.10,16.40) 1510 1510 efg( efg, CDEF, 16.10,16.40) 1510 1510 efg( efg, CDEF, 16.10,16.40) 1510 1510

I tried to link them but able to get required output

With this relationships

data class TrainWithScheduleAndStations
    (
    @Embedded val train: TrainTable= TrainTable(),
    @Relation(

        parentColumn = "number",
        entityColumn = "trainNumber"
    )
    val schedules: List<ScheduleTable> = emptyList(),
    @Relation(
        parentColumn = "sourceCode",
        entityColumn = "stationCode"
    )
    val fromStation: StationTable = StationTable(),
    @Relation(
        parentColumn = "DestinationCode",
        entityColumn = "stationCode"
    )
    val toStation: StationTable = StationTable()
)

i get this answer 12232 XYZCD ABCD abc(abc, ABCD, 15.10, 1.510) EFGH efg( efg, CDEF, 16.10,16.40) stops abc 1510 1510 efg 1510 1510 efg 1510 1510 efg 1510 1510

But required is this 12232 XYZCD ABCD abc(abc, ABCD, 15.10, 1.510) EFGH efg( efg, CDEF, 16.10,16.40) stops abc(abc, ABCD, 15.10, 1.510) 1510 1510 efg( efg, CDEF, 16.10,16.40) 1510 1510 efg( efg, CDEF, 16.10,16.40) 1510 1510 efg( efg, CDEF, 16.10,16.40) 1510 1510

I am not able to link Station table

Answer 1

You need to consider the hierarchy. That is a schedule will have it's related station. So something like:-

data class ScheduleWithStation(
    @Embedded
    val schedule: ScheduleTable,
    @Relation(
        entity = StationTable::class, parentColumn = "stationCode", entityColumn = "stationCode"
    )
    val station: StationTable
)

To incoporate this so that you get A Train with it's List of schedules AND with the schedules station then you can incorporate the above to use something like:-

data class TrainSourceAndDestAndWithScheduleWithStations(
    @Embedded
    val train: TrainTable,
    @Relation(
        entity = StationTable::class, parentColumn = "sourceCode", entityColumn = "stationCode"
    )
    val fromStation: StationTable,
    @Relation(
        entity = StationTable::class, parentColumn = "destinationCode", entityColumn = "stationCode"
    )
    val toStation: StationTable,
    @Relation(
        entity = ScheduleTable::class, parentColumn = "number", entityColumn = "trainNumber"
    )
    val schedulesWithStations: List<ScheduleWithStation>
)

This would then produce, for example, :-

TrainName=XYZC Number=12232 
    
        FromStation=ABCD Code=abc Lat=15.1 Lng=1.51
        To  Station=EFGH Code=efg Lat=18.05 Lng=18.1
        There are 3 Schedules. They are:-
            Arrival=1510 Depart=1510 Train#=12232 StationName= ABCD Code=abc Lat=15.1 Lng=1.51
            Arrival=1540 Depart=1545 Train#=12232 StationName= BCDE Code=bcd Lat=15.4 Lng=15.45
            Arrival=1610 Depart=1640 Train#=12232 StationName= CDEF Code=cde Lat=16.1 Lng=16.4




TrainName=QWER Number=13005 
    
        FromStation=XYZI Code=xyz Lat=18.05 Lng=18.1
        To  Station=DCAJ Code=dca Lat=18.05 Lng=18.1
        There are 2 Schedules. They are:-
            Arrival=1705 Depart=1710 Train#=13005 StationName= DEFG Code=def Lat=17.05 Lng=17.1
            Arrival=1805 Depart=1810 Train#=13005 StationName= EFGH Code=efg Lat=18.05 Lng=18.1

• note results are based upon the data from the question (but with some changes to circumvent referential integrity issues with the data in your question e.g. bcd/bce)