Hello,
I would like to querying CB like right join.
My data is :
fiche: {
_id : fiche::99
label : “test”
cat : {
id :“cat::1111”,
display : “Category 1”
}
Category : {
_id: “cat::1111”,
display:“Category 1”
}
I want to retrieve all “fiche” attach to a “Category” like :
id : “category::1111”,
display : “Category 1”,
fiches:[
{
id : “fiche::99”,
label : “test”
}, {…}
]
or if no fiche is attach
id : “category::1111”,
display : “Category 1”,
fiches:
With “left join on keys” it doesn’t work when no “fiche” is attach to a category.
Any ideas?
vsr1
March 1, 2018, 5:32pm
2
Try Index Join
CREATE INDEX ix1 ON fiche(cat.id);
SELECT c.*, f AS fiche FROM category AS c NEST fiche AS f ON KEY f.cat.id FOR c;
thank you but when nothing is attached to “category” there is no return.
I want to recover all the categories and if “fiche” are attached, recover the “fiche” in an “array” if not an empty “array”
a simple solution is to make two requests but I would like to do it with only one if possible.
vsr1
March 2, 2018, 7:17am
4
SELECT c.*, f AS fiche FROM category AS c LEFT NEST fiche AS f ON KEY f.cat.id FOR c;
This gives no fiche instead empty array. If you want empty array try following
SELECT c.*, IFMISSING(f, ) AS fiche FROM category AS c LEFT NEST fiche AS f ON KEY f.cat.id FOR c;
Thank you very much.
It works perfectly
The complete solution for my usecase :
SELECT c.*, IFMISSING(ARRAY {“id”: meta(l).id,“label”: l.label } FOR l IN f END,[]) as fiches
FROM category AS c
use keys "category::1"
LEFT NEST fiche AS f ON KEY fiche.category.id FOR c;