Subquery for retrieving count

Question

I'm trying to select all parents with the count of their children.

I have the following query:

SELECT 
    a.*, 
    (SELECT COUNT(*) FROM demo b WHERE b.parent = a.name) as count 
FROM demo a 
WHERE 
    meta(a).id LIKE "xyz:%" 
    AND a.parent IS MISSING 
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0

My documents look similar to:

xyz:1

{
    id: 1,
    name: "parent",
    createdAt: 1234
}

xyz:2

{
    id: 2,
    name: "child",
    parent: "parent",
    createdAt: 5678
}

I get the below error:

Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM demo.

Error code: 5010

UPDATE: The below query seem to work:

SELECT 
    a.*, 
    (SELECT COUNT(id) as count FROM demo b WHERE b.parent = "parent")[0].count as count 
FROM demo a 
WHERE 
    meta(a).id LIKE "xyz:%" 
    AND a.parent IS MISSING 
ORDER BY a.createdAt DESC LIMIT 50 OFFSET 0

but if I replace "parent" with a.name it gives the same error.

Johan Larson Johan Larson · Accepted Answer · 2019-01-03T13:09:39

You need to do an ANSI JOIN followed by a GROUP BY. The query will look something like this:

SELECT l.name, COUNT(1)
FROM demo l JOIN demo r ON r.name = l.child
GROUP BY l.name

To run this query you will need an index on the r side:

CREATE INDEX demo_name_idx ON demo(name)

Be sure to test the query on actual data; there may be some wrinkles I am missing. In particular, you may want to group by l.id rather than l.name if your names are not unique.

Subquery for retrieving count

1 Answers