sql - 使用 Bookshelf 的 Postgres 物化路径搜索

Question

假设我正在使用物化路径来存储管理链:

  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}

如何在给定用户 ID 的情况下构造一个返回他所有直接下属的查询，例如，给定中层经理，它应该返回 Cubicle Slave 和看门人，给定高级副总裁，它应该返回中层经理。换句话说，什么是获取所有记录的好方法，其中 management_chain 包含在倒数第二个位置查询的 id(假定最后一项代表用户自己的 id)。

换句话说，我如何表示以下 SQL:

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

我目前的 JS:

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

出了哪些错误

ReferenceError: id 未定义

Answer 1

假设 management_chain 是一个整数数组 (int[])，您可以执行以下操作(在普通 SQL 中)

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

这是有效的，因为 array_to_string() 不会将分隔符附加到字符串的末尾。因此，如果路径包含序列 /2/，则意味着该路径“下方”有更多节点。 2 是 management_chain 中最后一个 id 的节点将以 /2 结尾(没有尾随 /)并且不会包含在结果中。

表达式不会使用索引，因此这对于大型表可能不可行。

但是我不知道这将如何转化为那个 JS 东西。

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