[sql] tags recursief tree queryen.

[killercow]

Ik ben bezig met een gallery systeem welke aan een afbeelding een x aantal tags kan hangen.

De afbeeldingen worden dus allermaal getagd (zoals in de weblog wereld gebruikelijk is met verhalen.)

De tags gebruik ik als volgt:

ik lees alle tags uit, gesorteerd op het aantal afbeelding die er aan hangen.
Als je dan op een tag klikt moet je alle afbeeldingen van deze tag te zien krijgen, dit is natuurlijk niet zo ingewikkeld. Omdat een afbeelding echter meer dan een tags kan hebben moeten echter alle tags die nog beschikbaar zijn onder die hoofd-tag ook weer zichtbaar worden als sub-selecties.

Tags:

Id	Tag
1	vakantie
2	huis
3	zweden

Afbeeldingen:

Id	Name
1	vakantie-foto
2	vakantie2-foto
3	zweden
4	woonkamer

Koppel tabel: (afbeeldingen_tags)

Id	tag	afbeelding
1	3	3
2	2	2
3	2	1
4	1	3
5	2	4

Nu zou er ongeveer zo'n structuur zichtbaar moeten worden:

code:

Vakanties
     - zweden
Thuis
Zweden

En ik gebruik de volgende code om de eerste laag op te halen:

SQL:

select tags.tag,tags.id as id,count(afbeeldingen.id) as count 
from tags,afbeeldingen_tags,afbeeldingen 
where afbeeldingen_tags.tag=tags.id and afbeeldingen_tags.afbeelding=affbeeldingen.id 
group by tags.id

En dan voor een laag naar beneden:

SQL:

select
 tags.tag,tags.id as id,count(afbeeldingen.id) as count 
from tags,afbeeldingen_tags,afbeeldingen
 where afbeeldingen_tags.tag=tags.id and afbeeldingen_tags.afbeelding=affbeeldingen.id and (afbeeldingen_tags.tag=1) and (tags.id!=1) 
group by tags.id

Maar dat kan natuurlijk niet!

ik zit dus met het probleem dat ik alle tags wil vinden die gekoppeld zijn aan afbeeldingen die ook gekoppeld zijn aan de gegeven tag(s), maar niet de gekozen tag(s) zijn/is.
Iemand enig idee?


Als ik "afbeeldingen_tags.tag=tags.id and" uit de query laat koppeld hij natuurlijk niet goed, maar tjah.

SQL:

select tags.tag,tags.id as id,count(afbeeldingen.id) as count 
from tags,afbeeldingen_tags,afbeeldingen 
where  afbeeldingen_tags.afbeelding=affbeeldingen.id and (afbeeldingen_tags.tag=1) and (tags.id!=1) 
group by tags.id

[joopst]

ik denk dat je hem gemakkelijker kan oplossen door hem in 2-en te doen.

1. 1 foto ophalen met alle tags behalve 1

als dat lukt

dan die query joinen aan

2. foto's ophalen bij 1 tag

[killercow]

joopst, snap ik jouw niet, of jij mij niet?
(hoe bedoel je precies?)

Ik wil dus m;n foto's gewoon taggen ipv in mapjes gooien.
En daarna wel door de tags kunnen browsen.

De beste gevulde tags komen bovenaan gesorteerd, en als je op zo'n tag klikt krijg je alle foto's van die tag, en de tags die dan daar onder nog mogelijk zijn. (zodat je steeds specifieken kan zoeken)

[joopst]

ik snap wat jij bedoelt volgens mij :-)
een voorbeeld van een site die dat heeft: http://www.etoys.com/Results.html/N/2269/WSSN/PCSoftware

kan je steeds je search narrow-en met beschikbare 'tags'

ik zal eens proberen of ik iets van sql kan pritsen. ... (geen belofte hoor )

[joopst]

de truuk is volgens mij dat je twee keer de koppeltabel erin joint

code:

