set many-to-many relation using a DAO

[bitshift42]

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

2. 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 (#348)

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

[bitshift42]

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

[bitshift42]

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.

[Noah-RD]

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.

[antonshkurenko]

@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 .

[idrisadetunmbi]

By resetting as stated by @Tapac, can confirm that Exposed handles unique records of related entities in the join table so you can just update related entities by reassigning a SizedIterable of the related entities. Not sure what happens if you have other columns outside of the related entity ids on the join table though.