Keskustelu: Nettisivujen teko: SQL useita ehtoja samasta sarakkeesta

Moikka,

Tarvitsen nyt muita näkökulmia tähän asiaan kun itellä on jotenkin pää tukossa tämän kanssa eikä parin päivän tauko ratkaissut asiaa :)
Eli aloin iltojen iloksi tekemään käyttäjän ja käyttäjäryhmien hallintaa jonka kautta annetaan oikeuksia tässä tapauksessa ilmoitustauluun.

Tauluja löytyy

groups	// olemassa olevat ryhmät
	id	group_name
	1	Ryhmä1
	2	Ryhmä2

user_groups	// mihin ryhmiin käyttäjä kuuluu
	id	user_id	group_id
	1	1		1
	2	1		2
	3	2		2

privileges	// mitä oikeuksia ryhmällä on
	id	group_id	privilege
	1	1			1000
	2	1			1001
	3	1			1101
	4	2			1000
	5	2			1001

Ja mahdollisia oikeuksia on

	1001	Näkee tämän ryhmän ja itse tehdyt ilmoitukset
	1002	Näkee kaikkien ryhmien ilmoitukset
	1101	Voi tehdä ilmoituksia tälle ryhmälle
	1102	Voi tehdä ilmoituksia kaikille ryhmille (mitkä näkevät ilmoituksia)

	1000	"Näkee jotain ilmoituksia" tulee automaattisesti jos 1001 tai 1002 on olemassa

1001 ja 1002 oikeuksien kohdalla annetaan myös automaattisesti oikeus 1000 eli "näkee jotain ilmoituksia" että pääsee ilmoitustauluun.
Tämän jälkeen vasta tutkitaan että mitä saa nähdä (1001 vai 1002).

Muuten toimii kivasti mutta ilmoitusta lisättäessä on hankaluuksia hakea kannasta mihin ryhmiin käyttäjä voi ilmoituksensa lisätä.
Käyttäjä kun voi kuulua useampaankin ryhmään.

Esimerkki tilanne
Pekka (id 1) on ryhmissä ryhmä1 ja ryhmä2
Antti (id 2) on vain ryhmässä ryhmä2

Ryhmä1 näkee oman ryhmänsä ja itse tekemät ilmoitukset ja voi tehdä ilmoituksia tälle ryhmälle (1001 ja 1101 (myös se 1000))

Ryhmä2 näkee oman ryhmänsä ja itse tekemät ilmoitukset mutta ei voi tehdä ilmoituksia. (1001 (ja se 1000))

Eli Pekka näkee kummankin ryhmän ilmoitukset mutta voi lisätä ilmoituksia vain ryhmään ryhmä1.
Antti ei näe kuin ryhmä2:n ilmoitukset.

Tällä hetkellä queryt näyttää tältä
Jos löytyy oikeus 1102 eli voi tehdä kaikille ryhmille ilmoituksia (jotka näkee ilmoitustaulun = 1000)

SELECT groups.*
FROM   groups,
       user_groups,
       privileges
WHERE  privileges.group_id = groups.id
       AND privileges.privilege = 1000
GROUP  BY groups.id

Tähän asti toimii..
ja jos löytyy vain oikeus 1101 niin lisätään edellisen queryn where kohtaan vielä

AND user_groups.user_id = ?
AND user_groups.group_id = groups.id

Tämä toimii muuten ok mutta missään ei tarkisteta ryhmä2:n olematonta oikeutta eli ettei siihen ryhmään voi sen ryhmän oikeuksilla lisätä ilmoituksia.

Jossain tuntuu muutenkin menevän näiden oikeuksien kanssa epäloogisesti ja kuten jo sanoinkin niin pää tukossa ;)
Näkeekö kukaan mitään omituista näissä?

edit, päivitin taulut esimerkkitapauksen mukaiseksi

No sanoisin, että ensinnäkin ainakin minusta nuo kyselyt on helpompi hahmottaa jos sidokset määrittää suoraan from-kohdassa, eikä WHEREssä. Olet kyllä muotoillut kyselyt siististi, joten sen puolesta ne on helppo hahmottaa, mutta kun taulut ja niiden väliset sidokset määritellään eri paikoissa, niin ei näe suoraan jos jostain puuttuu sidos kokonaan.

Ensimmäisessä kyselyssä et määrittele minkäänlaista sidosta groups ja user_groups taulun välille, joten tuloksena on karteesinen tulo.

