Exposed: Eager loading

[damianbartos]

I have the following code.

class Author(id: EntityID<Int>): IntEntity(id){
    companion object : IntEntityClass<Author>(Authors)

    var firstName by Authors.firstName
    var lastName by Authors.lastName
    override fun toString(): String {
        return "Author($id, $firstName, $lastName)"
    }
}

object Authors: IntIdTable(){
    val firstName = varchar("firstName", 50)
    val lastName = varchar("lastName", 50)
}

class Book(id: EntityID<Int>): IntEntity(id){
    companion object : IntEntityClass<Book>(Books)

    var name by Books.name
    var authors by Author via BookAuthors
    override fun toString(): String {
        return "Book($id, $name, authors: [$authors])"
    }
}

object Books: IntIdTable(){
    val name = varchar("name", 50)
}

object BookAuthors: Table() {
    val book = reference("book", Books).primaryKey(0)
    val author = reference("author", Authors).primaryKey(1)
}

Studing documentation I can add new Book and Author

val book = transaction {
    Book.new {
        name = "test book"
    }
}
val author = transaction {
     Author.new {
        firstName = "FName"
        lastName = "LName"
    }
}

as well as create reference

transaction {
     book.authors = SizedCollection(listOf(author))
}

The problem I have is that after getting all books with eager loading, collection is not printed as expected

for (bookItem in Book.all().with(Book::authors)) {
    println(bookItem)
}

Reult Book(1, test book, authors: [org.jetbrains.exposed.sql.LazySizedCollection@7c56e013])

val book1 = Book.findById(1)?.load(Book::authors)
println(book1)
//same result 
//Book(1, test book, authors: [org.jetbrains.exposed.sql.LazySizedCollection@7c56e013])

println(book1?.authors)
//authors not printed 
//org.jetbrains.exposed.sql.LazySizedCollection@7c56e013

println(book1?.authors?.with(Book::authors))
//now it can print authors? 
//[Author(1, FName, LName)]

println(book1)
//still same issue 
//Book(1, test book, authors: [org.jetbrains.exposed.sql.LazySizedCollection@7c56e013])

Could you help with that? What might be the problem?
My assumption is that Book.findById(1)?.load(Book::authors) is not loading, am I correct?

Console debugg logs shows that authors are selected, but they are not populated in Book.all()
DEBUG Exposed - SELECT AUTHORS.ID, AUTHORS."firstName", AUTHORS."lastName", BOOKAUTHORS.AUTHOR, BOOKAUTHORS.BOOK FROM AUTHORS INNER JOIN BOOKAUTHORS ON BOOKAUTHORS.AUTHOR = AUTHORS.ID WHERE BOOKAUTHORS.BOOK IN (1, 2)

[Tapac]

I guess that Iterable and LazySizedCollection doesn't have default toString() implementation.

Could you check your code with: return "Book($id, $name, authors: [$authors.joinToString()])" ?

[damianbartos]

@Tapac Thanks for answer. Now everything is printing as expected :)

[damianbartos]

One more question. When code is executed from single transacion block everything is working, but with following example:

val books = transaction {
    Book.all().with(Book::authors).toList()
// authors are loaded here
}
//authors are not loaded here
println(books)

I have java.lang.IllegalStateException: No transaction in context. error
How I should return book list with filled(loaded) authors outside transaction block? Is it possible for entities with relations?

[CharlieTap]

All transformations that use the eager loaded relations have to be done inside the transaction closure, as relations are stored inside the cache for that specific transaction. This is just a limitation of the feature unfortunately. In short in your example above your println would need to go inside the transaction closure

[damianbartos]

What I actually want to achive is to return book list from call.respond like so

get("/book"){
        val books = transaction {
            Book.all().with(Book::authors).toList()
        }
        println(books)

        call.respond(ThymeleafContent("booksList", mapOf("books" to books)))
}

calling call.respond from transaction{} is not possible.
Should I copy whole list with authors data from transaction block to some variable (wchich can be visible outside transaction and then return that copy?

[CharlieTap]

Ultimately something is going to make a call into the relation at some point, in this case presumably the template engine. This will not work as its being performed outside the closure, you really have one choice which is transform your list of DAO's into another form inside the transaction closure and return that for use inside the template.

[damianbartos]

Thanks for answer, transforming to dao was that I was missing. Many thanks for help.
Solution snipped

data class BookDao(var name: Any, var authors: List<Author>)

get("/book"){
  var books = listOf<BookDao>()
  transaction {
     books = Book.all().with(Book::authors).map { BookDao(it.name, it.authors.toList()) }
   }
  println(books)
  call.respond(ThymeleafContent("booksList", mapOf("books" to books)))
}

[uuf6429]

All transformations that use the eager loaded relations have to be done inside the transaction closure, as relations are stored inside the cache for that specific transaction. This is just a limitation of the feature unfortunately. In short in your example above your println would need to go inside the transaction closure

@CharlieTap I have this same issue and unfortunately, it's not clear why this problem happens. If the object graph is loaded eagerly in a transaction, why is another (or same) transaction needed again later on? Also, perhaps the point of eager loading is not very clear. cc @Tapac