postgresql - 在 postgresql 中查询递归表和其他表？

Question

我在进行递归查询时遇到了一个小问题，我无法提取我需要的数据。

我有 3 个表(菜单、type_role、权限)

menu----------------------------------------------------------| id| parent_id |    tittle      | controller | action   || 1 |           | Users          |            |          || 2 |    1      | Create User    |  users     |  create  || 3 |    1      | Edit User      |  users     |  edit    || 4 |    1      | Show Users     |  users     |  show    || 5 |           | Contacts       |            |          || 6 |    5      | Create Contacs |  contacts  |  create  || 7 |    5      | Edit Contacts  |  contacts  |  edit    || 8 |           | Inventory      |            |          || 9 |    8      | Register Piece |  pieces    | register || 10|    8      | Show Pieces    |  pieces    | show     |

type_role---------------------| id|    role       |     | 1 | Administrator | | 2 | Technical     | | 3 | Operator      | 

privilege---------------------| role_id |  menu_id |     |    1    |    2     |  |    1    |    3     | |    1    |    4     ||    1    |    6     ||    1    |    7     ||    1    |    9     | |    1    |   10     ||    2    |    9     | |    2    |   10     ||    3    |    6     | |    3    |    7     ||    3    |   10     |

I need to extract:

id, parent_id, controller, action where the id of the type of role equals 1

I made the query and I get the data, but the query extract it parents who are not related to the type of user and I don't need this.

I just want to extract all the menu items and their parent by user type

SELECT menu.id,menu.parent_id,menu.controller,menu.action  
FROM privilege
INNER JOIN menu ON
menu.id = privilege.menu_id
INNER JOIN type_role ON 
type_role.id = privilege.role_id
WHERE type_role.id = 1
UNION
SELECT menu_recur.id,menu_recur.parent_id,menu_recur.menu_recur,menu_recur.action 
FROM menu menu_recur
INNER JOIN menu menutwo ON 
menu_recur.id = menutwo.parent_id  

我想我应该使用 WITH RECURSIVE 但我没有使用过这种递归查询

Answer 1

这是一个循环父条目的递归查询。

with    recursive list as
        (
        select  menu.id, parent_id, tittle, controller, action
        from    menu
        join    privilege
        on      menu.id = privilege.menu_id
        join    type_role
        on      type_role.id = privilege.role_id
        where   type_role.role = 'Technical'
        union all
        select  menu.id, menu.parent_id, menu.tittle, menu.controller, menu.action
        from    list
        join    menu
        on      menu.id = list.parent_id
        )
select  distinct *
from    list
order by
        id
;

这打印:

 id | parent_id |     tittle     | controller |  action  
----+-----------+----------------+------------+----------
  8 |           | Inventory      |            | 
  9 |         8 | Register Piece | pieces     | register
 10 |         8 | Show Pieces    | pieces     | show
(3 rows)

See it working at SQL Fiddle.该示例适用于 Technical，因为 Administrator 包括每一行。