MySQL join tables with Kotlin Exposed error: "no matching primary/foreign key pair"

Question

I am working with a legacy MySQL database in Kotlin using the Exposed library. I've got it working fine with MySQL (simple queries work as expected). I'm running MySQL version 5.7.26, but I don't think the problem is related to MySQL itself.

I have two tables, events and event_years. The relevant columns in events are the id (int, primary key), and name (varchar 255). Event_years contains an id (int, primary key), year (datetime), and event_id (int, foreign key), among other things that are not relevant to this question.

An event may have zero or more rows in event_years referring to it. I would like to select the event name, and year, and order the results by year.

I was able to achieve this using the mysql CLI like this:

SELECT e.id, e.name, y.id, y.date 
FROM events e, event_years y
WHERE e.id = y.event_id
ORDER BY y.date;

In Kotlin, I have created objects for Events and EventYears:

object Events : Table("events") {
    val id = integer("id").autoIncrement().primaryKey()
    val name = varchar("name", length = 255)
}

object EventYears : Table("event_years") {
    val id = integer("id").autoIncrement().primaryKey()
    val eventId = integer("event_id").uniqueIndex()
    val date = date("date")
}

I have then tried the following query:

val res = EventYears.innerJoin(Events)
    .slice(Events.name, Events.id, EventYears.date, EventYears.id)
    .select { EventYears.eventId eq Events.id }
    .groupBy(EventYears.date)

I expected the result to be an Iterable object containing these values (like the ones I've received when doing queries without joining), but an exception was raised:

java.lang.IllegalStateException: Cannot join with foo.bar.Events@b307e119 as there is no matching primary key/ foreign key pair and constraint missing

Thank you in advance for any help. I've read the Exposed documentation twice now and still can not understand why this is not working.

Answer 1

You should define your eventId as val eventId = reference("film", Events).uniqueIndex() then you'll be possible to use your query without an exception.

The other way is to provide both columns explicitly in innerJoin

val res = EventYears.innerJoin(Events, {EventYears.eventId}, {Events.id})
    .slice(Events.name, Events.id, EventYears.date, EventYears.id)
    .selectAll()
    .groupBy(EventYears.date)