[MySQL (& PHP)] nested search

[Niakmo]

Ik heb het volgende probleem. Ik heb een tabel met nested folders met de volgende velden:

id, parent_id, folder_name

Daarnaast is er een tabel met objecten die zich in een bepaalde folder bevinden dus onderandere de velden.

id, folder_id

Nu zou ik graag met zo min mogelijk queries id's van objecten kunnen ophalen door te zoeken naar de fodler_name van een bepaalde folder. Dus als je zoekt naar bijv folderX dat je dan alle id's van objecten die zich in deze folder en in zijn subfolders bevinden.

je kan nu de twee tabellen joinen op folder_id maar dan heb je nog niet de subfoldersa
Is dit mogelijk of ontkom ik er niet aan om een apparte zoek tabel te maken.

[kingmuze]

Je hebt twee mogelijkheden:

De gemakkelijkste: In PHP een recursieve functie maken.
De moeilijkere: DE hele tabel structuur e.d. omgooien. Google maar naar recursive mysql.

[Janoz]

Naast de bovenste twee zijn er nog 2 waarbij de tabelstructuur zo kan blijven.
-Mogelijkheid 3:
Een soort recursie waarbij je niet door de hele tree gaat wandelen, maar een query per level uitvoert. Je begint met je eerste folder ophalen. Vervolgens houd je bij welke folders er in zitten en laat je een nieuwe query los. Hierbij zoek je niet in 1 folder, maar in alle eerder gevonden folders tegelijk (met IN). Dit doe je tot er geen folders op dat level meer gevonden worden.

Het aantal queries blijft hiermee beperkt tot de diepte van de structuur en is niet afhankelijk van het aantal folders.

-Mogelijkheid 4 (Alleen goed mogelijk wanneer het aantal folders redelijk beperkt is):
De folder structuur in 1 query ophalen en vervolgens binnen PHP opbouwen.

[LuCarD]

Mogelijkheid 5:

Volgens mij is dit gewoon een Hierachical Tree. En daar bestaan al een aantal oplossing voor. Zoals deze:
http://www.sitepoint.com/article/hierarchical-data-database

[Niakmo]

het probleem is dat de eigenlijke zoek functie veel groter is, de objecten hebben nog properties in een andere tabel, en ze kunnen getagged worden, dus je bent uiteindelijk 5 tabbellen aan het doorzoeken. Vandaar dat ik elk onderdeel zo geoptimalisserd wil doen. Mogelijkheid 3 van janoz klinkt heel erg logisch maar in hoeverre is dit alleen met sql uit te voeren.

even surrogaat code in stappen

SQL:

--zoek eerst alle parents op:
SELECT id FROM folder WHERE folder_name LIKE '%zoekwoord%';
--voor elke parent zoek je recursief de children op, geen idee of dit in mysql kan
--dan heb je een lijst van folder_id's deze kan je weer op de objecten tabel gooien
SELECT id FROM object WHERE folder_id IN ('resultaat van vorige zoekopdracht') 

is dit allemaal mogelijk met alleen mysql?

[Janoz]

Het kan niet in MySQL alleen. Je zult een stuk php erbij moeten gebruiken. Waarom zou je het perse allemaal in SQL willen doen?

edit: Even scope narrower gemaakt nav opmerking hieronder

[ Voor 20% gewijzigd door Janoz op 08-04-2008 12:55 ]

[Verwijderd]

Janoz schreef op dinsdag 08 april 2008 @ 11:54:
Het kan niet in SQL alleen.

Nou niet kunnen is overdreven, het kan uiteraard wel maar de query wordt wel fors.
Zo kun je in oracle gebruik maken van sys_connect_by_path voor het betere knip en plak werk en ik mag toch aannemen dat MySQL iets soortgelijks kent. Daarmee kun je wel wat sjoemelen om alsnog alle ID's te verkrijgen binnen 1 enkele query. Het heeft natuurlijk niks meer met pragmatisme te maken

Maark het kan: dus methode 6?

[LuCarD]

Het is mogelijk met met methode 5
Net even getest in mijn database. Maar dan moeten er dus wel twee velden bij komen in de database.

tblFoldernaam: folder_id, parent_folder_id, order_links, order_rechts, foldernaam
tblFolderObjects: object_id, folder_id, ....

SQL:

select object.* from tblFolderObjects as object 
    inner join tblFolderNaam as folders on
        object.folder_id = folders.folder_id
    inner join tblFoldernaam as search on 
        folders.order_links between search.order_links and search.order_rechts

where search.foldernaam = 'PIET'

[ Voor 1% gewijzigd door LuCarD op 08-04-2008 12:54 . Reden: opmaak ]

