How do I search from multiple tables with DAO?

Question

Say I have tables like this:

object Leagues : IntIdTable() {
   val name = varchar("name", 50).uniqueIndex()
}
object Matches: IntIdTable() {
   val game = reference("game", Games)
}
object Users: IntIdTable() {
   val name = varchar("name", 50).uniqueIndex()
}
object Bets: IntIdTable() {
   val match = reference("match", Matches)
   val user = reference("user", Users)
}

Daos are in the lines of:

class Bet(id: EntityID<Int>) : IntEntity(id) {
   companion object : IntEntityClass<Bet>(Bets)

   var match by Bets.match
   var user by Bets.user
}

How do I write the dao or the query for the Bets class so I can query "give me all bets player X has made in league Y". Bet.find { (user eq X) and (/* what here to get the leagues table ? */) }

Answer 1

val bets = Bet.wrapRows(
    Bets.innerJoin(Matches).innerJoin(Leagues).select {
        Bets.user eq X.id and (Leagues.name eq "Y"  
    }
).toList()