[SQL] Hiërarchie attribuut maken op basis van ID en niveau

[jdh009]

Mijn vraag:
Mijn SQL kennis is beperkt tot de simpele SELECT, DELETE en UPDATE statements maar heb nog nooit echt gewerkt met vraagstukken die voorwaarden stellen/rekening houden met informatie buiten de eigen regel. Nu wil ik een nieuwe attribuut toevoegen die rekening houd en gebaseerd is op de regel(s) erboven.

ID/volgorde	tekst	soort	niveau
1	root	map	0
2	mapnaam1	map	1
3	mapnaam2	map	1
4	bestandnaam1	bestand	2
5	mapnaam3	map	1
6	bestandnaam2	bestand	2

Dus:

ID/volgorde	tekst	soort	niveau	path
1	root	map	0	root/
2	mapnaam1	map	1	root/mapnaam1/
3	mapnaam2	map	1	root/mapnaam2/
4	bestandnaam1	bestand	2	root/mapnaam2/
5	mapnaam3	map	1	root/mapnaam3/
6	bestandnaam2	bestand	2	root/mapnaam3/

In mijn hoofd klinkt dit vrij simpel maar krijg het dus niet voor elkaar om het in code om te zetten. Het is een soort CONCAT maar dan met een of meerdere waardes uit verschillende rows.

Relevante software en hardware die ik gebruik
MySQL

Wat ik al gevonden of geprobeerd heb
Ik heb gekeken of ik dit voorbeeld kon ombuigen naar een werkend alternatief voor mij maar ik mis de parent attribuut.

Mijn omslachtige manier die nog het meest in de buurt kwam van de oplossing was het dupliceren het attribuut "tekst" met het hoogste getal niveau + 1 (in dit gevak tekst1, tekst2,tekst3) de waarde van het verkeerde niveau eruit te gooien deze dan proberen samen te voegen met bovenstaande niveaus en dan een CONCAT uit te voeren. Waar ik dus nog mee bezig ben maar heel omslachtig werkt.

[RobIII]

Wat je wil is een veld parent_id welke verwijst naar het (joh) parent id van dat record. Alle records (desnoods noem je het tuples of rows/rijen, niet "regels") welke daar de waarde null bevatten zijn root(s). Zet een foreign key naar het ID en klaar. Het queryen van zo'n model, afhankelijk van je eisen, brengt ook wel wat uitdagingen met zich mee maar is met moderne SQL en een fatsoenlijk RDBMs prima te doen. Vooral bij MySQL is 't behoorlijk afhankelijk van welke versie je gebruikt. Over het algemeen, in een modern RDBMS, kun je een "CTE" gebruiken (MySQL voorbeeld).

Niveau kun je schrappen (kun je afleiden, redundant dus) en path moet dus je parent worden:

id	tekst	soort	parent
1	root	map	<null>
2	mapnaam1	map	1
3	mapnaam2	map	1
4	bestandnaam1	bestand	3
5	mapnaam3	map	1
6	bestandnaam2	bestand	5

En "id/volgorde"-veld is ook een slecht plan; als je de volgorde wil wijzigen moet je niet 't ID hoeven aanpassen. Maak daar een los veld van alsjeblieft mits je zelf 'handmatig' de volgorde überhaupt wil kunnen bepalen; volgens mij wil je gewoon sorteren op naam en heel die volgorde dus ook overbodig/redundant. En voor "soort" zou ik een "enum" of type-tabel gebruiken (of een enkele char - M voor map, B voor bestand of "D" voor directory en "F" voor file - of een int: "1" voor map en "2" voor bestand of...); in ieder geval géén tekst; kost veel meer opslagruimte dan nodig.

id	tekst	soort	parent
1	root	d	<null>
2	mapnaam1	d	1
3	mapnaam2	d	1
4	bestandnaam1	f	3
5	mapnaam3	d	1
6	bestandnaam2	f	5

Bovenstaand werkt in praktisch elk zichzelf respecterend RDBMS en kan zelfs zonder CTE's bevraagd worden (maar wat omslachtiger met bijv. self-joins). Maar je hebt ook RDBMS-specifieke oplossingen zoals dit bijvoorbeeld.

[ Voor 97% gewijzigd door RobIII op 16-09-2020 16:50 ]