Re: SQL to get WORKING from NODES

Philip Martin <philip.martin_at_wandisco.com> writes:

> but I don't know how to fix the second one. How do I count the number
> of rows returned by that GROUP BY query?

From IRC the following was suggested

-- STMT_SELECT_WORKING_NODE_CHILDREN_1
SELECT DISTINCT local_relpath FROM nodes
WHERE wc_id = ?1 AND parent_relpath = ?2 AND op_depth > 0;

-- STMT_COUNT_WORKING_NODE_CHILDREN_1
SELECT COUNT(*) FROM (SELECT DISTINCT local_relpath FROM nodes
                      WHERE wc_id = ?1 AND parent_relpath = ?2
                      AND op_depth > 0);

In the longer term if/when we switch to per-dir queries we can
probably eliminate the counting step in some or all cases.

> This leads on to the problem of selecting just the highest op_depth
> for each child. Is it possible to get one query to return just the
> highest op_depth for each child?

I suspect we will still want to do this at some point.

-- 
Philip