Scope by Directory with Path Depth

In addition to scoping by directory, it may be useful to start with a given path, but then only include subordinate paths within a given range below the selected path.

In this case, we make use of the same nested set model calculations seen in the previous section, but include the use of the path_depth parameter as well.

The following example selects all paths starting two levels below a given path:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
WITH root_path AS (
    SELECT
        sd.ns_left,
        sd.ns_right,
        sd.scan_id,
        sd.path_depth
    FROM srs.current_fs_scandata_ad AS sd
    WHERE sd.fullpath_hash = srs.path_hash('\\server1.ad.test.lab\Share\Groups')
    AND sd.path_type = 2
)
SELECT sd.*
FROM srs.current_fs_scandata_ad AS sd
JOIN root_path AS rp ON rp.scan_id = sd.scan_id
 AND rp.ns_left <= sd.ns_left
 AND rp.ns_right >= sd.ns_right
 AND sd.path_depth > rp.path_depth + 2;    -- Upper bound

This example is common when folder structures have managed content, such as collaborative or group folders, organized below division or department folders one or more layers deep.

In order to pull all the content from just the group folders themselves, and not include the structural folders, we can make use of path depth, but assign the selected path to the root structural folder.

For a share organized as:

	\\Server\Share\Groups\Departments\GroupA

the selected path could be \\Server\Share\Groups and the path_depth could be assigned to the root_path + 2 or greater, as in the SELECT statement above.

We could just as easily limit the depth of paths searched by adding another comparison of path_depth as a lower bounds:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH root_path AS (
    SELECT
        sd.ns_left,
        sd.ns_right,
        sd.scan_id,
        sd.path_depth
    FROM srs.current_fs_scandata_ad AS sd
    WHERE sd.fullpath_hash = srs.path_hash('\\dbdev.db.dtest.lab\home')
    AND sd.path_type = 2
)
SELECT sd.*
FROM srs.current_fs_scandata_ad AS sd
JOIN root_path AS rp ON rp.scan_id = sd.scan_id
 AND rp.ns_left <= sd.ns_left
 AND rp.ns_right >= sd.ns_right
 AND sd.path_depth > rp.path_depth + 2   -- Upper bound
 AND sd.path_depth < rp.path_depth + 3;  -- Note that we have a lower bound as well