Hi Guys I have two collections which one collection Meta.id(Collection A) is the referral Id(Collection B) of another.I want to return list of objects of collection A and Count of all Collection B related to Collection A.
SELECT Query that returns a list of objects, that contain Collection A + a count of all Collection B that correspond to Collection A
Hi! Look at the docs for Join expressions. You create a join clause expressing how one document maps to another, and then you can query things about both documents, including their counts in the database.
If documents in Collection A refer to Documents in Collection B, then the query you want is:
SELECT A.b_ref, count(A.b_ref)
FROM A, B
WHERE B.id == A.b_ref
GROUP BY A.b_ref
That will give you a list of B.id s with the number of A’s that point to it, for each.
You can’t write a query like that (with multiple FROM sources) in Couchbase Lite. You have to explicitly use a JOIN, like
SELECT A.b_ref, count(A.b_ref) FROM db as A JOIN db AS B ON B.id == A.b_ref GROUP BY A.b_ref
(sorry I can’t provide source code: I’m not fluent in that syntax, and you haven’t said what language you’re using.)