Hello everyone! Exposed is wonderful. I manage to build API based on a very related MySQL database w...

# exposed

[Dario Pellegrini]

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)

[neetkee]

It should make only one query for findById. Do you also request any referencedOn fields by any chance?

[Dario Pellegrini]

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

[neetkee]

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

[neetkee]

Or if you have N+1 problem, you can use .with() and load() methods. https://github.com/JetBrains/Exposed/wiki/DAO#eager-loading

[Dario Pellegrini]

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

[Lucas]

Did you manage to solve this?

[Dario Pellegrini]

No solution right now, I’m sorry.

[Lucas]

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

[Dario Pellegrini]

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

[Dario Pellegrini]

There is something I’m missing for sure

[Lucas]

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

[Lucas]

If you're getting multiple queries, you're probably still using wrapRow and entity referencedOn

[Lucas]

Another approach i tried is the following, which looks better but still too much boilerplate

[Lucas]

This also produced a single query

[Lucas]

Using with and referencedOn reduced the number of queries to one per relation (before i was getting 1 per relation/record), which for now, is enough