Hey guys,
is it potentially possible to do the following with N1QL?:
(all in the same bucket)
I have 2 document types:
Doc A {
idA: anyID,
…
}
Doc B {
idB: ID,
idA: anyID,
…
}
They have a 1:* relation (A to B)
Is it possible to write a N1QL SELECT query that returns a list of objects, that contain A + a count of all B that correspond to A.
Sth. like this:
[{
doc: A {…},
count: 23
},
{
doc: A {…},
count: 45
},
…]
How do you differentiate Doc A , Doc B is there type field in the documents or prefix in META().id ?
On what key you have 1:* relation . DocB.idA is META().id of DocA?
CREATE INDEX ix_seat ON bucket(stageId) WHERE type = "SEAT";
SELECT stage, q.count
FROM (SELECT stageId, COUNT(1) AS count
FROM bucket
WHERE type = "SEAT" AND stageId IS NOT NULL
GROUP BY stageId
) AS q
JOIN bucket stage ON KEYS q.stageId
WHERE stage.type = "SEAT";
If need non present document also
SELECT stage, q.count, q,stageId
FROM (SELECT stageId, COUNT(1) AS count
FROM bucket
WHERE type = "SEAT" AND stageId IS NOT NULL
GROUP BY stageId
) AS q
LEFT JOIN bucket stage ON KEYS q.stageId ;
Hey,
thank you very much for the fast response.
The upper one is almost perfect, I don’t really get what the lower one is doing.
My problem in the upper one, there can be stages that don’t have a seat, but have to be shown anyways (with count 0). Is this achievable? Sry that I missed that in the first place.
CREATE INDEX ix_seat ON default(stageId);
CREATE INDEX ix_stage ON default(id) WHERE type = "STAGE";
SELECT stage, SUM(CASE WHEN seat IS MISSING TEHN 0 ELSE 1 END) AS count
FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
WHERE stage.type = "STAGE" AND stage.id IS NOT NULL
GROUP BY stage;
This would work perfectly if I had no seats. Problem is in my data model, empty seats are not missing but tagged with a flag empty: true/false. I thought I can just add this condition to the WHERE clause, but actually this doesn’t work.
So I have sth. like this:
SELECT stage, SUM(CASE WHEN seat IS MISSING TEHN 0 ELSE 1 END) AS count
FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
WHERE stage.type = “STAGE” AND stage.id IS NOT NULL AND seat.type = "EMPTY"
GROUP BY stage;
This makes sense, because I try to join something with a condition that is not fulfilled. The question is, how to always add the stage to the result, even if no join matches? And then set the count to 0
post sample document and explain how to use it. example : seat.empty value is true add to count
CREATE INDEX ix_seat ON default(stageId,empty);
CREATE INDEX ix_stage ON default(id) WHERE type = "STAGE";
SELECT stage, SUM(CASE WHEN (seat.empty == true ) TEHN 1 ELSE 0 END) AS count
FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
WHERE stage.type = "STAGE" AND stage.id IS NOT NULL
GROUP BY stage;
You solved it with the example, that was the solution
Thank you very very much, and sorry for the confusion. I thought I simplify by not using my real documents… didn’t work.
The next time I will initially explain and post my full documents.
Sorry for the waste of time.
Great support
Edit: When I have no stages, I would expect the response to be empty, but it is:
[
{
“count”: null
}
]
can I catch this somehow in Couchbase directly or should I do this in the code?
SELECT q.stage, q.count FROM (
SELECT stage, SUM(CASE WHEN (seat.empty == true ) TEHN 1 ELSE 0 END) AS count
FROM default stage LEFT JOIN default seat ON KEY seat.stageId FOR stage
WHERE stage.type = "STAGE" AND stage.id IS NOT NULL
GROUP BY stage) AS q
WHERE q.stage IS NOT MISSING;