If I am coming to you today, it is to learn about best practices, but also to know if I am on the wrong track.
Here I make several requests that I need and by force I ask if really this is the best way to achieve my ends but also if I am doing it correctly.
I’ll take an example that I was stuck on for a little while.
I have four types of document:
Action : “id”,“table”
Tache : “id”, “actId”,“table”
Ordre : “id”,“actId”,“rutId”,“table”
actId refers to a document of type Action
rutId refers to a document of type Route
table allows me to know the type of the document
So I would like my request to return me the id of my Tache the id of my Ordre and the id of my Route, specifying that Tache.actId == Ordre.actId and that several Tache can have the same actId .
I managed to do:
WITH ENSEMBLE AS (SELECT rl.id as OrdreId, a.id as ActionId, r.id as RouteId FROM bucket rl JOIN bucket a ON KEYS rl.actId JOIN bucket r ON KEYS rl.rutId Where rl.table ="Ordre") SELECT e.id as TacheId, (SELECT RAW k.OrdreId FROM ENSEMBLE k WHERE k.ActionId = e.actId) as OrdreId, (SELECT RAW k.RouteId FROM ENSEMBLE k WHERE k.ActionId = e.actId) as RouteId FROM bucket e where e.table="Tache" and e.actId IN (SELECT RAW k.ActionId FROM ENSEMBLE k)
But I feel like it’s heavy and slow, can you tell me if I can’t do better and comment?