id
object_table
object_id
parent_object_table
parent_object_id
depth

object_table = 'table_c'
object_id = 15
parent_object_table = 'table_b'
parent_object_id = 12
depth = 1

object_table = 'table_c'
object_id = 15
parent_object_table = 'table_a'
parent_object_id = 3
depth = 2

object_table = 'table_c'
object_id = 15
parent_object_table = 'table_c'
parent_object_id = 15
depth = 0

SET @table_associations = 'pa_associations';

SET @associations_create = CONCAT(
    'CREATE TABLE IF NOT EXISTS ',
    @table_associations,
    ' (
        `ancestorName` varchar(255) CHARACTER SET utf8 NOT NULL,
        `ancestorId` int(11) UNSIGNED NOT NULL,
        `descendantName` varchar(255) CHARACTER SET utf8 NOT NULL,
        `descendantId` int(11) UNSIGNED NOT NULL,
        `depth` int(11) UNSIGNED NOT NULL,
        PRIMARY KEY (`ancestorName`, `ancestorId`, `descendantName`, `descendantId`)
    ) ',
    ' ENGINE=InnoDB DEFAULT CHARSET=utf8'
);

PREPARE statement FROM @associations_create;
EXECUTE statement;

-- Here's an example query to acquires *all* user ancestors given a permission ID of 1:

      SELECT * FROM pa_accounts AS accounts
      JOIN pa_associations AS associations
          ON accounts.id = associations.ancestorId
      WHERE associations.descendantId = 1
          AND associations.descendantName = 'pa_permissions'
          AND associations.ancestorName = 'pa_accounts';

  -- Here's an example query that inserts a relationship between the 4th role and the 10th permission:

      INSERT INTO pa_associations (ancestorName, ancestorId, descendantName, descendantId, depth)
      SELECT
          ancestorName,
          ancestorId,
          'pa_permissions',
          10,
          depth + 1,
      FROM pa_associations
      WHERE descendantId = 4 AND descendantName = 'pa_roles'
      UNION ALL SELECT 'pa_permissions', 10, 'pa_permissions', 10, 0;