Many-To-Many with extra column

[1fexd]

Hello,

how does one use this framework and the DAO to create a Many-To-Many table with an extra column, which can be accessed from the Entity class referencing the many-to-many table? Take the code from here for example, if I were to add another column to StarWarsFilmActors, how do I access it in StarWarsFilm? Because there I can only access Actor, but not StarWarsFilmActors.

I have found a similar issue (#180) which uses a hacky approach with an Entity, but this entity requires the table to have an Id, which is not a wise database-design decision, since the primary key of many-to-many relation tables has to consist of the two referenced tables, and an Id would make it possible to insert all kinds of invalid data into it.

[forntoh]

I have the same problem

[pqt]

I was just about to post a new issue but that seems unnecessary, I'd love to know if anyone has solved this problem gracefully in their codebase.

[pboulch]

Have you found a solution to this issue ?

[rhdunn]

The problem here is that InnerTableLink requires that the source and target are entity ID tables which isn't the case for a linking table with two (or more) foreign keys as the primary key.

Additionally, there are two cases w.r.t. the target object:

1. it is an entity referenced by the target id -- this is the case supported by InnerTableLink;
2. it is an object that contains the entity referenced by the target id and some additional columns -- this is the case described in this issue.

Ideally, InnerTableLink should support the two target types (entity and wrapped entity). In the case of a wrapped entity that entity/entity class would reference the wrapped entity/entity class instead of being an instance of that type. I.e. something like (using the table structure from #180):

object Songs : IntIdTable() {
    val name = varchar("name", 64)
}
object Artists : IntIdTable() {
    val name = varchar("name", 20)
}
object ArtistSongs : Table() {
    val songID = reference("songID", Songs)
    val artistID = reference("artistID", Artists)
    val role = integer("role")
}
class ArtistSong(val song: Song) : WrappedEntity<Song>(song) {
    companion object : WrappedEntityClass<Song, ArtistSong>(ArtistSongs)

    val name by song::name
    val role by ArtistSongs.role
}
class SongArtist(val artist: Artist) : WrappedEntity<Artist>(artist) {
    companion object : WrappedEntityClass<Artist, ArtistSong>(ArtistSongs)

    val name by artist::name
    val role by ArtistSongs.role
}
class Song(id: EntityID<Int>) : IntEntity(id) {
    companion object : IntEntityClass<Song>(Songs)

    var artists by SongArtist via ArtistSongs // wrapped
    var name by Songs.name
}
class Artist(id: EntityID<Int>) : IntEntity(id){
    companion object : IntEntityClass<Artist>(Artists)

    var name by Artists.name
    var songs by ArtistSong via ArtistSongs // wrapped
}

val artist = Artist.new { "Leonard Cohen" }
val song = Song.new { "Hallelujah" }
artist.songs = SizedCollection(ArtistSong.new(song) { role = 1 })
print(song.artists.forEach { artist -> "${artist.name} (${artist.role})" })

[rhdunn]

As a work around you will need to manually write the wrapping entity class logic and the linking logic (replicating parts of InnerTableLink). This doesn't interact with the entity cache as 1) a lot of the methods are internal; and 2) are dependent on the target class being an Entity.

So far, this is what I have:

object BookTitles : Table("book_titles_link") {
    val book = reference("book", Books, onDelete = ReferenceOption.CASCADE)
    val title = reference("title", Titles, onDelete = ReferenceOption.CASCADE)
    val isMainTitle = bool("is_main_title")

    override val primaryKey: PrimaryKey = PrimaryKey(book, title)
}

data class BookTitle(val title: Title, val isMainTitle: Boolean) {
    val name: String get() = title.name
    val sortName: String get() = title.sortName

    override fun toString(): String = name

    companion object {
        val dependsOnTables: ColumnSet = Books.innerJoin(BookTitles).innerJoin(Titles)

        fun wrapRow(row: ResultRow): BookTitle = BookTitle(
            title = Title.wrapRow(row),
            isMainTitle = row[BookTitles.isMainTitle]
        )

        fun wrapRows(rows: SizedIterable<ResultRow>): SizedIterable<BookTitle> = rows mapLazy {
            wrapRow(it)
        }
    }
}

class BookTitleLink : ReadWriteProperty<Book, SizedIterable<BookTitle>> {
    private var cachedValue: SizedIterable<BookTitle>? = null

    override fun getValue(thisRef: Book, property: KProperty<*>): SizedIterable<BookTitle> {
        if (cachedValue != null) return cachedValue!!
        val query = BookTitle.dependsOnTables.selectAll().where { Books.id eq thisRef.id }
        cachedValue = query.run { BookTitle.wrapRows(this) }
        return cachedValue!!
    }

    override fun setValue(thisRef: Book, property: KProperty<*>, value: SizedIterable<BookTitle>) {
        val existingIds = getValue(thisRef, property).map { it.title.id }.toSet()
        val targetIds = value.map { it.title.id }
        BookTitles.deleteWhere { (book eq thisRef.id) and (title notInList targetIds) }
        BookTitles.batchInsert(value.filter { !existingIds.contains(it.title.id) }, shouldReturnGeneratedValues = false) { target ->
            this[BookTitles.book] = thisRef.id
            this[BookTitles.title] = target.title.id
            this[BookTitles.isMainTitle] = target.isMainTitle
        }
        cachedValue = null
    }
}

object Books : LongIdTable("books", "id")

class Book(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<Book>(Books)

    var titles by BookTitleLink()
}

book.titles = SizedCollection(BookTitle(title, isMainTitle = true))
book.titles.forEach { title -> println("${title.name} main-title=${title.isMainTitle}") }