Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006

PHP/MySQL - Kategorisystem

Tjena!

Har ett problem vi brottats med ett tag nu. Det är ganska omfattande, så jag ska försöka göra det så kortfattat som möjligt.

Vi håller på med en hemsida där användare ska kunna välja sina kompetenser. Varje användare kan alltså ha flera kompetenser, och varje kompetens kan ha x antal kategorier - antalet kategorieroch levlar kan variera hos olika kompetenser.

De största kraven är enligt följande:
- Enkelt kunna lägga till/ta bort kompetenser och dess kategorier.
- Möjlighet att få fram alla huvudkategorier.
- Kunna få fram alla kategorier hos en kompetens.
- Kunna ha olika många levlar kategorier hos en kompetens.
- Enkelt att byta namn/plats etc. hos olika kompetenser.

Och så ska förstås systemet vara logiskt och inte ta mer prestanda än nödvändigt. Vi har tänkt igenom 3 olika metoder för att åstadkomma detta, och jag tänkte presentera den jag tycker är bäst.

Då har vi följande tabeller i databasen:
- users(id, username)
- competences(id, parent, name)
- list_competences(user_id, competence_id)

Och så tar vi lite exempeldata...

Olle har följande kompetenser:
Administration->Arbetsledning
Språk->Tolk->Polska
Filosofi

Pelle har följande kompetenser:
Hantverke->Snickeri
Språk->Tolk->Arabiska, Persiska

I det här fallet ser databasen kanske ut så här:

I tabellen competences ligger alltså själva kompetenserna som refereras till i list_competences. Kolumnen parent anger till vilken kategori kompetensen tillhör - om kompetensen inte tillhör till någon kategori eller är en huvudkategori så är detta satt till 0.

Så att lista alla huvudkategorier är inga konstigheter:

SELECT id, name FROM competences WHERE parent = 0;

Men hur gör jag för att lista huvudkategoriernas respektive underkategorier? Och hur listar jag upp användarnas kompetenser insorterat i sina kategorier? Det största problemet är som sagt att olika kompetenser kan ha olika många kategorier/levlar.

Kanske det går att skapa en MySQL-funktion som loopar igenom kompetenserna?

Bara att säga till om något är oklart. Var inte blyg för att komma med tips!

Tack på förhand!

Trädvy Permalänk
Legendarisk
Hedersmedlem
Plats
::1
Registrerad
Dec 2002

Rekommenderar att du tar en titt på nested sets istället för att bara lagra {id,parent} i databasen. Det är lite mer att sätta sig in i men det löser alla dina problem.

http://dev.mysql.com/tech-resources/articles/hierarchical-dat...

Abstractions all the way down.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006

Okej, det låter intressant. Ska kika lite på det.

Tack!

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006

Nu har jag läst igenom hela sidan. Måste säga att det är en smart idé men med sjukt långa queries - är inte det här påfrestande för databasen? Och är metoden "säker", dvs. det finns väl ingen risk för att någon av de många momenten skiter sig och ställer till med problem i hela tabellen?

Trädvy Permalänk
Legendarisk
Hedersmedlem
Plats
::1
Registrerad
Dec 2002

Om du bara kör alla queries som behövs för att t.ex. flytta noder i ett sånt system efter varandra och en av dom krashar blir du sittande med halvt uppdaterade tabeller. Man får vara noga med att scriptet inte kan stanna mitt i pga felaktig input, kontrollera att operationen är genomförbar, och kanske även kika på transaktioner om det är ett alternativ för din databas.

Att läsa från tabellen är däremot mycket smidigare iom att du inte behöver loopa queryn för varje nivå eller bygga ett stort träd manuellt. Om du har indexerat kolumnerna som behövs för att hämta t.ex. alla undernoder är det lika snabbt som att köra en av de vanliga frågorna.

Nu sova.

Abstractions all the way down.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006

Okej, hur skulle en sådan kontroll se ut då?
Man vill ju vara säker på att inget kan gå snett.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006

Nu har jag lekt runt en del med nested sets, och det fungerar väldigt bra. Men en enorm nackdel jag stötte på var att man inte kan sortera kategorierna i bokstavsordning. Kanske du kan ett smart sätt för det? Om det nu inte går så skulle jag vilja veta hur man får in datan i en array så att man kan sortera kategorierna där. Har sökt runt en hel del men utan vettiga resultat.
Sorteringen är ett mycket viktigt moment inom just detta område.

