Dotaz: MySQL - Select řádků i s NULL hodnotami

[Luke]

Ahoj, trochu zápasím s dotazem na výpis práv uživatele ke každému presenteru a jeho view.

Mám tyto 4 tabulky:

system_roles - Obsahuje skupinu práv uživatelů (admin, uzivatel, technik, redaktor, personalista ...)

id | name
1, admin
2, user
3, technician
...

system_modules - Jsou názvy presenterů (Homepage, Users, Helpdesk, Property ...)

id | name
1, homepage
2, users
3, helpdesk
...

system_modules_views - Jsou akce jednotlivých presenterů (View, Edit, Add, Export, ShowTree ...)

id | module_id | name | description
1, 1, show, Zobrazit hlavni stranku
2, 2, showList, Zobrazit seznam uzivatelu
3, 2, show, Zobrazit detail uzivatele
4, 2, edit, Editovat uzivatele
5, 2, delete, Smazat uzivatele
6, 3, add, Pridat dotaz
7, 3, showList, Zobrazit seznam vsech dotazu
...

system_acl - Je tabulka, která určuje zda role má, nebo nemá právo pro daný presenter a jeho view

id | role_id | module_id | view_id | access
1, 2, 1, 1, 1 # uživatel ve skupině users může zobrazit hlavní stránku
2, 2, 3, 6, 1 # uživatel ve skupině users může přidat dotaz do helpdesku
3, 2, 3, 7, 0 # uživatel ve skupině users nemůže zobrazit seznam všech dotazů v helpdesku
...

A teď o co mi jde...

Potřeboval bych pro konkrétní roli (WHERE role_id = x) vypsat všechny moduly z tabulky system_modules a jejich views (system_modules_views) a k nim joinout tabulku system_acl tak, aby pokud nebude pro daný presenter a jeho view v acl tabulce záznam se přesto ukázal řádek ač s NULL hodnotami. V mé aplikaci kontroluji, zda záznam v tabulce system_acl je a zda se access == 1 - tím uživatel má pro danou akci právo. Pokud v tabulce záznam není, nebo se access == 0, tak je přístup zamítnut. Teď ale potřebuji všechna tato práva vypsat na stránku jako přehled (a k nim pak přibude tlačítko buď Povolit/Zakázat podle toho, čemu se bude rovnat access hodnota - ta bude buď 1, 0 nebo NULL).

Pokud vezmu jako příklad má ukázková data shora, tak pro roli user (ID = 2) by měl výsledek vypadat následovně:

module_id | module_name | view_id | view_name | view_description | access
1, homepage, 1, show, Zobrazit hlavni stranku, 1
2, users, 2, showList, Zobrazit seznam uzivatelu, NULL # tento zaznam v system_acl vubec neni, proto NULL
...

Doufám, že je to vůbec možné takto uzpůsobit. Z části mi to už funguje, ale jen pro některé role Nebo se vůbec nebráním jinému řešení, pokud je toto složité...

Děkuji všem za pomoc.

---

---

Odpovědi

[rADOn]

LEFT JOIN

[Luke]

Příloha:

• role.png (80561 bytů)

Left join používám a zkoušel jsem to s ním, ale nejde mi to tak, jak bych si představoval. Přikládám svůj dotaz:

$this->database->query('SELECT system_modules.name AS module_name, 
  system_modules.header AS module_header, 
  system_modules_views.name AS view_name, 
  system_modules_views.header AS view_header, 
  system_acl.id, system_acl.role_id, 
  system_modules.id AS module_id, 
  system_modules_views.id AS view_id, 
  IFNULL(system_acl.allowed, 0) AS allowed, 
  IFNULL(system_acl.editable, 1) AS editable 
  FROM system_modules 
  JOIN system_modules_views ON system_modules_views.module_id = system_modules.id 
  LEFT JOIN system_acl ON system_modules.id = system_acl.module_id 
  AND system_modules_views.id = system_acl.view_id
  WHERE system_acl.role_id = %i 
  OR system_acl.role_id IS NULL 
  ORDER BY system_modules_views.id ASC', $role_id)->fetchAll();

V tabulce system_acl mám teď na zkoušku 4 záznamy jen pro roli user. Když vypisuji dle výše zmíněného dotazu práva právě pro roli user, tak mi vše funguje jak má - DB vrátí ty 4 záznamy + přidá chybějící řádky s NULL. Ale když vypíšu data pro jinou roli, která v ACL nemá žádný záznam, tak mi to místo samých NULL vypíše pouze ty chybějící řádky s NULL, které se předtím doplnili u výpisu role user a zbytek chybí. Možná pro lepší pochopení přikládám screen.

[Kit]

To ti likviduje ta podmínka WHERE, která se vztahuje na celý dotaz. Zkus ji dát do poddotazu s tabulkou system_acl a teprve na ten aplikovat LEFT JOIN.

[Luke]

Příloha:

• sql2.png (28025 bytů)

Ok, dotaz jsem upravil následovně:

SELECT t1.id AS module_id,
  t1.name AS module_name, 
  t1.header AS module_header,
  t2.id AS view_id, 
  t2.name AS view_name, 
  t2.header AS view_header 
  FROM system_modules AS t1
  JOIN system_modules_views AS t2 ON t2.module_id = t1.id 
  LEFT JOIN (SELECT system_acl.id, system_acl.role_id, system_acl.module_id, system_acl.view_id, 
    IFNULL(system_acl.allowed, 0) AS allowed, 
    IFNULL(system_acl.editable, 1) AS editable 
    FROM system_acl 
    WHERE system_acl.role_id = 5 OR system_acl.role_id IS NULL) AS t3
  ON t1.id = t3.module_id AND t2.id = t3.view_id 
  ORDER BY t2.id ASC

Výsledek je vždy 7 řádků - všechny moduly a jejich view, přesně jak to má být. Nicméně sloupec allowed, který je v poddotazu se vůbec nezobrazuje, viz. příloha.

[Luke]

Tak už to mám :)

Výsledek:

SELECT t1.id AS module_id,
  t1.name AS module_name, 
  t1.header AS module_header,
  t2.id AS view_id, 
  t2.name AS view_name, 
  t2.header AS view_header,
  IFNULL(t3.allowed, 0) AS allowed,
  IFNULL(t3.editable, 1) AS editable 
  FROM system_modules AS t1
  JOIN system_modules_views AS t2 ON t2.module_id = t1.id 
  LEFT JOIN (SELECT system_acl.id, system_acl.role_id, system_acl.module_id, system_acl.view_id, system_acl.allowed AS allowed, system_acl.editable AS editable    
    FROM system_acl 
    WHERE system_acl.role_id = 5 OR system_acl.role_id IS NULL) AS t3
  ON t1.id = t3.module_id AND t2.id = t3.view_id 
  ORDER BY t2.id ASC

Ještě pár věcí upravím a celé to důkladně otestuji, ale první pokusy vypadají funkčně. Děkuji za pomoc!

[j]

Mozna by trochu prehlednejsi bylo tu podminku dat primo do joinu - ale jde spis o osobni preference (nesnasim subquery).

LEFT JOIN system_acl ON system_modules.id = system_acl.module_id and (system_acl.role_id = 5 OR system_acl.role_id IS NULL)

(a nejsem si tak uplne jistej, jestli mysql tuhle syntax zvlada, ale myslim ze jo)