I need to show count associated with each group even if the count is zero, however the query below shows shows non empty groups only. Could anyone please review the query.
select RAW count(1) from accountMgmt as act
left outer join accountMgmt as ci
on META(act).id = ci.accountToken
where
act.type=“account” and
act.packageName = “VR3” and
act.status = ‘ACTIVATED’ and
ci.type=“catalogItem” and
ci.status=“RECORDED”
group by
META(act).id;
select RAW count(1) from accountMgmt as act
left outer join accountMgmt as ci
on META(act).id = ci.accountToken
where
act.type=“account” and
act.packageName = “VR3” and
act.status = “ACTIVATED” and
(ci IS MISSING OR (ci.type=“catalogItem” and
ci.status=“RECORDED”))
group by
META(act).id;
I use following index for ci term:
CREATE INDEX index_catalogitems ON accountMgmt ( accountToken, status, durationMinutes )
WHERE ( type= “catalogItem” and (status = ‘RECORDED’ or status = ‘BOOKED’ ));
SELECT RAW COUNT(ci)
FROM accountMgmt AS act
LEFT OUTER JOIN accountMgmt AS ci
ON META(act).id = ci.accountToken AND ci.type="catalogItem" AND ci.status="RECORDED"
WHERE act.type="account" AND act.packageName = "VR3" AND
act.status = "ACTIVATED" AND
GROUP BY META(act).id;
CREATE INDEX index_catalogitems ON accountMgmt ( accountToken, status, durationMinutes ) WHERE type="catalogItem";