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.