Postgres Materialized Path Search using Bookshelf

Question

Let's say I am using materialized paths to store management chains:

  Table: User
  id           name             management_chain
  1            Senior VP        {1}
  2            Middle Manager   {1,2}
  3            Cubicle Slave    {1,2,3}
  4            Janitor          {1,2,4}

How do I construct a query given a user id that returns all of his direct reports, eg given the middle manager, it should return Cubicle Slave and Janitor, given the Senior VP it should return the Middle Manager. Put another way, what would be a good way to get all records where the management_chain contains the id queried for at a position that is second to last (given that the last item represent the user's own id).

In other words, how do I represent the following SQL:

SELECT *
FROM USER u 
WHERE u.management_chain @> {stored_variable, u.id}

My current JS:

 var collection = Users.forge()
    .query('where', 'management_chain', '@>', [req.user.id, id]);

Which errors out with

ReferenceError: id is not defined

score 1 · Answer 1 · answered Oct 24 '14 at 20:36

Assuming management_chain is an integer array (int[]) you could do the following (in plain SQL)

select *
from (
  select id,
         name,
         '/'||array_to_string(management_chain, '/') as path
  from users
) t
where path like '%/2/%';

This works, because array_to_string() will not append the delimiter to the end of the string. Therefore if a path contains the sequence /2/ it means there are more nodes "below" that one. The nodes where 2 is the last id in the management_chain will end with /2 (no trailing /) and will not be included in the result.

The expression will not make use of an index, so this might not be feasible for large tables.

However I don't know how this would translate into that JS thing.

SQLFiddle example: http://sqlfiddle.com/#!15/75948/2

score 0 · Answer 2 · answered Oct 24 '14 at 19:56

Lookup WITH RECURSIVE

As an example take a look a this code:

CREATE VIEW
    mvw_pre_import_cellpath_check
    (
        pkid_cell,
        id_cell  ,
        id_parent,
        has_child,
        id_path  ,
        name_path,
        string_path
    ) AS WITH RECURSIVE cell_paths
    (
        pkid_cell,
        id_cell  ,
        id_parent,
        id_path  ,
        name_path
    ) AS
    (
     SELECT
                tbl_cell.pkid                     ,
                tbl_cell.cell_id                  ,
                tbl_cell.cell_parent_id           ,
                ARRAY[tbl_cell.cell_id]   AS "array",
                ARRAY[tbl_cell.cell_name] AS "array"
           FROM
                ufo.tbl_cell
          WHERE
                (((
                            tbl_cell.cell_parent_id IS NULL)
                        AND (
                            tbl_cell.reject_reason IS NULL))
                    AND (
                        tbl_cell.processed_dt IS NULL))
  UNION ALL
     SELECT
                tbl_cell.pkid                             ,
                tbl_cell.cell_id                          ,
                tbl_cell.cell_parent_id                   ,
                (cell_paths_1.id_path || tbl_cell.cell_id),
                (cell_paths_1.name_path || tbl_cell.cell_name)
           FROM
                (cell_paths cell_paths_1
           JOIN
                ufo.tbl_cell
             ON
                ((
                        tbl_cell.cell_parent_id = cell_paths_1.id_cell)))
          WHERE
                (((
                            NOT (
                                tbl_cell.cell_id = ANY (cell_paths_1.id_path)))
                        AND (
                            tbl_cell.reject_reason IS NULL))
                    AND (
                        tbl_cell.processed_dt IS NULL))
    )
 SELECT
        cell_paths.pkid_cell,
        cell_paths.id_cell  ,
        cell_paths.id_parent,
        (
         SELECT
                    COUNT(*) AS COUNT
               FROM
                    ufo.tbl_cell x
              WHERE
                    ((
                            cell_paths.id_cell = x.cell_id)
                        AND (
                            EXISTS
                            (
                             SELECT
                                        1
                                   FROM
                                        ufo.tbl_cell y
                                  WHERE
                                        (
                                            x.cell_id = y.cell_parent_id))))) AS has_child,
        cell_paths.id_path                                                                ,
        cell_paths.name_path                                                              ,
        array_to_string(cell_paths.name_path, ' -> '::text) AS string_path
   FROM
        cell_paths
   ORDER BY
        cell_paths.id_path;

There are plenty more examples to find on SO when looking for recursive CTE. But in contrary with your example the top level cells (managers) have parent_id = NULL in my example. These are the starting points for the different branches.

HTH

I fail to see how this is at least remotely related to the table structure presented in the question. — , Oct 24 '14 at 20:36

Postgres Materialized Path Search using Bookshelf

2 Answers2