Och så är jag nyfiken på hur man hämtar alla huvudkategorier.

Tack på förhand.

Trädvy Permalänk
Legendarisk
Hedersmedlem
Plats
::1
Registrerad
Dec 2002

Du har hela tiden en sortering för trädet sparad i tabellen. Om du bara behöver ha noderna i alfabetisk ordning får du leta upp rätt syskon att placera en nod brevid när du ändrar i tabellen. Vill du ha olika sorteringar kan du antingen ha kolumner med alternativa ordningar eller sortera manuellt per nivå i php.

För att hämta ut huvudkategorier eller bara en nivå ner brukar jag lagra idt till föräldranoden också, det är lätt att uppdatera när man gör ändringar och underlättar i de fallen.

När du flyttar noder måste du kontrollera att du inte flyttar en nod till en av sin egna undernoder och allt den nya föräldranoden och eventuella syskon är rätt placerade mot varandra.

Det underlättar att knacka ihop ett par klasser för att hantera träden och bygga arrayer åt dig. Sorry för det korta svaret, måste kila.

Abstractions all the way down.

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Jun 2004

Om detta är hjälp med en produktion, rekommenderar jag att du letar upp en abstraktionsklass som klarar denna och all annan databashantering åt dig. Det finns flera olika, jag har ingen erfarenhet av de stora utan har bara kört abstraktionsklass för enklare behov (ingen hierarkisk data), så någon som har mer erfarenhet av det får tipsa dig. Annars finns Google.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006
Citat:

Ursprungligen inskrivet av Biberu
Du har hela tiden en sortering för trädet sparad i tabellen. Om du bara behöver ha noderna i alfabetisk ordning får du leta upp rätt syskon att placera en nod brevid när du ändrar i tabellen. Vill du ha olika sorteringar kan du antingen ha kolumner med alternativa ordningar eller sortera manuellt per nivå i php.

För att hämta ut huvudkategorier eller bara en nivå ner brukar jag lagra idt till föräldranoden också, det är lätt att uppdatera när man gör ändringar och underlättar i de fallen.

När du flyttar noder måste du kontrollera att du inte flyttar en nod till en av sin egna undernoder och allt den nya föräldranoden och eventuella syskon är rätt placerade mot varandra.

Det underlättar att knacka ihop ett par klasser för att hantera träden och bygga arrayer åt dig. Sorry för det korta svaret, måste kila.

Du har inget att ursäkta, ditt svar var högst uppskattat.

Jo en klass för detta har jag, och det fungerar jättesmidigt; för tillfället har jag $data_comp->add($parent, $node) och $data_comp->tree(). När jag fått upp de funktioner som behövs och sett till att allt fungerar som det ska så ska jag även lägga in de här långa anropen i stored procedures.

Det enda jag behöver sortera efter är egentligen namnen. Om man bara ska en generation (a.k.a. "djup" eller "level") så lär det ju inte vara några konstigheter. Men på ett ställe (där man lägger till en ny kompetens) ska jag presentera alla kompetenser och dess underkategorier, och då är jag beroende av att de ligger i ordning då det annars blir väldigt rörigt att hitta rätt. Vill inte fråga alltför många frågor, men just det här skulle jag uppskatta väldigt mycket om jag kunde få hjälp med; antingen, som du nämnde, ladda in datan till en multidimensionell array (ingen aning om hur det skulle gå till i nested sets, men det kanske går att använda den nya kolumnen parent du tipsade om eller trolla lite med depth) eller lösa det på något annat sätt.

Citat:

Ursprungligen inskrivet av azoapes
Om detta är hjälp med en produktion, rekommenderar jag att du letar upp en abstraktionsklass som klarar denna och all annan databashantering åt dig. Det finns flera olika, jag har ingen erfarenhet av de stora utan har bara kört abstraktionsklass för enklare behov (ingen hierarkisk data), så någon som har mer erfarenhet av det får tipsa dig. Annars finns Google.

Lite osäker med exakt vad du menar. Det här är inget jag kommer att tjäna pengar på, om det är det du syftar på.
Jag tycker om att bygga allt från grunden då man lättare håller koll på vad som är vad (möjligen ha andra koder som referens) om det nu inte rör sig om större bibliotek, ex jQuery.

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Jun 2004
Citat:

