Does anyone know how to write something like andWh...

# exposed

[franek]

Does anyone know how to write something like andWhere if I use many to many reference?

Untitled.kt

[franek]

this working but is not on DB level 😞

RadioEntity.all().limit(limit, offset).filter { it.genres.any { it.id.value in genreIds } }.filter { it.name.contains(search) }.sortedBy { it.name }.map { row ->}

[tapac]

Why the first variant with RadioTable.genreIds inList genreIds doesn't work?

[franek]

hi @tapac first variant was working but then I change tables to relation MANY TO MANY and then I think I can not use first variant. So after reading doc I thought only way how work with IntEntity tables is variant 2. Sorry if i use it bad .( Can you help me?

[franek]

This is how my tables looks like now:

object RadioTable : IntIdTable() {
val isEnabled = bool("isEnabled")
val name = varchar("name", 100)
}

class RadioEntity(id: EntityID<Int>) : IntEntity(id) {
companion object : IntEntityClass<RadioEntity>(RadioTable)

var isEnabled by RadioTable.isEnabled
var name by RadioTable.name

var genres by GenreEntity via RadioGenresTable
}

object GenreTable : IntIdTable() {
val cover = text("cover")
}

class GenreEntity(id: EntityID<Int>): IntEntity(id) {
companion object : IntEntityClass<GenreEntity>(GenreTable)
var cover by GenreTable.cover
}

object RadioGenresTable : Table() {
val radio = reference("radio", RadioTable).primaryKey(0)
val genre = reference("genre", GenreTable).primaryKey(1)
}

[tapac]

Try this

RadioTable.innerJoin(RadioToGenresTable).select {
   (RadioTable.name like "%$search%")  
     and (RadioToGenresTable.genreId inList genreIds)
}.withDistinct().
orderBy(RadioTable.name, isAsc = true).
limit(limit, offset)

[franek]

thanks for help I'll try it after work

[franek]

@tapac Ok Its working .) My last question waht I need to do if I want select full genres object not only ID? The result shuld by object with array of genres example:

{
“id”: 2,
“name”: “ABCD”,
“genres”: [
{
“id”: 1,
“cover”: “B”,
},
{
“id”: 2,
“cover”: “BB”,
}
],
“enabled”: true
}

[tapac]

If you have some Genre data class then you can innerJoin Radio with Genres and then make groupBy Radio.id on kotlin side.

If you use Exposed DAO then read here https://github.com/JetBrains/Exposed/wiki/DAO#many-to-many-reference