https://kotlinlang.org logo
#exposed
Title
# exposed
f

franek

10/08/2018, 8:18 PM
Does anyone know how to write something like andWhere if I use many to many reference?
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 ->}
t

tapac

10/09/2018, 7:44 AM
Why the first variant with
RadioTable.genreIds inList genreIds
doesn't work?
f

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) }
t

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)
f

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 }
t

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