How to join two tables on two fields using Exposed?

Question

I have the following database Tables:

// CurrenciesTable
object CurrenciesTable : Table("currencies") {
    val symbol = varchar("symbol", 48)
    val name = varchar("name", 48)
    override val primaryKey = PrimaryKey(symbol)
}


// OrdersTable
object OrdersTable : IntIdTable("orders") {
    val baseCurrency = varchar("base_currency", 48)
    val counterCurrency = varchar("counter_currency", 48)
    val price = decimal("price", DECIMAL_PRECISION, DECIMAL_SCALE)
    val createdAtEpochSecond = long("created_at_epoch_second")
}

In the OrdersTable, I have to fields which reference CurrenciesTable:

baseCurrency
counterCurrency

I want to select records from OrdersTable and join them with CurrenciesTable on two fields. So I get the symbol and name for each currency.

Here is my DSL query to join on baseCurrency field only.

// Exposed DSL
OrdersTable.join(CurrenciesTable, JoinType.INNER, OrdersTable.baseCurrency, CurrenciesTable.symbol)
            .selectAll()
            .forEach {
                // Getting OrdersTable record data
                it[OrdersTable.id].value
                it[OrdersTable.price]
                it[OrdersTable.createdAtEpochSecond]

                // Getting CurrenciesTable record data (for baseCurrency only)
                it[CurrenciesTable.symbol]
                it[CurrenciesTable.name]
            }

I tried to do a second join as follows:

// Exposed DSL
OrdersTable.join(CurrenciesTable, JoinType.INNER, OrdersTable.baseCurrency, CurrenciesTable.symbol)
           .join(CurrenciesTable, JoinType.INNER, OrdersTable.counterCurrency, CurrenciesTable.symbol)

However, I get the following exception.

Caused by: java.sql.SQLSyntaxErrorException: Not unique table/alias: 'currencies'

Answer 1

Try to add alias for CurrenciesTable:

OrdersTable.innerJoin(CurrenciesTable.alias("baseCurrency"), { OrdersTable.baseCurrency }, { CurrenciesTable.symbol })
           .innerJoin(CurrenciesTable, { OrdersTable.counterCurrency }, { CurrenciesTable.symbol })