Lisäksi ryhmittelet groups.id:n perusteella, mutta et määrittele mitään perusteluita miten muut sarakkeet valitaan. Monissa muissa tietokantamoottoreissa tästä tulisi virheilmoitus, mutta MySQL ottaa ensimmäisen (tai viimeisen) vastaantulevan rivin arvot noihin muihin. Eli saat vain yhden rivin per groups.id, mutta muiden rivien arvot on mitä sattuu. Tässä tapauksessa ne tosin on siitä samasta taulusta, joten saat tosiaan vain yksittäisiä rivejä groups -taulusta. Oikea tapa tehdä asia olisi kuitenkin tässä tapauksessa DISTINCT eikä tuollainen group by.

Usein myöskin se että edes tarvii DISTINCTiä viittaa siihen että tietokannan datassa on jotain hämärää tai kysely on virheellinen (esim. juuri puuttuva sidos)

Yhteenvetona sanoisin, että ylipäätään noi kyselyt saattaa nyt toimia "hyvin" kun on tuuria, mutta varmemmin ne saisi toimimaan jos ne tekisi oikein.

Jos haluat löytää kaikki ryhmät, joissa oikeus 1000 niin

SELECT
	DISTINCT groups.*
FROM
	groups
	JOIN privileges ON groups.id = privileges.group_id
WHERE
	privileges.privilege = 1000

Jos haluat löytää kaikki ryhmät joissa on määritelty sekä oikeus 1101 ja 1000 sekä joissa käyttäjä ? on mukana

SELECT
	DISTINCT G.*
FROM
	groups G
	JOIN privileges A ON G.id = A.group_id AND A.privilege=1000
	JOIN privileges B ON G.id = B.group_id AND B.privilege=1101
	JOIN user_groups U ON G.id = U.group_id
WHERE
	U.user_id = ?

Noissa edellä olevissa voi normaalitilanteessa jättää tuon DISTINCTinkin pois, esimerkiksi laittamallasi datalla antaa saman tuloksen ilmankin.

Oletko ajatellut, että voisit heittää roskiin nuo epämääräiset luvut ja käyttää ENUM-tyyppiä? Silloin olisi selvää, mitä eri oikeudet tarkoittavat. Toinen mahdollisuus olisi käyttää SET-tyyppiä, jolloin erillistä oikeustaulua ei edes tarvittaisi ja ryhmän oikeudet mahtuisivat yhteen sarakkeeseen. Kyselyissä voisi sitten käyttää sellaisia selviä ehtoja kuin FIND_IN_SET('kirjoitus_kaikille', oikeudet).

Asiaan. Kyselysi ovat ihan kummallisia siihen nähden, mitä ongelman kuvauksesta ymmärsin. Eivätkö oikeat kyselyt ole tällaiset:

-- Tarkistetaan, onko oikeus kirjoittaa kaikille.
SELECT 1
FROM user_groups AS ug
JOIN privileges AS p ON p.group_id = ug.id AND p.privilege = 'write_all'
WHERE ug.user_id = ?
LIMIT 1

-- Jos äskeisestä saatiin ykkönen, voidaan kirjoittaa kaikille.
SELECT groups.*
FROM groups
JOIN privileges AS p ON p.group_id = groups.id AND p.privilege = 'read'

-- Muuten voidaan kirjoittaa vain omiin ryhmiin, joissa on kirjoitusoikeus.
SELECT groups.*
FROM groups
JOIN user_groups AS ug ON ug.group_id = groups.id AND ug.user_id = ?
JOIN privileges AS p ON p.group_id = ug.id AND p.privilege = 'write_own'

Tuli nyt väännettyä myös tällainen all-in-one-kysely:

-- Halutaan tietää, mihin ryhmiin viestin voi lähettää.
SELECT DISTINCT dest_group.*

-- Kerätään kaikki lukukykyiset kohderyhmät.
FROM groups AS dest_group
JOIN privileges AS dest_priv ON dest_priv.group_id = dest_group.id AND dest_priv.privilege = 'read'

-- Kerätään kaikki käyttäjän ryhmät oikeuksineen.
JOIN user_groups AS src_user_groups ON src_user_groups.user_id = ?
JOIN groups AS src_group ON src_group.id = src_user_groups.group_id
JOIN privileges AS src_priv ON src_priv.group_id = src_group.id

-- Täytyy olla joko laaja oikeus tai ryhmäoikeuden ohella oikea ryhmä.
WHERE
	src_priv.privilege = 'write_all'
	OR (src_priv.privilege = 'write_own' AND src_group.id = dest_group.id)

Sama onnistuu myös monella muulla tavalla, mm. näin:

SELECT groups.*

-- Kohderyhmät.
FROM groups
JOIN privileges AS pr ON pr.group_id = groups.id AND pr.privilege = 'read'

