Diskuze: Vyhledání dle počtu záznamů z jiné tabulky

Aktivity

Martin Konečný (pavelco1998)

Tvůrce

Martin Konečný (pavelco1998):12.9.2016 12:20

zdravím,
potřebuji radu od zkušenějších databázistů ohledně jednoho SQL dotazu. Mám tyto tři tabulky

[user] (
  id INT,
  name VARCHAR
)

[resource] (
  id INT,
  section VARCHAR,
  name VARCHAR
)

// vazební tabulka,  vazba mezi user-resource je M:N
[user_resource] (
  id INT,
  user_id INT,
  resource_id INT
)

V tabulce resource budu mít tři záznamy
section = "User", name = "add"
section = "User", name = "edit"
section = "User", name = "delete"

Nyní potřebuji sebrat všechny uživatele, kteří mají záznamy daných resources, které jsou vypsány v IN(). Udělal jsem si k tomu takovýhle dotaz:

SELECT * FROM user
INNER JOIN user_resource ON user_resource.user_id = user.id
INNER JOIN resource ON user_resource.resource_id = resource.id
WHERE
  resource.section IN ("User", "User", "User") AND
  resource.name IN ("add", "edit", "delete")
GROUP BY user.id
HAVING COUNT(resource.id) = 3

Pokud uživatel nebude mít všechny tyto tři záznamy v tabulce user_resource (tzn. ty, kde resource je "add", "edit" a "delete"), tak ho z vyhledání vyřadí.
Pokud nějaký uživatel bude mít víc resources, tak ho to taky vyhledá - musí mít minimálně ty tři, které jsou v tom dotazu. Naopak se nesmí stát, že by to vybralo uživatele, který by některý z vyjmenovaných resources neměl.

Dokázal byste mi někdo říct, jestli to obsahuje nějakou zásadní chybu? Párkrát jsem to zkoušel a zdá se to funkční, ale je možné, že jsem nevyčerpal všechny možnosti.
Snad je to nějak srozumitelně napsaný, vysvětluje se mi to docela složitě.

Díky

Odpovědět

12.9.2016 12:20

Aktuálně připravuji browser RPG, FB stránka - https://www.facebook.com/AlteiraCZ

Martin Konečný (pavelco1998)

Tvůrce

Martin Konečný (pavelco1998):12.9.2016 12:25

Jen podotýkám, že to HAVING COUNT(resource.id) = 3 mi má ověřit, že se všechny tři záznamy z tabulky resource shodují a že uživatel dané resources má. Použil jsem to snad poprvý v životě, takže si nejsem jistý, jestli to funguje tak, jak si myslím

Nahoru Odpovědět

12.9.2016 12:25

Aktuálně připravuji browser RPG, FB stránka - https://www.facebook.com/AlteiraCZ

Paul

Člen

Paul:13.9.2016 15:48

Principielně to máš správně. Je třeba ještě ale použít distinct nad tím sloupcem, kde počítáš count. Když ho nepoužiješ a uživatel bude mít více stejných resources (např. 2krát "add"), tak ho to zahrne do výsledku.
Navíc bys měl mít v having podmínce ">=" namísto "=", protože říkáš že chceš i ty, který by jich měli víc.
Když vyjmenováváš hodnoty v klauzuli IN, stačí tam hodnotu mít jen jednou ("User").
Já bych to napsal takhle:

select user.id, user.name, count(distinct resource_name) from user
join user_resource on user.id = user_resource.user_id
join resource on user_resource.resource_id = resource.id
where resource.section = 'User'
and resource.name in ('add', 'edit', 'delete')
group by user.id, user.name
having count(distinct resource_name) >= 3

Editováno 13.9.2016 15:49

Nahoru Odpovědět

13.9.2016 15:48

Martin Konečný (pavelco1998)

Tvůrce

Martin Konečný (pavelco1998):13.9.2016 15:55

Díky za radu já prvně si nebyl jistý, jestli to vůbec jedním dotazem půjde vyřešit, nakonec se tedy zdá, že to jde.
Já ty resources dostanu jednotlivě v poli, takže jsem to raději řešil přes IN(), kam se postupně nacpou všechny ty hodnoty. Takže když některé ty resources budou mít stejnou sekci nebo název, vypíše se tam vícekrát. Tím, doufám, pořeším možnost, že kdyby byly např.:

//  sekce : název
User:add
User:edit
Article:add
Article:edit

tak pokud bude uživatel mít User:add, ale už ne Article:add, aby mi to toho uživatele nebralo včetně.

Nahoru Odpovědět

13.9.2016 15:55

Aktuálně připravuji browser RPG, FB stránka - https://www.facebook.com/AlteiraCZ

Děláme co je v našich silách, aby byly zdejší diskuze co nejkvalitnější. Proto do nich také mohou přispívat pouze registrovaní členové. Pro zapojení do diskuze se přihlas. Pokud ještě nemáš účet, zaregistruj se, je to zdarma.

Zobrazeno 4 zpráv z 4.