Skip to content

Instantly share code, notes, and snippets.

@BARNZ

BARNZ/materialised_path.php

Created October 1, 2020 19:17
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save BARNZ/eda50d82d9722470f5e0e061502d1b9b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save BARNZ/eda50d82d9722470f5e0e061502d1b9b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. BARNZ created this gist Oct 1, 2020.
    100 changes: 100 additions & 0 deletions materialised_path.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,100 @@
    // 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
    )
    ");