Ursprungligen inskrivet av Ivarska
Lite osäker med exakt vad du menar. Det här är inget jag kommer att tjäna pengar på, om det är det du syftar på. Jag tycker om att bygga allt från grunden då man lättare håller koll på vad som är vad (möjligen ha andra koder som referens) om det nu inte rör sig om större bibliotek, ex jQuery.

Med produktion menar jag att du har stora krav på att få ihop detta, så snabbt och stabilt som möjligt. Då är ett effektivt sätt att använda ett färdigskrivet och färdigtestat abstraktionslager som tar hand om databashanteringen åt dig. Ett av de bättre har jag hört ska vara detta: http://adodb.sourceforge.net/

P.S. jQuery är litet i sammanhanget

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006
Citat:

Ursprungligen inskrivet av azoapes
Med produktion menar jag att du har stora krav på att få ihop detta, så snabbt och stabilt som möjligt. Då är ett effektivt sätt att använda ett färdigskrivet och färdigtestat abstraktionslager som tar hand om databashanteringen åt dig. Ett av de bättre har jag hört ska vara detta: http://adodb.sourceforge.net/

P.S. jQuery är litet i sammanhanget

Hm, okej. Får nog kika på det om jag inte får till det själv (vilket jag faktiskt skulle föredra).

Anledningen till att jag tog jQuery som exempel är att det är ett av de få biblioteken (oavsett språk) jag tycker är ruggigt bra.

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Jun 2004
Citat:

Ursprungligen inskrivet av Ivarska
Hm, okej. Får nog kika på det om jag inte får till det själv (vilket jag faktiskt skulle föredra).

Ja det är det bästa, men har man bara ett antal timmar till sitt förfogande har ett abstraktionslager flera fördelar, bl.a. är det färdigtestat och man får en god konsekvens alltigenom sin applikation. Men som sagt, om detta är ett hobbyprojekt är det mycket bättre att skapa sin egna abstraktionsklass, som är skräddarsydd efter applikationens behov. På ett webbföretag är det dock lite dålig stil att göra så, eftersom man då ganska effektivt gör sig till en beroende del i vidareutvecklingen. Bra i lågkonjunktur, med andra ord

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006
Citat:

Ursprungligen inskrivet av azoapes
Ja det är det bästa, men har man bara ett antal timmar till sitt förfogande har ett abstraktionslager flera fördelar, bl.a. är det färdigtestat och man får en god konsekvens alltigenom sin applikation. Men som sagt, om detta är ett hobbyprojekt är det mycket bättre att skapa sin egna abstraktionsklass, som är skräddarsydd efter applikationens behov. På ett webbföretag är det dock lite dålig stil att göra så, eftersom man då ganska effektivt gör sig till en beroende del i vidareutvecklingen. Bra i lågkonjunktur, med andra ord

Är väl just det man använder kommentarer till, om man nu inte verkligen vill skapa ett beroende.
Lite svårt att beskriva projektet. Kan PM:a länk till sidan om du är intresserad.

Men återigen någon, hur laddar man in data från nested sets till en multidimensionell array? Hoppas på ett svar från Biberu igen, hehe.

Trädvy Permalänk
Medlem
Plats
i din garderob
Registrerad
Sep 2007

Ett alternativ är kanske att dumpa hela tabellen med SELECT * FROM tabell och spara ner raderna i en array som man sedan bygger XML-noder från? Jag vet inte hur mycket långsammare detta blir, om ens något eftersom frågan i sin ursprungliga form bygger på rekursion.

Man skulle kunna låta exempelvis PHP cacha resultatet till en .XML-fil som används så länge databasen inte har uppdaterats.

Bilanaloger är som Volvo — varenda svenne kör med dem

Trädvy Permalänk
Medlem
Plats
Göteborg
Registrerad
Jun 2004
Citat:

Ursprungligen inskrivet av Ivarska
Är väl just det man använder kommentarer till, om man nu inte verkligen vill skapa ett beroende.

Jojo, men en annan utvecklare ska ju kunna hoppa in när upphovsmannen är sjuk och utföra ett supportärende eller en uppdatering. Om de båda kan med ett ramverk underlättar det mycket. Nu ska jag sluta posta, tillför ändå inget. Men jag har måste-svara-tvångstankar.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006
Citat:

