Inner join in exposed SQL

Question

I have table which has trains current location and another table which station details

object Train:Table("Train"){
    val numb = integer("no")
    val name= varchar("name", length = 40)
    val cStnCode =varchar("cSC", length = 10).references(Stations.code)
    val delay = integer("dly")
    val srcCode = varchar("sC", length = 10).references(Stations.code)
    val destCode = varchar("dC", length = 10).references(Stations.code)
     val startDay = varchar("sD", length = 12)
     override val primaryKey = PrimaryKey(numb, startDay)
}
object Stations:Table("Stations"){
    val name= varchar("name", length = 50)
    val code= varchar("code", length = 20)
    override val primaryKey = PrimaryKey(code)
}

How could I get station name corresponding to code. query i wrote is giving error: there is multiple primary key - foreign key references.

db.dbQuery {
            (Train innerJoin Stations)
                .select {
                (Train.cStnCode eq Stations.code)
                        (Train.srcCode eq Stations.code) and
                        (Train.destCode eq Stations.code)
            }
            .map {row->
                TrainClass(
                no = row[Train.numb],
                na = row[Train.name],
                cStC = row[Train.cStnCode],
                cStN = row[Stations.name], // Station name from join
                dly = row[Train.delay],
                sC = row[Train.srcCode],
                sN = "row[Stations.name]", // Station name from join
                dC = row[Train.destCode],
                dN = "row[Stations.name]", // Station name from join
}
}

If i am removing references then I get only one station name, where refernce is. How can i get all three station names from Station table

Answer 1

You can use Aliases so that each join treats stations independently

• Define alias for stations for each reference in train
• Join train with alias based on foreign key relationship
• retrieve all relevent fields from train and each aliased stations

for each row in the result [we can put it in an instance of trainclass]

import org.jetbrains.exposed.sql.*
import org.jetbrains.exposed.sql.transactions.transaction

object Train : Table("Train") {
    val numb = integer("no")
    val name = varchar("name", length = 40)
    val cStnCode = varchar("cSC", length = 10).references(Stations.code)
    val delay = integer("dly")
    val srcCode = varchar("sC", length = 10).references(Stations.code)
    val destCode = varchar("dC", length = 10).references(Stations.code)
    val startDay = varchar("sD", length = 12)
    override val primaryKey = PrimaryKey(numb, startDay)
}

object Stations : Table("Stations") {
    val name = varchar("name", length = 50)
    val code = varchar("code", length = 20)
    override val primaryKey = PrimaryKey(code)
}

// Data for train details
data class TrainClass(
    val no: Int,
    val na: String,
    val cStC: String,
    val cStN: String,
    val dly: Int,
    val sC: String,
    val sN: String,
    val dC: String,
    val dN: String
)

fun fetchTrainsWithStations() {
    transaction {
        // Alias for each foreign key relationship
        val currentStation = Stations.alias("currentStation")
        val sourceStation = Stations.alias("sourceStation")
        val destinationStation = Stations.alias("destinationStation")

        val query = Train.join(currentStation, JoinType.INNER, additionalConstraint = { Train.cStnCode eq currentStation[Stations.code] })
            .join(sourceStation, JoinType.INNER, additionalConstraint = { Train.srcCode eq sourceStation[Stations.code] })
            .join(destinationStation, JoinType.INNER, additionalConstraint = { Train.destCode eq destinationStation[Stations.code] })
            .selectAll()

        val result = query.map { row ->
            TrainClass(
                no = row[Train.numb],
                na = row[Train.name],
                cStC = row[Train.cStnCode],
                cStN = row[currentStation[Stations.name]], // Current station name from join
                dly = row[Train.delay],
                sC = row[Train.srcCode],
                sN = row[sourceStation[Stations.name]], // Source station name from join
                dC = row[Train.destCode],
                dN = row[destinationStation[Stations.name]] // Destination station name from join
            )
        }
        println(result)
    }
}