https://kotlinlang.org logo
#exposed
Title
# exposed
d

Dario Pellegrini

05/18/2022, 10:18 AM
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

neetkee

05/19/2022, 2:54 PM
It should make only one query for
findById
. Do you also request any
referencedOn
fields by any chance?
d

Dario Pellegrini

05/19/2022, 9:56 PM
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

neetkee

05/20/2022, 7:02 AM
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

Dario Pellegrini

05/20/2022, 10:17 AM
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

Lucas

06/22/2022, 3:38 PM
Did you manage to solve this?
d

Dario Pellegrini

06/22/2022, 3:40 PM
No solution right now, I’m sorry.
l

Lucas

06/22/2022, 3:43 PM
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

Dario Pellegrini

06/22/2022, 3:47 PM
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

Lucas

06/22/2022, 4:03 PM
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
3 Views