nineinchnick/closure-table-manager

PHP library that helps maintain adjacency list SQL structures.

closure-table-manager

PHP library that helps maintain adjacency list SQL structures., _(*1)

TL;DR: It allows fetching all ancestors/descendants (indirect parents/children) in a single query, without using recursive queries., _(*2)

Inspired by: * SQL::Tree Perl module * http://www.depesz.com/index.php/2008/04/11/my-take-on-trees-in-sql/, _(*3)

Currently supported databases: * PostgreSQL * SQLite 3 * MySQL and MariaDB, _(*4)

Pull requests with other databases support are very welcome., _(*5)

Installation

Using composer:, _(*6)

curl -sS https://getcomposer.org/installer | php
./composer.phar require nineinchnick/closure-table-manager:dev-master

Usage

Call Manager::getQueries() to get an array of SQL queries that create a helper table to store ancestor/descendant relationships from the main table and triggers that maintain it., _(*7)

When installed, triggers will block the following operations: * Changing the primary key value * Creating loops, _(*8)

A command line script is provided:, _(*9)

Usage: ./vendor/bin/closureTable.php [options] [operands]
Options:
  -d, --dsn <arg>         DSN connection string or just the driver name (pgsql, sqlite, mysql).
  -t, --table <arg>       Table name.
  -p, --parent <arg>      Parent foreign key column name.
  -i, --pk <arg>          Primary key column name.
  --pk_type <arg>          Primary key and parent column type.
  --path <arg>            Path column name; if set, additional triggers will be generated.
  --path_from <arg>       Column which value will be used to build a path. Its values cant't contain path_separator.
  --path_separator <arg>  Path separator character.
  --table_suffix <arg>    Suffix of the closure table.

Example

Having the following tables:, _(*10)

CREATE TABLE products (
  id INTEGER,
  category_id INTEGER NOT NULL REFERENCES categories (id),
  -- ...
  PRIMARY KEY(id)
);

CREATE TABLE categories (
  id INTEGER,
  parent_id INTEGER NOT NULL REFERENCES categories (id),
  -- ...
  PRIMARY KEY(id)
);

It is quite common to ask database for all products in given category and it's subcategories., _(*11)

    SELECT p.*
      FROM products p
INNER JOIN categories_tree c on p.category_id = c.id
     WHERE c.parent_id = <SOME_ID>;

When user is in some category, we would like to show him path to this category. So he could easily move to some parent category., _(*12)

    SELECT c.*
      FROM categories c
INNER JOIN categories_tree t on c.id = t.parent_id
     WHERE c.id = 4
  ORDER BY t.depth DESC;