materialised_path.php

// Using postgres ltree module to build an automatic materialised path from a parent_id field
// Useful for parent-child database structures where you need to query for non-immediate descendants or ancestors

DB::statement("CREATE EXTENSION IF NOT EXISTS pg_trgm"); // for additional index types
DB::statement("CREATE EXTENSION IF NOT EXISTS ltree"); // to build a materialised path with

// Helper function for fetching a materialised path of the given table and ID
// Assumes the parent fkey is parent_id

$sql = <<<'SQL'
    CREATE OR REPLACE FUNCTION get_materialised_path(tableName text, id bigint) RETURNS ltree AS
        $$
            DECLARE path ltree;
            BEGIN
                EXECUTE format('
                        SELECT
                            CASE WHEN t.parent_id IS NULL THEN t.id::text::ltree
                            ELSE get_materialised_path(''%I'', t.parent_id) || t.id::text
                            END
                        FROM %I as t
                        WHERE t.id = $1
                    ', tableName, tableName
                )
                USING id
                INTO path;
                RETURN path;
            END
        $$
        LANGUAGE plpgsql VOLATILE
        COST 100;
SQL;
DB::statement($sql);

// Create a function to handle automatic updating of materialised paths for us in the DB.
$sql = <<<'SQL'
    CREATE OR REPLACE FUNCTION tf_update_materialised_path() RETURNS trigger AS
        $$
            BEGIN
                IF TG_OP = 'UPDATE' THEN
                    IF (COALESCE(OLD.parent_id, 0) != COALESCE(NEW.parent_id, 0) OR NEW.id != OLD.id) THEN
                        -- update all descendant nodes including self
                        EXECUTE format('
                                UPDATE %I SET path = get_materialised_path(''%I'', %I.id)
                                WHERE $1 @> %I.path
                            ', TG_TABLE_NAME, TG_TABLE_NAME, TG_TABLE_NAME, TG_TABLE_NAME
                        )
                        USING OLD.path;
                    END IF;
                ELSIF TG_OP = 'INSERT' THEN
                    EXECUTE format('
                            UPDATE %I SET path = get_materialised_path(''%I'', $1)
                            WHERE %I.id = $1
                        ', TG_TABLE_NAME, TG_TABLE_NAME, TG_TABLE_NAME
                    )
                    USING NEW.id;
                END IF;
                RETURN NEW;
            END
        $$
        LANGUAGE plpgsql VOLATILE
        COST 100;
SQL;
DB::statement($sql);

DB::statement("
    COMMENT ON FUNCTION tf_update_materialised_path() IS 'This trigger re-calculates the materialised path when a new row has been inserted,
        or the row id or its parent id has changed'
");

// Actual usage on a table
// Replace <table> with actual name of database table

DB::statement("ALTER TABLE <table> ADD COLUMN path ltree");

// GiST index to speed up path queries
DB::statement("CREATE INDEX <table>_path_idx ON <table> USING GIST (path)");
DB::statement('
    CREATE TRIGGER tg_update_path
        AFTER INSERT OR UPDATE OF parent_id, id
        ON <table>
        FOR EACH ROW
    EXECUTE PROCEDURE tf_update_materialised_path();
');


// Also useful is a recursive CTE in a view that allows us to easily query the fully qualified name from a parent/child relationship
DB::statement("
    CREATE OR REPLACE view vw_<table>_tree AS (
        WITH RECURSIVE <table>_tree(id, qualified_name) AS (
            SELECT <table>.id, <table>.name
                FROM <table>
            WHERE <table>.parent_id IS NULL
            UNION ALL
            SELECT children.id, (lt.qualified_name || ' > '::text) || children.name
                FROM <table>_tree lt, <table> children
            WHERE children.parent_id = lt.id
        )
        SELECT * FROM <table>_tree
    )
");