Collections and counts


@borrrden I need count of two collections(CollectionB ,CollectionC) related to CollectionA .If the actual count of CollectionB related to A is 2 and CollectionC related to A is 3 but i’m getting the count as 6 while running the above query


It’s a bit hard to reason about all the hypotheticals here. That’s why I asked for some samples inputs here to reason about what the result should be and to write a test for it.


Let consider the count of CollectionB related to CollectionA is 4
and count CollectionC related to CollectionA is 8
The actual output be 4 and 8 respectively
But If i run the above query i’m getting count’s as 32


@borrrden is anything i need to change the query?


Show us some actual documents that produce wrong results. Otherwise we’re just going around in circles.


“msg”:“lorum ipsum”






What’s the output of query.explain?


Output of Query.explain:
SELECT fl_result(fl_root(“collectionADS”.body)),fl_result(count(fl_root(“collectionBDS”.body))), fl_result(count(fl_root(“collectionCDS”.body))) FROM kv_default AS “collectionADS” LEFT OUTER JOIN kv_default AS “collectionBDS” ON ((fl_value(“collectionADS”.body, ‘documentId’) = fl_value(“collectionBDS”.body, ‘referralIdOfA’) AND fl_value(“collectionADS”.body, ‘type’) = ‘collectionA’) AND fl_value(“collectionBDS”.body, ‘type’) = ‘collectionB’) AND (“collectionBDS”.flags & 1) = 0 LEFT OUTER JOIN kv_default AS “collectionCDS” ON ((fl_value(“collectionADS”.body, ‘documentId’) = fl_value(“collectionCDS”.body, ‘referralId’) AND fl_value(“collectionADS”.body, ‘type’) = ‘collectionA’) AND fl_value(“collectionCDS”.body, ‘type’) = ‘collectionC’) AND (“collectionCDS”.flags & 1) = 0 WHERE (fl_value(“collectionADS”.body, ‘type’) = ‘collectionA’ AND fl_value(“collectionADS”.body, ‘status’) = ‘PUBLISHED’) AND (“collectionADS”.flags & 1) = 0 GROUP BY fl_value(“collectionADS”.body, ‘documentId’), fl_value(“collectionBDS”.body, ‘referralIdOfA’), fl_value(“collectionCDS”.body, ‘referralId’)


None of this collectionADS stuff shows up in your sample documents. Don’t give samples that leave us guessing as to the real content of the documents. Give it as if we were going to take it and immediately run it in a program to test the result (not fill in the blanks first).


@borrrden collectionADS represents CollectionA


I understand what it represents but it is too abstract. For example, what is the correct result for running the query against the sample that you gave? The original query makes use of a type property, a status property, areferralId property, and a referralIdOfA property. None of those are present in your sample.


Collection A:

“documentId”: “collection_a_id”,
“msg”: “lorum ipsum”

Collection B,

“documentId”: “collection_b_id”,
“msg”: “lorum ipsum”,
“referralIdOfA”: “collection_a_id”


“documentId”: “collection_c_id”,
“msg”: “lorum ipsum”,
“referralId”: “collection_a_id”
If I chain the joins the count value returned from the query is not the actual value


@borrrden Any update?


When I added those documents you gave and run the query you gave above I got 3 entries in 1 result using .NET:

Index 0: The first document in your list
Index 1: 1
Index 2: 1

This seems to be correct? What are you getting?


@borrrden just increase the number of entries of Collection B and Collection C to 5 and above it will return the wrong results in Couchbase lite for android


I’m not that big of an expert in the way that JOIN works but what is happening is that it’s creating a sort of cross product for every permutation of the JOIN. So say you have values of 3 and 5, first you end up with 3 rows for the initial join and then each of those rows get joined with the 5 from the second for a total of 15 entries. @jens Is this how JOIN is supposed to work? Part of me thinks it should be different but I can’t quite put my finger on what the behavior should be and the other part thinks that this is correct.

As a workaround, obviously, you can stop chaining JOIN in the short term and run the two independently.


@borden shall i report as a bug in github?


It took me a while to correctly write the N1QL equivalent but the same behavior applies to server which makes me think that this result is correct.


I’ve confirmed with the N1QL team that the results are correct. Unfortunately given the current mobile query constraints there is not a way to get all the results you want in one single query. It would be reliant on an aggregate distinct operator which has yet to be implemented (i.e. Function.count(SelectResult.expression("collectionBDS")).distinct()). The reasoning behind the way it becomes 6 is because each join permutation needs to be accounted for. So in the case you have 2 matches on B and 3 matches on C your rows would be like this:

  1. Result with B1 and C1
  2. Result with B1 and C2
  3. Result with B1 and C3
  4. Result with B2 and C1
  5. Result with B2 and C2
  6. Result with B2 and C3

COUNT is a post fetch operator so it will just return the number of rows emitted in the query. You will have to split your query into two each with one join to get the results you want. If you are worried about multithreading issues you can run the entire two step query inside of a call to inBatch


Thanks @borrrden :grinning: