Hello everyone! Exposed is wonderful. I manage to ...
# exposed
d
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?
Copy code
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)
n
It should make only one query for
findById
. Do you also request any
referencedOn
fields by any chance?
d
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
n
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
Or if you have N+1 problem, you can use .with() and load() methods. https://github.com/JetBrains/Exposed/wiki/DAO#eager-loading
d
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
l
Did you manage to solve this?
d
No solution right now, I’m sorry.
l
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
d
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
There is something I’m missing for sure
l
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
If you're getting multiple queries, you're probably still using
wrapRow
and
entity
referencedOn
Another approach i tried is the following, which looks better but still too much boilerplate
This also produced a single query
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