[xces]

mptt is cool (en snel, als je het begrip goed snapt)

[Niakmo]

LuCarD schreef op dinsdag 08 april 2008 @ 12:51:
Het is mogelijk met met methode 5
Net even getest in mijn database. Maar dan moeten er dus wel twee velden bij komen in de database.

tblFoldernaam: folder_id, parent_folder_id, order_links, order_rechts, foldernaam
tblFolderObjects: object_id, folder_id, ....

SQL:

select object.* from tblFolderObjects as object 
    inner join tblFolderNaam as folders on
        object.folder_id = folders.folder_id
    inner join tblFoldernaam as search on 
        folders.order_links between search.order_links and search.order_rechts

where search.foldernaam = 'PIET'

dit is inderdaad wel een optie, alleen moet wel met hele folder class herschreven worden. Vooral om de volgorde binnen een folder aan te passen is een stuk ingewikkelder nu.

[ Voor 5% gewijzigd door Niakmo op 08-04-2008 14:19 ]

[LuCarD]

Niakmo schreef op dinsdag 08 april 2008 @ 14:16:
[...]


dit is inderdaad wel een optie, alleen moet wel met hele folder class herschreven worden. Vooral om de volgorde binnen een folder aan te passen is een stuk ingewikkelder nu.

Tja, dan word het natuurlijk wel een stuk lastiger. Hoe diep kan de boom worden ?

Maar je hebt het over een class die folder structuur beheerd? Hoe vaak worden er veranderingen binnen de folderstructuur gedaan? Toevoegen aan de folderstructuur is redelijk simpel, verwijderen ook.

Ik maak vaak zelf gebruik van een routine die simpel weg de hele boom opnieuw aanmaakt, die routine is relatief kostbaar in processortijd en query tijd. Maar aangezien die niet zo vaak ( 1x maand is veel ) wordt aangeroepen dan vallen die kosten bij mij te overzien.

[Niakmo]

De boom word meestal niet dieper dan 4 lagen voor de huidige toepassing. Het punt is dat binnen elke folder ik de voglorde van de mappen wil aanpassen dus de eigenlijke velden momenteel zijn:

id parent_id name position

waarin position de positie is binnen die folder.

Ik ben nu aan het kijken of ik die positie weg kan laten als ik gebruik ga maken van die left en right om de positie te bepalen maar dan moet ik mijn hele ajax interface aanpassen dus dat word hem niet. Als ik nu een folder volgorde aanpas roep ik een sort method op met sort($folderId, $sortArray) die dan voor elke child een waarde van 0 tot x heeft voor de volgorde.

[LuCarD]

Niakmo schreef op dinsdag 08 april 2008 @ 15:23:
De boom word meestal niet dieper dan 4 lagen voor de huidige toepassing. Het punt is dat binnen elke folder ik de voglorde van de mappen wil aanpassen dus de eigenlijke velden momenteel zijn:

id parent_id name position

waarin position de positie is binnen die folder.

Ik ben nu aan het kijken of ik die positie weg kan laten als ik gebruik ga maken van die left en right om de positie te bepalen maar dan moet ik mijn hele ajax interface aanpassen dus dat word hem niet. Als ik nu een folder volgorde aanpas roep ik een sort method op met sort($folderId, $sortArray) die dan voor elke child een waarde van 0 tot x heeft voor de volgorde.

Hoe moet ik die position zien?

Diepte? Volgorde in de boom?

[Niakmo]

positie binnen een laag dus:

parent
child2
child4
child3
child1

de volgorde van die childs is aanpasbaar dus nu hebben ze bijv respectivelijk id: 153, 155,160 en 162 en hun position zijn bijv 0, 1, 3 en 2


edit:
wat ik verder nog tegen kwam: als ik mijn eindpunt weten en ik wil alle parents weten moet ik nogsteeds mijn recursieve manier gebruiken. Als ik bijv de path naar Banana wil weten zodat er Food>Fruit>Yellow>Banana staat moet ik toch alles weer ophalen en met php ordenen.



Het enige voordeel wat deze methode heeft is dat je makkelijk met 1 query de tree kan ophalen wat ik verder nooit doe omdat ik alleen maar de childs ophalen die ik wil hebben dus ik kan volgens mij het best de oude en nieuwe manier combineren.

[ Voor 60% gewijzigd door Niakmo op 08-04-2008 16:48 ]

[Coju]

Niakmo schreef op dinsdag 08 april 2008 @ 15:51:


