Eager fetching, collections, and detaching objects from the transaction

[v79]

I think I might be about to give up on Exposed and scurry back to Hibernate. I'm trying to fetch a complete object graph but without eager fetching of collections, I don't know how to do it. I'm assuming a three-layer separation of concerns - a web layer, a service layer, and a DB/DAO layer. My service layer wants to convert the DB/DAO objects (like PageTemplates below) into a viewable web layer model. Where should my transaction blocks be? In the DAO, or in the service layer?

Given this model:

object PAGE_TEMPLATE : LongIdTable() {
	val refName = varchar("ref_name", 255).uniqueIndex()
}
object INPUT_FIELD : LongIdTable() {
	val refName = varchar("ref_name", 255).uniqueIndex()
	val pageTemplate = optReference("page_template", PAGE_TEMPLATE)
}
class InputFields(id: EntityID<Long>) : LongEntity(id) {
	var refName by INPUT_FIELD.refName
	var pageTemplate by PageTemplates optionalReferencedOn INPUT_FIELD.pageTemplate
	companion object : LongEntityClass<InputFields>(INPUT_FIELD)
}
class PageTemplates(id: EntityID<Long>) : LongEntity(id) {
	var refName by PAGE_TEMPLATE.refName
	val fields by InputFields optionalReferrersOn INPUT_FIELD.pageTemplate
	companion object : LongEntityClass<PageTemplates>(PAGE_TEMPLATE) {
		fun get(refName: String): PageTemplates? { .... ] }
}

I want to write the get(refName: String) function which will return an entire PageTemplate object, including all of its fields, eagerly fetched and ready to be sent up to the service layer as a detached object.

fun get(refName: String): PageTemplates? {
	var template: PageTemplates? = null
	transaction(dbConnections.connect()) {
		addLogger(StdOutSqlLogger)
		// there will only be one matching PAGE_TEMPLATE to this query, but multiple rows because there are many INPUT_FIELDS belonging to the template
		val result = PAGE_TEMPLATE.innerJoin(INPUT_FIELD).select {
			PAGE_TEMPLATE.refName eq refName and (INPUT_FIELD.pageTemplate eq PAGE_TEMPLATE.id)
		}.toList()				
		if (result != null) {
			template = wrapRows(result)		// type mismatch error here
		}
	}
	return template
}

Maybe I'm completely misunderstanding Exposed, but my gut feeling is that an ORM should allow me to do this. As it is, I feel as though I have to write a getPageTemplate(refName) function, a getInputFieldsForTemplate(refName) function, and and many more besides, at both the service and DAO layers. And that doesn't feel very ORM to me.

[Tapac]

Related issue #281

[Tapac]

@v79 , Why do you use wrapRows (which returns an Iterable) and try to assign it to an object reference?
You have to use one of the following:

fun get(refName: String): PageTemplates? {
	return transaction(dbConnections.connect()) {
		addLogger(StdOutSqlLogger)		
		wrapRows(PAGE_TEMPLATE.innerJoin(INPUT_FIELD).select {
			PAGE_TEMPLATE.refName eq refName and (INPUT_FIELD.pageTemplate eq PAGE_TEMPLATE.id)
		}).firstOrNull()				
	}
}

or

fun get(refName: String): PageTemplates? {
	return transaction(dbConnections.connect()) {
		addLogger(StdOutSqlLogger)		
		PAGE_TEMPLATE.innerJoin(INPUT_FIELD).select {
			PAGE_TEMPLATE.refName eq refName and (INPUT_FIELD.pageTemplate eq PAGE_TEMPLATE.id)
		}.firstOrNull()?.let { row ->
                    wrapRow(row)
                }				
	}
}