Collections and counts

Mobile Couchbase Lite
1

Hi Guys,
I have three Collections-CollectionA,CollectionB,CollectionC

I have to Write the query to get Collection A,Count of CollectionB related to CollectionA,Count of CollectionC related to CollectionA,last inserted value of Collection B and Count of CollectionC related to CollectionB.

CollectionB having referral id of CollectionA

CollectionC having tag referral_doc_id which can be either CollectionA Meta.id or CollectionB Meta.id

I’m Using couchbase lite for Android

I have tried chaining the Joins But I’m not able to get the expected Results.

2

Show us what you’ve done.

3

I have followed this blog
https://blog.couchbase.com/join-queries-couchbase-mobile/

And my final query is like
Query selectQuery = QueryBuilder.select(
SelectResult.all().from(“collectionADS”)
,
SelectResult.expression(Function.count(Expression.all().from(“collectionBDS”)))
,
SelectResult.expression(Function.count(Expression.all().from(“collectionCDS”)))
)
.from(collectionADS)
.join(joinAB, joinAC)
.where(whereExpr)
.groupBy(Expression.property(“documentId”).from(“collectionADS”),
Expression.property(“referralIdOfA”).from(“collectionBDS”),
Expression.property(“referralId”).from(“collectionCDS”));

4

How are we supposed to know what all these variables mean (collectionADS, joinAB, joinAC, whereExpr)? Please make sure your example is complete otherwise we will just be going back and forth trying to figure out what is happening.

5

@borrrden
Complete Query:

DataSource collectionADS = DataSource.database(getCouchDB()).as(“collectionADS”);
DataSource collectionBDS = DataSource.database(getCouchDB()).as(“collectionBDS”);
DataSource collectionCDS = DataSource.database(getCouchDB()).as(“collectionCDS”);

Expression collectionAExpr = Expression.property(“documentId”).from(“collectionADS”);
Expression collectionBExpr = Expression.property(“referralIdOfA”).from(“collectionBDS”);
Expression collectionCExpr = Expression.property(“referralId”).from(“collectionCDS”);

Expression joinABExpr = collectionAExpr.equalTo(collectionBExpr)
.and(Expression.property(“type”).from(“collectionADS”).equalTo(Expression.string(“collectionA”)))
.and(Expression.property(“type”).from(“collectionBDS”).equalTo(Expression.string(“collectionB”)));

Expression joinACExpr = collectionAExpr.equalTo(collectionCExpr)
.and(Expression.property(“type”).from(“collectionADS”).equalTo(Expression.string(“collectionA”)))
.and(Expression.property(“type”).from(“collectionCDS”).equalTo(Expression.string(“collectionC”)));

Join joinAB = Join.leftOuterJoin(collectionBDS).on(joinABExpr);
Join joinAC = Join.leftJoin(collectionCDS).on(joinACExpr);

Expression whereExpr = (Expression.property(“type”).from(“collectionADS”).equalTo(Expression.string(“collectionA”)))
.and((Expression.property(“status”).from(“collectionADS”)).equalTo(Expression.string(“PUBLISHED”)));

Query selectQuery = QueryBuilder.select(
SelectResult.all().from(“collectionADS”)
,
SelectResult.expression(Function.count(Expression.all().from(“collectionBDS”)))
,
SelectResult.expression(Function.count(Expression.all().from(“collectionCDS”)))
)
.from(collectionADS)
.join(joinAB, joinAC)
.where(whereExpr)
.groupBy(Expression.property(“documentId”).from(“collectionADS”),
Expression.property(“referralIdOfA”).from(“collectionBDS”),
Expression.property(“referralId”).from(“collectionCDS”));

6

@borrrden @jens any update on this?

7

What are you expecting to get and what are you getting? What documents are you operating on? You have not said what is going wrong…only that you get “unexpected results”

8

@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

9

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.

10

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

11

@borrrden is anything i need to change the query?

12

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

13

CollectionA:
{
“documentId”:“some_id”,
“msg”:“lorum ipsum”
}

CollectionB:

{
documentAId:“id_a”,
“documentId”:“some_id”

}

CollectionC:
{
documentAId:“id_a”,
“documentId”:“some_id”
}

14

What’s the output of query.explain?

15

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

16

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

17

@borrrden collectionADS represents CollectionA

18

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.

19

Collection A:

{
“documentId”: “collection_a_id”,
“status”:“PUBLISHED”,
“type”:“collectionA”,
“msg”: “lorum ipsum”
}

Collection B,

{
“documentId”: “collection_b_id”,
“msg”: “lorum ipsum”,
“status”:“PUBLISHED”,
“type”:“collectionB”,
“referralIdOfA”: “collection_a_id”
}

CollectionC

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

20

@borrrden Any update?