Hello everyone! Exposed is wonderful. I manage to build API based on a very related MySQL database with very simple code, but I can’t understand why my DAO is performing so many queries instead of a single one. This is my DAO. When I do

findById

I see a large number of SELECT queries instead of a single one. This could be a problem if server and database are not in the same network. Is there any way to force Exposed use a single query?

class HighlightDAO(id: EntityID<Int>): IntEntity(id) {
    companion object : IntEntityClass<HighlightDAO>(Highlights)
    val video by VideoDAO referencedOn Highlights.video
    val preview by FileDAO referencedOn Highlights.preview
    val title by Highlights.title
    var createdAt by Highlights.createdAt
    var match  by MatchDAO referencedOn Highlights.matchId

    var players by PlayerDAO via HighlightsPlayers
    val events by HighlightEventDAO referrersOn HighlightEvents.highlightId

    val homeScore by Highlights.homeScore
    val awayScore by Highlights.awayScore
    val empty by Highlights.empty
}

...

HighlightDAO.findById(highlightId)

It should make only one query for

findById

. Do you also request any

referencedOn

fields by any chance?

Actually yes. I'm providing the code as soon as possible. My question is if it could be possible to force Exposed to use a single query every time

Exposed will issue a query per referenced field, afaik. You could use exposed DSL to make joins manually, then wrap it to classes via

HighlightDAO.wrapRow

I suspected that Expose performs 1 query per referenced field (maybe to avoid load too many records). I already tried using DSL join with wrapRow, but I still see multiple queries. Actually I tried also a join with a manual mapping of the results, but I still see multiple queries (with just joins, no DAO). That’s why I thought that maybe there is a global configuration

Did you manage to solve this?

No solution right now, I’m sorry.

I think this should be possible with a manual join and parsing the ResultRow "recursively" using the DAO api, but i feel like there should be an easier alternative

Actually I started writing a Join using DAO wrap, but there were still multiple queries. The strange thing is that this multiple queries behavior happened also using ORM join without using DAO at all

I mean, i managed to do it, but i really wanted there to be a better way In my use case, i need to fetch the menu from the database, from 3 tables, Categories, Products, and FriendlyProduct (some extra product info for the customer) This is a very simple example The following code produced a single query

SELECT IndividualData.InfoProdutoCliente.id, ... FROM IndividualData.InfoProdutoCliente INNER JOIN ... WHERE IndividualData.InfoProdutoCliente.idTenant = 1003

But again, i wish there was a better way to do this