Best way to count related documents

SQL++
1

I have two documents: Organization and Users.
I want to fetch all organizations and the count of users. An organization document doesn’t have any field that is linked with users, but a user has orgId. I seeded DB with data, total users is about 110K. Total organizations are about 1100. And every organization has about 100 users. JOIN takes a lot of time, about 17-20 seconds, I tried IN operator, the time reduced to 7 seconds. But slow, anyway. As far as I have LIMIT and OFFSET, I want to get organizations first and after that count users.

I tried simple JOIN just to get data:

SELECT info.org.id,
       (
           SELECT COUNT(*)
           FROM `MyBucket` users
           join info  on keys info.org.id = users.orgId
           WHERE users.model = 'user'
              ) AS uCount
           FROM (
               SELECT *
               FROM `MyBucket` org WHERE org.model = 'org'
               // ... here are some joins but I skip them for brevity
               ORDER BY org.level ASC,
                        org.name ASC
               OFFSET 0
               LIMIT 10) as info

The problem is couchbase force me to use USE KEYS operator, but I cannot, because org doesn’t have any fields related to users.

2 
CREATE INDEX ix1 ON  MyBucket(level,name, id) WHERE model = "org";
CREATE INDEX ix2 ON  MyBucket(orgId) WHERE model = "user";

SELECT d.*, ARRAY_COUNT(u) AS uCount
FROM  (SELECT o.*
    FROM `MyBucket` AS o
    WHERE o.model = "org" AND o.level IS NOT NULL
    ORDER BY o.level, o.name
    OFFSET 0
    LIMIT 10) AS d
LEFT NEST `MyBucket` AS u ON d.orgId = u.orgId AND u.model = "user";
3

Thanks for the answer, but I get error

syntax error - at (",

Looks like you skipped comma, I put comma but then I have another syntax error:

syntax error - at LEFT

4

Not comma, FROM is missing. Try updated query

5

Now I have

Ambiguous reference to field o.

6

Found the error. In the nest clause need to use “d.orgId” not “o.orgId”.
Thanks.