1

Temat: Zapytanie hierarchiczne (id, parent_id)

Witam.
Może zacznę od opisu problemu.
potrzebuję pobrać z bazy dane z których utworzę menu w aplikacji.
Aplikacja korzysta z ACL dlatego pozycje w menu poiwnny się znaleźć tylko takie do których uzytkownik ma dostęp.
Menu jest dwu-poziomowe.
Czasem użytkownik może mieć dostęp do strony zarówno na pierwszym poziomie jak i do podstron. Innym razem może mieć dostęp tylko do podstron i wtedy muszę mu wygenerować na pierwszym poziomie przycisk bez linku natomiast na drugim poziomie przycisk z linkiem.

Wykombinowałem sobie, ze w bazie utworzę dwie tabele: resources, oraz roles_vs_resources.
poniżej definicje

CREATE TABLE resources
(
  id integer NOT NULL DEFAULT nextval('menu_id_seq'::regclass),
  id_parent integer,
  label character varying(30) NOT NULL DEFAULT 'menu'::character varying,
  module character varying(20),
  controller character varying(20),
  "action" character varying(20),
  parameters character varying(100),
  description character varying(100),
  CONSTRAINT pkey_menu PRIMARY KEY (id),
  CONSTRAINT unique_controller_action UNIQUE (controller, action),
  CONSTRAINT unique_module_controller_action UNIQUE (module, controller, action)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE resources OWNER TO brif;
CREATE TABLE roles_vs_resources
(
  id serial NOT NULL,
  id_role integer NOT NULL,
  id_resource integer NOT NULL,
  CONSTRAINT pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE roles_vs_resources OWNER TO brif;

Chodzi o to, że chce pobrać z bazy tylko te zasoby do których użytkownik ( rola ) ma dostęp plus te które są rodzicem dla pozycji z dostępem. Dodam, że użytkownik może mieć też dostęp do zasobu który nie ma dzieci.
Mam nadzieję, że dokładnie wyjaśniłem o co mi chodzi.

W oraclu można by to pewnie zrobić jakimś zapytaniem hierarchicznym ,ale w PG nie mam pojęcia jak do tego dojść. Kombinowałem z subselektami, ale nie wychodzi.
Moze ktoś ma jakiś pomysł ?

Pozdrawiam

Postgres 8.4 | PHP 5.3 | Kubuntu 10.04

2

Odp: Zapytanie hierarchiczne (id, parent_id)

Jakoś tak

with recursive zapytanie(<TU WYMIEN PO KOLEI KOLUMNY Z RESOURCE>) as(
     select rs.* from resources rs, roles_vs_resources rr where
               rs.id=rr.id_resource and rr.id_role=<IDENTYFIKATOR ROLI>   
     union
     select rs.* from resources rs, zapytanie zp where
               rs.id=zapytanie.id_parent
)
select * from zapytanie;

3

Odp: Zapytanie hierarchiczne (id, parent_id)

Dzięki rski
Generalnie działa.
Musiałem to jednak troche zmodyfikować, ponieważ:
w menu mogą zdarzać się sytuacje, że user nie będzie miał dostępu do strony na pierwszym poziomie a jedynie na drugim, ale żeby mieć taka możliwość , to musi przejść przez poziom pierwszy dlatego zapytanie trzeba trochę zmodyfikować

na poczatku zrobiłem to tak

with recursive zapytanie(checked, id, id_parent, label, module, controller) as(
     select CASE when rr.id_resource = rs.id then 'yes' else 'no' end as checked,rs.*, rr.id_resource from resources rs, roles_vs_resources rr where
               rs.id=rr.id_resource and rr.id_role=3   
     union
     select CASE when zp.id_resource = rs.id then 'z union yes' else 'z union no' end as checked, rs.*, '00' as id_resource from resources rs, zapytanie zp where
               rs.id=zp.id_parent
)
select * from zapytanie
order by id, id_parent;

Jednak w tym przypadku dostawałem "zdublowane" rekordy dotyczące tej samej pozycji (union)
Następnie case wyrzuciłem poza "zapytanie" i wyszło mi coś takiego

with recursive zapytanie(id) as(
     select rs.* from resources rs, roles_vs_resources rr where
               rs.id=rr.id_resource and rr.id_role=3   
     union
     select rs.* from resources rs, zapytanie zp where
               rs.id=zp.id_parent
)
select CASE when rvr.id_resource = z.id then 'yes' else 'no' end as checked,
z.id, z.id_parent, z.label, z.module, z.controller, z.action, z.parameters 
from zapytanie z 
left join roles_vs_resources rvr on z.id = rvr.id_resource and rvr.id_role=3
order by id, id_parent;

I to daje wynik taki o jaki mi chodziło. Być może dałoby się to jeszcze jakoś zoptymalizować, ale jak dla mnie , to już wystarczy.

jeszcze raz dziękuję i pozdrawiam
Gaza

Postgres 8.4 | PHP 5.3 | Kubuntu 10.04