CBL Count With multiple Left Joins

Mobile Couchbase Lite
, ,
1

Hi! How can I modify this Couchbase Lite Query to correctly return the distinct count of tasks and notes, along with an accurate sum of both? Right now, the query inflates the values.
For example, I expect COUNT(task) to be 1, COUNT(note) to be 12, and (COUNT(task) + COUNT(note)) to be 13, but I’m getting 13, 13, and 26 instead.

SELECT
    tag.id,
    tag.name,
    COUNT(task) AS taskCount,
    COUNT(note) AS notesCount,
    (COUNT(task) + COUNT(note)) AS numberOfTimesUsed
FROM temp.tags AS tag
LEFT JOIN temp.tasks AS task ON ARRAY_CONTAINS(task.tagIds, tag.id)
LEFT JOIN temp.notes AS note ON ARRAY_CONTAINS(note.tagIds, tag.id)
GROUP BY tag.id
2

I’m not an expert on joins, but I think the COUNT clauses there are incorrect. COUNT(task) is going to be the number of rows with task, but using LEFT JOIN means each row from the main data source (tag) is going to produce at least one row from task even if no documents match.

I think the fix is to make the parameter of COUNT be some property of the task / note document, even its docID.

1 Like