Skip to content

Instantly share code, notes, and snippets.

@wwnbb

wwnbb/find_cycles.sql

Created October 19, 2023 15:36
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save wwnbb/24d5890f7ab436ca45b1a4eb39c97c0d to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save wwnbb/24d5890f7ab436ca45b1a4eb39c97c0d to your computer and use it in GitHub Desktop.
Download ZIP
How to find cycles in parent <- child relationships.
Raw
find_cycles.sql
WITH RECURSIVE recursive_search AS (
SELECT
uuid,
ARRAY[uuid] AS path,
false AS cycle_found
FROM some_table
WHERE parent_uuid IS NOT NULL
UNION ALL
SELECT
d.uuid,
d.parent_uuid,
r.path || d.uuid,
d.uuid = ANY(r.path) OR r.cycle_found
FROM some_table d
JOIN recursive_search r ON d.uuid = r.parent_uuid
WHERE NOT r.cycle_found
)
SELECT distinct recursive_search.uuid
FROM recursive_search
WHERE cycle_found;
@wwnbb
Copy link
Author

wwnbb commented Oct 19, 2023

This SQL query leverages Common Table Expressions (CTE) using the WITH RECURSIVE clause to perform a recursive search in a hierarchical data structure. The goal of the query is to identify cycles in a tree-like table named some_table.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment