Hello,
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”
Route: “id”,“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)[0] as OrdreId,
(SELECT RAW k.RouteId FROM ENSEMBLE k WHERE k.ActionId = e.actId)[0] 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?