Ursprungligen inskrivet av Teknocide
Ett alternativ är kanske att dumpa hela tabellen med SELECT * FROM tabell och spara ner raderna i en array som man sedan bygger XML-noder från? Jag vet inte hur mycket långsammare detta blir, om ens något eftersom frågan i sin ursprungliga form bygger på rekursion.

Man skulle kunna låta exempelvis PHP cacha resultatet till en .XML-fil som används så länge databasen inte har uppdaterats.

Jo jag funderade faktiskt på det i början, men det känns onödigt. Man kan väl lika gärna serializa arrayn och spara den i en textfil, känns som att det skulle gå snabbare och samtidigt ta betydligt mindre plats. Men eftersom att alla kompetenser kan ha olika många kategorier så vet jag inte riktigt hur jag skulle göra med looparna.

Får testa mig fram lite, men jag fortfarande uppskatta om någon skulle ha en lösning för detta.

Trädvy Permalänk
Medlem
Plats
i din garderob
Registrerad
Sep 2007

Fördelen med ett XML-dokument är att du kan använda XSLT för att omvandla det till HTML som du vill. Det blir också lättare att parsa en specifik gren med undernoder, och det finns färdiga verktyg för att göra detta.

Fast jag gillar approachen med en serialiserad array också Det lär ju fungera så länge den inte blir för stor.

edit: för att förtydliga; jag menar alltså inte att XML-filen ska innehålla en rå-dump av table-datan, utan att den ska innehålla den färdiguträknade strukturen -- det du önskar att databasen hade kunnat leverera. Det blir en större engångsberäkning men när den är klar kan du hoppa omkring i nodträdet bäst du vill

Bilanaloger är som Volvo — varenda svenne kör med dem

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006
Citat:

Ursprungligen inskrivet av Teknocide
Fördelen med ett XML-dokument är att du kan använda XSLT för att omvandla det till HTML som du vill. Det blir också lättare att parsa en specifik gren med undernoder, och det finns färdiga verktyg för att göra detta.

Fast jag gillar approachen med en serialiserad array också Det lär ju fungera så länge den inte blir för stor.

edit: för att förtydliga; jag menar alltså inte att XML-filen ska innehålla en rå-dump av table-datan, utan att den ska innehålla den färdiguträknade strukturen -- det du önskar att databasen hade kunnat leverera. Det blir en större engångsberäkning men när den är klar kan du hoppa omkring i nodträdet bäst du vill

Det förstod jag.
Ska leka mig fram lite.

Trädvy Permalänk
Medlem
Plats
Stockholm
Registrerad
Sep 2006

Nu känns det som att jag kommit en bit på vägen med att sortera datan. Så här ser funktionen ut just nu:

function tree_sorted() { //Get data $query = 'SELECT node.id, node.name, node.parent, (COUNT(parent.name) - 1) AS depth FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft'; $result = mysql_query($query) or die(mysql_error()); //Fetch gotten data while($data = mysql_fetch_assoc($result)) { $fetched[$data['depth']][$data['id']] = array($data['name'], $data['parent']); } //Sort fetched data foreach ($fetched as $i => $row) { asort($row); $sorted[$i] = $row; } //Merge sorted data foreach($sorted as $i => $arr) { foreach($arr as $x => $row) { $returnarray[] = array('id' => key($row), 'name' => $row[0], 'depth' => $x); } } //Return merged data return $returnarray; }

Tanken är att få ut datan som följande: array(id, name, depth).
Precis som i min funktion nedan:

function tree() { $query = 'SELECT node.id, node.name, (COUNT(parent.name) - 1) AS depth FROM test_competence AS node, test_competence AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt GROUP BY node.name ORDER BY node.lft'; $result = mysql_query($query) or die(mysql_error()); while($data = mysql_fetch_assoc($result)) { $returnarray[] = $data; } return $returnarray; }

För tillfället så blir alla kompetenser sorterade i tree_sorted(), men även insorterade i vilken nivå de ligger på (depth).
Därför undrar jag om någon skulle vilja hjälpa mig bli klar med funktionen? Osäker på hur jag ska göra härnäst för att placera underkategorierna efter sin parent, precis som i funktionen tree().