-- select alle sub-tags
SELECT distinct SubTag.Tag FROM Tag AS SubTag
-- die als sub-tag gekoppeld zijn
JOIN AfbeeldingTag AS subAfbtag ON subAfbtag.TagId = SubTag.TagId
-- aan afbeeldingen
JOIN Afbeelding afb ON subAfbtag.AfbeeldingId = afb.AfbeeldingId
-- die gekoppeld zijn
JOIN AfbeeldingTag AS afbtag ON afbtag.AfbeeldingId = afb.AfbeeldingId
-- aan tag 2
WHERE afbtag.TagId = 2

dan kan je zelf wel de sortering en dat je geen 'lege' subtags eruit wilt hebben erbij typen.

[killercow]

joopst,

Ja dat lijkt inderdaad te werken, lijkt me heerlijk om zo m'n foto's en andere media files te managen.
Eerste stuk werkt nu, maar uhm hoe verder met 2 tags? gewoon de laatste join nogmaals herhalen?
(still trying to wrap my head around it)

[joopst]

je wilt alle foto's die aan 2 tags voldoen...
... dat worden minder foto's ... dus... de where uitbreiden ?

[killercow]

yup idd, gewoon met een or lijkt me.. (anyway, ik ga eerst je query even visualizen, en daarna weer lompe dingen roepen )

iig muchos gracias!

[Woy]

Wil je dat je tree in een keer opgebouwd wordt of wil je pas laten zien welke eronder zitten als er op geklikt wordt.

In dat laatste geval kun je idd gewoon door middel van een join/where clause de extra tags selecteren.

Als je de hele tree in een keer op wilt bouwen en dan ook meteen voor een oneindige diepte dan kan je denk het best even naar dit topic kijken. EfBe in [VB.NET] Treeview + Subnodes (van Subnodes)

[joopst]

de where moet je met een AND uitbreiden he,
anders krijg je meer foto's ipv minder

[killercow]

yup, was ik ook achter

Ik kom er echter nog niet helemaal uit, er gaat iets mis.

Als ik deze query uitvoer krijg ik geen afbeeldingen te zien als ik

code:

'SELECT distinct gal.name
            FROM tags AS SubTag
            JOIN tags_galleries AS subGaltag ON subGaltag.tag = SubTag.id
            JOIN galleries AS gal ON subGaltag.gallery = gal.id
            JOIN tags_galleries AS galtag ON galtag.gallery = gal.id
            where galtag.tag=1 and galtag.tag=3

Terwijl er een gallery is die aan beide tags gebonden is.

Als ik maar een tag gebruik gaat het wel goed, en krijg ik wel de juiste selectie terug.

ZIe hier de gebruikte queryies;
http://cashcannon.com/members/index.php

Gallery 1 hangt aan Tags, 1 en 3
Gallery 2 hangt aan Tags, 1 en 4

[joopst]

code:

-- selecteer all subtags van alle afbeeldingen
-- (uit de subset van afbeeldingen die ook al voldoen aan tag-level 1 
---(en twee dus ook, omdat level1 uit level2 is gequeried))
SELECT distinct Subtag.Tag FROM Tag AS SubTag
JOIN AfbeeldingTag AS subAfbtag ON subAfbtag.TagId = SubTag.TagId

-- selecteer alle afbeeldingen gekoppeld aan tag-level 1
-- (uit de subset van afbeeldingen die ook al voldoen aan tag-level 2)
JOIN Afbeelding afb_l1 ON subAfbtag.AfbeeldingId = afb_l1.AfbeeldingId
JOIN AfbeeldingTag AS level1 ON level1.AfbeeldingId = afb_l1.AfbeeldingId
AND level1.TagId = 2

-- selecteer alle afbeeldingen gekoppeld aan tag-level 2
JOIN Afbeelding afb_l2 ON level1.AfbeeldingId = afb_l2.AfbeeldingId
JOIN AfbeeldingTag AS level2 ON level2.AfbeeldingId = afb_l2.AfbeeldingId
AND level2.TagId = 3

[killercow]

yay!, geweldig.

Eens kijken of ik er een gtk client van kan bakken. (mooie type-manager)

[joopst]

ik ben benieuwd wat je gaat klussen, als het werkt wil ik het wel graag zien