Exposed: set many-to-many relation using a DAO

[niond]

I searched for an answer online but wasn't able to find one.
I created my tables according to this issue.
I want to store albums and artists. They should be related by a many-to-many relation.
These are my tables:

object Artists : UUIDTable() {
    val name = varchar("name", 256).uniqueIndex()
    val created = datetime("created")
    val updated = datetime("updated")
}

class Artist(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<Artist>(Artists)

    var name by Artists.name
    var created by Artists.created
    var updated by Artists.updated

    val albums by Album via AlbumArtists
    val tracks by Track referrersOn Tracks.artist
}

object Albums : UUIDTable() {
    val name = varchar("name", 256)
    val created = datetime("created")
    val updated = datetime("updated")
}

class Album(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<Album>(Albums)

    var name by Albums.name
    var created by Albums.created
    var updated by Albums.updated

    val artists by Artist via AlbumArtists
    val tracks by Track referrersOn Tracks.album
}

object AlbumArtists : Table() {
    val artist = reference("artist", Artists).primaryKey(0)
    val album = reference("album", Albums).primaryKey(1)
}

While filling the database i couldn't figure out how to set the many-to-many relation. I tried it this way but it didn't work:

Album.new {
            name = album
            created = DateTime.now()
            updated = DateTime.now()
            artists = artistsOfAlbum
        }

Additionally i took a look at methods of Album.artists and couldn't find anything like add.
Am i supposed to create a class AlbumArtist and set the relation with AlbumArtist.new?
I'll gladly update the wiki with examples for this scenario as soon as i know how to do it.

[Tapac]

Try this:

1. Replace val with var:
   var artists by Artist via AlbumArtists

1. Wrap your collection with SizedCollection:
   artists = SizedCollection(artistsOfAlbum)

There are no add/remove/other collections functions at the moment. I guess it's possible to extend InnerTableLink class with other operators like plus/plusAssign/etc. I created the issue for this (https://github.com/JetBrains/Exposed/issues/348)

P.S.: Will wait for wiki improvements from you! :D

[niond]

Thanks for the quick reply.
it did work but i had to do an additional change.
Setting the value in Album.new resulted in a NPE:

val trackAlbum = transaction {
        Album.new {
            name = album
            created = DateTime.now()
            updated = DateTime.now()
            artists = SizedCollection(artistsOfAlbum)
        }
    }

Here's the corresponding stack trace:

kotlin.KotlinNullPointerException: null
    at org.jetbrains.exposed.dao.EntityID.getValue(Entity.kt:25)
    at org.jetbrains.exposed.sql.EntityIDColumnType.nonNullValueToString(ColumnType.kt:98)
    at org.jetbrains.exposed.sql.IColumnType$DefaultImpls.valueToString(ColumnType.kt:42)
    at org.jetbrains.exposed.sql.ColumnType.valueToString(ColumnType.kt:59)
    at org.jetbrains.exposed.sql.QueryBuilder.registerArguments(Expression.kt:17)
    at org.jetbrains.exposed.sql.QueryBuilder.registerArgument(Expression.kt:14)
    at org.jetbrains.exposed.sql.QueryParameter.toSQL(Op.kt:72)
    at org.jetbrains.exposed.sql.ComparisonOp.toSQL(Op.kt:101)
    at org.jetbrains.exposed.sql.AndOp.toSQL(Op.kt:122)
    at org.jetbrains.exposed.sql.statements.DeleteStatement.arguments(DeleteStatement.kt:18)
    at org.jetbrains.exposed.sql.statements.Statement.executeIn$exposed(Statement.kt:32)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:129)
    at org.jetbrains.exposed.sql.Transaction.exec(Transaction.kt:123)
    at org.jetbrains.exposed.sql.statements.Statement.execute(Statement.kt:29)
    at org.jetbrains.exposed.sql.statements.DeleteStatement$Companion.where(DeleteStatement.kt:24)
    at org.jetbrains.exposed.sql.QueriesKt.deleteWhere(Queries.kt:28)
    at org.jetbrains.exposed.sql.QueriesKt.deleteWhere$default(Queries.kt:27)
    at org.jetbrains.exposed.dao.InnerTableLink.setValue(Entity.kt:159)
    at ch.niond.streamz.db.Album.setArtists(Database.kt)
    at ch.niond.streamz.audio.ScanModuleKt$createOrGetAlbum$trackAlbum$1$3.invoke(ScanModule.kt:83)
    at ch.niond.streamz.audio.ScanModuleKt$createOrGetAlbum$trackAlbum$1$3.invoke(ScanModule.kt)
    at org.jetbrains.exposed.dao.EntityClass.new(Entity.kt:650)
    at org.jetbrains.exposed.dao.EntityClass.new(Entity.kt:630)
    at ch.niond.streamz.audio.ScanModuleKt$createOrGetAlbum$trackAlbum$1.invoke(ScanModule.kt:79)
    at ch.niond.streamz.audio.ScanModuleKt$createOrGetAlbum$trackAlbum$1.invoke(ScanModule.kt)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.inTopLevelTransaction(ThreadLocalTransactionManager.kt:102)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:73)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction(ThreadLocalTransactionManager.kt:56)
    at org.jetbrains.exposed.sql.transactions.ThreadLocalTransactionManagerKt.transaction$default(ThreadLocalTransactionManager.kt:56)

I had to split the creation and setting of the relation into two transactions:

  val trackAlbum = transaction {
       Album.new {
            name = album
            created = DateTime.now()
            updated = DateTime.now()
        }
    }
    transaction {
        trackAlbum.artists = SizedCollection(artistsOfAlbum)
    }

I'm not sure if this behavior is intended.

[Tapac]

This is surely a bug, but it can't be fixed in a simple way. The problem is that at the moment Exposed trying to insert a reference to not-initialized entity. It should be replaced with scheduled insert, but that's not so easy:D

[niond]

Figured out how to make it work in a single transaction when using UUIDTables.
I had to set the UUID myself while creating the DAO:

Album.new (UUID.randomUUID()){
            name = album
            created = DateTime.now()
            updated = DateTime.now()
            artists = SizedCollection(artistsOfAlbum)
        }

Just useful for UUIDTables but a good workaround for now.
Will add this to the wiki.

[RDCynthia]

I have another queston for this statement:

artists = SizedCollection(artistsOfAlbum)

It realy work for save relatonship via manyTomany. But it will delete other relationship!!! Why?

[Tapac]

@RDCynthia , because you don't add relation but re-set whole relations.

[tonyshkurenko]

@Tapac how to add new item to the collection without re-setting? I didn't find any example on how to do that. SizedIterable is read-only. One way I see: sql, but I'd like to do that with dao :)

[chemax]

I also want to know this .