Does anyone know how to write something like andWhere if I u kotlinlang #exposed

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

10/09/2018, 7:44 AM

Why the first variant with

RadioTable.genreIds inList genreIds

doesn't work?

franek

10/09/2018, 8:07 AM

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?

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

10/09/2018, 8:10 AM

Try this

Copy code

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

franek

10/09/2018, 8:16 AM

thanks for help I'll try it after work

👌 1

@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

10/10/2018, 2:40 PM

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

👍 1

52 Views