DAO eager loading executes unnecessary queries

[unbearables]

Hello, I have encountered a strange behavior with eager loading collection of entities. The eager loading for collections seems not be working - load call on entity triggeres wrong query.

I have following 2 entities:

object DiscussionTable : IdTable<UUID>(name = "discussion") {
    override val id = uuid(name = "discussion_id").entityId()
    ...
}
class DiscussionEntity(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<DiscussionEntity>(DiscussionTable)
    ...
    val messages by DiscussionMessageEntity referrersOn DiscussionMessageTable.discussionId
}

object DiscussionMessageTable : IdTable<UUID>(name = "discussion_msg") {
    override val id = uuid(name = "discussion_msg_id").entityId()
    val discussionId = reference(name = "discussion_id", foreign = DiscussionTable)
    ...
}
class DiscussionMessageEntity(id: EntityID<UUID>) : UUIDEntity(id) {
    companion object : UUIDEntityClass<DiscussionMessageEntity>(DiscussionMessageTable)

    var discussionId by DiscussionMessageTable.discussionId
    ...
}

When I try to find DiscussionEntity by id while eager loading it's messages like so:

val discussion = transaction {
    DiscussionEntity.findById(discussionId)?.load(DiscussionEntity::messages)
}

It creates additional queries instead of getting all the data from 1st query. This outputs:

SELECT ... FROM discussion WHERE discussion.discussion_id = 'bc07d4ed-da6b-440a-be13-0f5c25ab4c44'
SELECT ... FROM discussion_msg WHERE discussion_msg.discussion_id = 'bc07d4ed-da6b-440a-be13-0f5c25ab4c44'
SELECT ... FROM discussion_msg WHERE discussion_msg.discussion_msg_id = 'bc07d4ed-da6b-440a-be13-0f5c25ab4c44'

The 2nd and 3rd query are not supposed to be there. All data is accessible from the 1st query (I am not using slice).

Without ?.load(DiscussionEntity::messages) it does only 2 queries (without the 3rd wrong query), but still does the additional 2nd query.

I am using exposed version 0.39.2

Am I doing something wrong? Is eager loading of collections in 1 SQL call supported?

[Tapac]

Hi, eager loading in a single query is not supported and always requires another call to fetch related entities. To me 3rd query looks strange, I'll try to reproduce it.

[bog-walk]

Hi @unbearables I am not able to reproduce this issue using current version 0.45.0. Using the table mappings that you provided with the eager-loading transaction block does not log the 3rd query that you mentioned (WHERE discussion_msg.discussion_msg_id ...).

If the problem persists with the current version, please consider sharing a minimally-reproducible example that includes how the discussion transaction block is being called (is it a top-level transaction or nested).

[bog-walk]

If the 3rd query is still being logged with the current Exposed version, please consider reopening this issue on YouTrack with the details requested above, so we can attempt to investigate further.