How can I prevent the DAO library from running mul...

# exposed

[Hakon Grotte]

How can I prevent the DAO library from running multiple select queries for the optionalBackReferencedOn reference in the following example?

Definitions (I have modified the actual object names and definitions):

// tables (DSL)
object PersonTable : IdTable<UUID>("person") {
  override val id: Column<EntityId<UUID>> = uuid("person_id").entityId()
  val name = varchar("name", 255)
}
object AddressInformation : IdTable<UUID>("address") {
  override val id: Column<EntityId<UUID>> = reference("address_id", PersonTable.id) // from this table's perspective the reference is not optional
  val address = varchar("address", 255)
}

// entities (DAO)
class PersonEntity(id: EntityID<UUID>) : Entity<UUID>(id) {
  companion object : EntityClass<UUID, PersonEntity>(PersonTable)
  var name by PersonTable.name
  val addressInformation by AddressEntity optionalBackReferencedOn AddressInformation.id 
}
class AddressEntity(id: EntityID<UUID>) : Entity<UUID>(id) {
  companion object : EntityClass<UUID, AddressEntity>(AddressInformation)
  var address by AddressInformation.address
}

I want to select all people matching the same name and include the optional reference addressInformation. Moreover, I want to use a single query with join for this. I read online that wrapRows can be used for this:
Transaction:

PersonTable.leftJoin(AddressInformation)
  .select { PersonTable.name eq name }
  .map { PersonTable.wrapRow(it).toDTO() }

Inside my toDTO() I do select the PersonEntity.addressInformation . This makes the DAO api run an additional select query to fetch this field. How can I avoid this additional query from being run?

[Hakon Grotte]

My current hacky solution avoids using the optionalBackReferencedOn and is quite ugly:
Inside final map:

.map {
  val addressEntity = try {
    AddressEntity.wrapRow(it)
  } catch (e: NullPointerException) { null }
  val personEntity = PersonEntity.wrapRow(it)
  personEntity.toDto(addressEntity)
}

, but only uses 1 SQL select query

[Gustav Elmgren]

Hm... Are you using the DAO at all? Have you implemented your own wrapRow (i think it exists one on the DTO class)?

[Gustav Elmgren]

You should be able to control this yourself without the DAO. It might be that it always do additional selects when using DAOs to avoid the n+1 problem?

[Hakon Grotte]

Both the PersonEntity and the wrapRow I use are from the DAO library.

My approach is inspired by @tapac's answer in this issue (2. approach); you can supposedly join using DSL and then wrapRow(s) from DAO to only do a single query

I am interpreting your second comment to suggest mapping directly from the ResultRow to the DTO , is that correct? I do, however, wonder if it is possible to avoid dealing with the ResultRow explicitly?