-- Käyttäjän kirjoitusoikeus kohderyhmään sen jäsenenä.
LEFT JOIN user_groups AS ug ON ug.group_id = groups.id AND ug.user_id = ?
LEFT JOIN privileges AS pw ON pw.group_id = ug.group_id AND pw.privilege = 'write_own'

WHERE
	-- Joko ryhmän jäsenenä oikeus tai yleisesti oikeus kaikkeen.
	pw.privilege IS NOT NULL
	OR EXISTS (
		SELECT 1
		FROM user_groups AS ug
		JOIN privileges AS p ON p.group_id = ug.id AND p.privilege = 'write_all'
		WHERE ug.user_id = ?
	)

Olen periaatteessa samaa mieltä tuosta enum-hommelista, mutta käytännön syistä ainakin itselläni on usein tietokannan päässä juuri noita numeroita.

Se johtuu siitä, että en tee tietokantakyselyitä käsin suoraan MySQL:ään, joten minulle ei ole mitään iloa siitä, että MySQL:n päässä on "selväkieliset" enumit. Ohjelmointikielen kannalta ne olisi vaan merkkijonoja, ja se taas ei tue muuta kuin kokonaislukuja enumien avaimina. Eli kantaan tallentuu kokonaislukuja, mutta ORMissa sarakkeet on määritelty enumeiksi, jolloin koodin puolella ne näkyy selväkielisinä.

Huoh, taas huomaa että on hetki aikaa vierähtänyt näistä hommista joka onkin yksi syy siihen että projektia aloin tekemään..

En tiedä miksi aloin tässä taulujen sidoksia tekemään where kohdassa, täytyy tarkistaa kaikki muutkin queryt vastaavien varalta.

Epämääräisiin numeroihin päädyin sen takia koska käyttäjän oikeudet haen tunnistamisen yhteydessä taulukkoon käyttäen oikeutta avaimena ($user['privileges'][oikeus] = true) jolloin on helppo tarkistaa aina tarvittaessa onko käyttäjällä oikeutta vai ei. Tosin nyt metabolixin viestistä tajusin että voinhan käyttää avaimena myös tekstiä esimerkiksi juuri write_all (huoh..). Mieluummin näin kuin etsiä oikeutta esim in_arrayn avulla, right? Eiköhän nämä hommat taas palaudu mieleen kunhan vauhtiin pääsee.

Pitänee kirjotella paljon hommia uudestaan. Jo tässä vaiheessa ollut hankaluuksia nimenomaan noiden numeroiden kanssa että mikä tarkoitti mikäkin.

Ennen en ole ENUMia käyttänytkään joten pitää siihen tutustua. Onko se järkevin vaihtoehto vaikka eri oikeuksien määrä kasvaisikin suuremmaksi? Jos tästä ottais ikuisuusprojektia :)

Thänks, pitää kattella noita tarkemmin huomenna. Nyt lauantai-iltaa ja saunaan!

edit, tekis mieli käyttää kovempaa kieltä mutta täällä on nuorempaakin kaveria joten sanotaan vaan että kyllä ottaa päähän nyt nuo numerot.. ;)

omasta profiilista kirjoitti:

Voin olla hyvinkin pitkiä aikoja tekemättä mitään kunnes taas palaan tähän tuskalliseen maailmaan.

ENUM on tietokannassa suunnilleen vastaava asia kuin ohjemoinnissa nimetyt vakiot. Sitä on siis fiksua käyttää, kun arvoilla on sanallisesti ilmaistavissa olevat merkitykset. ENUM tallennetaan tietokantaan fyysisesti lukuna mutta se toimii kyselyissä tekstinä.

Tietysti on asia erikseen, jos ohjelmoi sellaisessa ympäristössä, joka sisältää omat kikkansa näitä varten, kuten Grez äsken selitti.

Jeps, otin tuon ENUMin käyttöön ja on kyllä paljon selkeämpää nyt.

Tutustuin myös SET-tyyppiin että voisi sitä FIND_IN_SETtiä käyttää ja se olisi ehkä meikäläiselle helpompi kun pääsisi yhdestä taulusta kokonaan eroon mutta suorituskyky taitaisi laskea sitten isommilla määrillä oikeuksia jos innostuu kehittelemään. Tuskimpa merkittävästi mutta enivei.. Eikä tässä muutenkaan ole tarkotus mennä sieltä mistä aita on matalin ;)

Tuli tarkistettua muutkin käytössä olevat kyselyt ja huomaa että oli monen taulun järkevät kyselyt vähän päässyt unohtumaan..

Mutta kiitos!