CODEWITHSUNDEEP
postgresqljoinrecursion
Nelson Jean Pierre
Nelson Jean Pierre

Reputation: 281

query recursive and others tables in postgresql?

I have a little problem to make a recursive query and I can’t to extract the data which I need.

I have 3 tables (menu, type_role, privilege)

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

I think I should use WITH RECURSIVE but I have not worked with this kind of recursive queries

Upvotes: 2

Views: 479

Answers (1)

Andomar
Andomar

Reputation: 238216

Here's a recursive query that loops up the parent entries.

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
;

This prints:

 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. The example is for Technical, as Administrator includes every row.

Upvotes: 1

Related Questions