SELECT Query that returns a list of objects, that contain Collection A + a count of all Collection B that correspond to Collection A

Mobile Couchbase Lite
1

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.

2

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.

3

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.

4

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.)