There should be a way to fetch eagerly

[lnhrdt]

My understanding is that all collections in Exposed are fetched lazily using the SizedIterable class. @Tapac has shared some of his reasoning (preventing out of memory errors) here https://github.com/JetBrains/Exposed/issues/131#issuecomment-315642092 and https://github.com/JetBrains/Exposed/issues/158#issuecomment-328367447.

The suggested solution for eager fetching seems to be calling .toList() on the result of a query, before returning from the transaction. However this doesn't solve the whole problem. Referrers would also need to be transformed with .toList(). For example:

object ParentTable : LongIdTable("parents") {
    val name = varchar("name", 255)
}

class ParentEntity(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<ParentEntity>(ParentTable)

    var name by ParentTable.name
    val children by ChildEntity referrersOn ChildTable.parent
}

object ChildTable : LongIdTable("children") {
    val name = varchar("name", 255)
    val parent = reference("parent_id", ParentTable)
}

class ChildEntity(id: EntityID<Long>) : LongEntity(id) {
    companion object : LongEntityClass<ChildEntity>(ChildTable)

    var name by ChildTable.name
    var parent by ChildTable.parent
}

fun main(args: Array<String>) {
    val parents = transaction { ParentEntity.all().toList() }

    parents.map { it.children } // java.lang.IllegalStateException: No transaction in context.
}

Sure, one could call .toList() on each children of each parent but this starts to become cumbersome. In my case where I'm building things generically, it's become a showstopper.

Is there a way to require eager fetching, perhaps at the transaction level? If not, @Tapac do you have any ideas for what the design could look like? Are you open to supporting this type of use case?

[Tapac]

There might be side-effects when you modify data in a database and expect to see changes in a reference field, but while it was preloaded you will see a previous state.

Could you give a try to such code and write your feedback:

class EagerReferrers<Parent: Entity<Int>, Child: Entity<Int>>(ref: Referrers<Int, Parent, Int, Child>, entity: Parent){
    private val cachedReferrers = if (ref.cache) ref else Referrers(ref.reference, ref.factory, true)
    init {
        cachedReferrers.getValue(entity, EagerReferrers<*,*>::cachedReferrers).toList()
    }

    operator fun getValue(o: Parent, desc: KProperty<*>): List<Child> = cachedReferrers.getValue(o, desc).toList()
}

fun <Parent: Entity<Int>, Child: Entity<Int>> Referrers<Int, Parent, Int, Child>.eager(e: Parent) = EagerReferrers(this, e)

...

val children by ChildEntity.referrersOn(ChildTable.parent).eager(this)

[lnhrdt]

Interesting approach. I'm sure we can work out the kinks but right now I still get java.lang.IllegalStateException: No transaction in context. but from the getValue method in EagerReferrers.

The side-effects you mentioned are important, however to me they're only a concern while still operating within a transaction. Alternatively, is it possible to keep referrers lazy until returning from the outermost transaction, at which point ensuring they're all fetched?

[Tapac]

Sorry, I forgot to add cache:D

    class EagerReferrers<Parent: Entity<Int>, Child: Entity<Int>>(ref: Referrers<Int, Parent, Int, Child>, entity: Parent) {
        private val cachedReferrers = if (ref.cache) ref else Referrers(ref.reference, ref.factory, true)

        private var cachedValue by Delegates.notNull<List<Child>>()

        init {
            getValue(entity, EagerReferrers<*, *>::cachedReferrers)
        }

        operator fun getValue(o: Parent, desc: KProperty<*>): List<Child> {
            return if (TransactionManager.currentOrNull() == null) 
                cachedValue
            else {
                cachedReferrers.getValue(o, desc).toList().apply { 
                    cachedValue = this
                }
            }
        }
    }
}

[chrisjenx]

Would be nice if something more permeant made it into the API? If that's planned. I'm not sure how other DAO's handle this?

[Gabrinthei]

I too would really appreciate the ability to fetch references eagerly (both Reference and Referrers, optional cases as well). I'm fine defaulting fetching to lazy, but I think whoever is using the API should be able to decide if and when they need it to be eager, accepting the fact that there's a possibility of making the wrong decision which could cause an OOM exception.

[glasser]

toList also works poorly if you have dates: you get the same java.lang.IllegalStateException: No transaction in context. exception from DateColumnType.valueFromDB when it checks currentDialect.

[v79]

I'm struggling to understand how I'm supposed to work with Exposed in these scenarios. Given a DAO class

class PageTemplates(id: EntityID<Long>) : LongEntity(id) {
    var refName by PAGE_TEMPLATE.refName
    val fields by InputFields optionalReferrersOn INPUT_FIELD.pageTemplate
}

What do I need to do to get a complete PageTemplates object, including a list of InputFields, detached from the database transaction and ready to send up to my service or web layer?

I've written a get() function:

fun get(refName: String): PageTemplates? {
  var template: PageTemplates? = null
  transaction(dbConnections.connect()) {
    addLogger(StdOutSqlLogger)
    val result = PAGE_TEMPLATE.select {
      PAGE_TEMPLATE.refName eq refName
    }.firstOrNull()
    if (result != null) {
     template = wrapRow(result)
    }
  return template
}

But the returned object does not contain anything in the fields list. I'm not even sure what Exposed expects me to expect? Do I need to write a separate function to get the fields, then join them together up at the service layer? Can I 'attach' the fields to the PageTemplate somehow in the transaction?

[sigma-libra]

Tacking on that the last scenario also still doesn't work for many-to-many relations using via.

[Tapac]

@SabrinaKall , Please check new keepLoadedReferencesOutOfTransaction config option from the last release (few docs here)