edit:
wat ik verder nog tegen kwam: als ik mijn eindpunt weten en ik wil alle parents weten moet ik nogsteeds mijn recursieve manier gebruiken. Als ik bijv de path naar Banana wil weten zodat er Food>Fruit>Yellow>Banana staat moet ik toch alles weer ophalen en met php ordenen.

code:

SELECT WHERE left<8 AND right >9

Pad van banana terug naar food in een query.

[Pete]

Coju schreef op dinsdag 08 april 2008 @ 23:29:
[...]

code:

SELECT WHERE left<8 AND right >9

Pad van banana terug naar food in een query.

Op die manier is je pad nog niet gesorteerd, dit is dus beter:

code:

SELECT WHERE `left`<8 AND `right` >9 order by `left`

(die lelijke backticks moeten echt gebruikt worden als je left en right als kolomnamen neemt)

[Verwijderd]

phsmit schreef op woensdag 09 april 2008 @ 09:10:
(die lelijke backticks moeten echt gebruikt worden als je left en right als kolomnamen neemt)

Snapt MySql dan niet iets als table_naam.left en table_naam.right?

[Peedy]

Verwijderd schreef op woensdag 09 april 2008 @ 10:56:
[...]
Snapt MySql dan niet iets als table_naam.left en table_naam.right?

Dat wel, maar los niet, omdat je left en right ook gebruik in LEFT JOIN en RIGHT JOIN (bijv). Zo moet order als tablenaam ook als `order` worden geschreven en zo is het met nog wel meer. Als je het doet met table_naam.left heeft MySQL wel door dat het om een tablecolumn gaat en niet om een query element.

[ Voor 13% gewijzigd door Peedy op 09-04-2008 11:50 ]

[Voutloos]

Je kan ook gewoon die kolomnaam beschrijvender maken. LeftNode, LeftSibling, LeftFooBar, LeftWistikutmaar...

Btw, er is ook een functie LEFT().

[ Voor 15% gewijzigd door Voutloos op 09-04-2008 12:20 ]

[Verwijderd]

Peedy schreef op woensdag 09 april 2008 @ 11:50:
Dat wel, maar los niet,

Ja ik begrijp nog wel lichtelijk enigszins de logica erachter hoor Het ging mij meer om de opmerking dat je magic quotes zou moeten gebruiken en dat is dus niet zo want er zijn meerdere wegen naar Rome. Sowieso vind ik de tabel benoemen een zeer goede gewoonte aangezien het bijdraagt een de leesbaarheid.

bijv:

code:

...
from person, group
where person.name = '...' and group.name = '...'
...

[remcotolsma]

Kun je met mogelijkheid 5 ook de objecten vinden binnen een bepaalde folder? Als je bijvoorbeeld de volgende opzet hebt:

• map 1 (objecten: 3, 5)
  • submap 1.1 (objecten: 1, 2, 3)
  • submap 1.2 (objecten: 2, 3, 6)
• map 2 (objecten: 1, 2)
  • submap 2.1 (objecten: 7, 10, 11)
    • submap 2.1.1 (objecten: 9, 8, 2)
  • submap 2.2 (objecten: 1, 4, 7)
  • submap 2.3 (objecten: 32, 2, 16)
  • submap 2.4 (objecten: 2, 3, 6)
• map 3 (objecten: 5, 6)

en het enige gegeven is een pad:
map 2 » submap 2.1 » submap 2.1.1

kun je dan met 1 query objecten 9, 8 en 2 terug krijgen?

En hoe dom is het om de volgende tabel structuur te gebruiken:

code:

id     path
1      map 1
2      map 2/submap 2.1
3      map 2/submap 2.1/submap 2.1.1
4      map 2/submap 2.2
5      map 2/submap 2.3
6      map 2/submap 2.4
7      map 3

Je kunt dan ook vrij eenvoudig zoeken met de LIKE operator.

SQL:

SELECT `id` FROM `table` WHERE `path` LIKE 'map 2/submap 2.1%'

Of is dat een hele zware operatie met heel veel records?

[Orphix]

remcotolsma schreef op woensdag 09 april 2008 @ 21:03:
En hoe dom is het om de volgende tabel structuur te gebruiken:
[..]
[/code]
Of is dat een hele zware operatie met heel veel records?

Dat is zeker geen domme methode. Het wordt ook wel 'materialized path' genoemd (ff googlen) en ik vind het persoonlijk heel prettig werken. En ook simpeler dan de nested set oplossingen. Je kan idd met de LIKE operator razendsnel (via een index) de gegevens ophalen. Tevens zit er meer informatie in de materialized path geencodeerd zoals de hierarchie-diepte en alle ancestors. Die kun je ook gebruiken in je applicatie zonder elke keer weer terug te hoeven naar de database.