[MYSQL] boomstructuur gaat fout in root

[Nielsz]

We hebben een media bibliotheek. Deze bibliotheek bevat directories en mediabestanden. Een mediabestand zit in een directory. Een directory heeft een parentID als het niet de rootmap is, en een parentID van 0 als het wel de rootmap is.

De database structuur is als volgt:

code:

CREATE TABLE `media_dir` (
  `dirID` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate utf8_bin NOT NULL default '',
  `parentID` int(11) NOT NULL default '0'
);

CREATE TABLE `media` (
  `mediaID` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate utf8_bin NOT NULL default '',
  `dirID` int(11) NOT NULL default '0'
);

De eerste directory is "root" met dirID = 1 en parentID = 0. In de database staan verder geen items met parentID 0.

Wat wil ik nu zien? Ik wil van een bepaalde directory zien welke subdirectories hij heeft, en van elke subdirectory wil ik weten hoeveel subdirectiries die weer bevat en hoeveel mediabestanden hij heeft.

Dat doe ik met de volgende query:

code:

                    select
                        
                            media_dir.dirID as media_dir_id,                    
                            media_dir.name as media_dir_name,
                            count(md.dirID) as aantal_dir,
                        count(m.mediaID) as aantal_files
                    from
                        media_dir
                    left join 
                        media_dir md
                            on (media_dir.dirID = md.parentID)
                    left join 
                        media m
                            on (media_dir.dirID = m.dirID)
                    where 
                        media_dir.parentID = 2
                    group by
                        media_dir.dirID                  
                    order by
                        media_dir.name
                    asc

Nu krijg ik netjes alle results terug. Alles prima. Behalve als ik alle subdirectories van de root dir wil bekijken. Dan moet ik dus alle directories ophalen die parentID = 1 hebben. Dan geeft hij voor zowel de directories als het aantal files een raar getal terug: subdircount * 3. Dus heb ik 2 files en 3 subdirectories, krijg ik toch allebei 9 terug. Als ik nu een van de 2 joins + count (maakt niet uit welke) weghaal, haalt hij de resterende gegevens wel goed op.

Het vreemde is hier nu dat het in alle gevallen goed gaat, behalve in de root dir.
update: Hij telt blijkbaar altijd verkeerd, niet alleen bij de root.
Iemand een idee?

[ Voor 4% gewijzigd door Nielsz op 29-08-2005 15:00 ]

[Gerco]

Je zou eens naar Nested Sets kunnen kijken. Het is niet alleen sneller bij relatief weinig updates, maar het is ook nog eens kinderlijk eenvoudig om een hele subtree op te halen met 1 select. Daar kun je dan uiteraard een COUNT() overheen halen als je dingen wilt tellen.

Je lukt dan alle mediafiles in een subtree tellen door zo te doen:

SQL:

SELECT COUNT(*)
FROM Dirs
LEFT JOIN Files ON Files.DirID = Dirs.DirID
WHERE Dirs.LeftIndex > RootLeft AND Dirs.RightIndex < RootRight

RootLeft en RootRight zijn dan de Left en Right index van de parent node waar je de subtree van wilt hebben.

[ Voor 34% gewijzigd door Gerco op 29-08-2005 15:11 ]

[Nielsz]

Dat zou eventueel een mogelijkheid zijn, maar ik zie dan nog niet in hoe ik die twee counts icm met de joins te kunnen laten werken. Op dit moment werkt het wel als ik een van beide joins doe, maar zodra ik ze combineer, dan gaat het fout.

Topictitle is dus eigenlijk verkeerd; het zou iets als "counts icm met 2 joins" moeten zijn oid.

[ Voor 3% gewijzigd door Nielsz op 29-08-2005 16:01 ]

[Janoz]

De denkfout die jij maakt is dat count het aantal rijen telt dat terug gegeven wordt en niet het aantal unieke voorkomens.

[ Voor 7% gewijzigd door Janoz op 29-08-2005 16:23 ]

[drm]

Volgens mij gewoon een foute grouping, waardoor de resultaten met elkaar vermenigvuldigd worden.

code:

where 
                         media_dir.parentID = 2
                    group by
                        media_dir.dirID

moet volgens mij zijn:

code:

where 
                         media_dir.dirID = 2
                    group by
                        media_dir.dirID

[Nielsz]

De huidige directory is = 2, en hiervan wil je submappen ophalen. Dat houdt dus in dat je alle submappen wilt hebben waarvan het parentID 2 is.

Ik denk dat Janoz wel in de buurt zit, maar hoe ik dat kan vertalen naar een andere query is een volgende

[ Voor 29% gewijzigd door Nielsz op 29-08-2005 17:11 ]

[drm]

Nielsz:
De huidige directory is = 2, en hiervan wil je submappen ophalen. Dat houdt dus in dat je alle submappen wilt hebben waarvan het parentID 2 is.

Laat ik even je query duidelijker op schrijven om mijn punt te maken:

SQL:

SELECT
   parent.dirID,
   COUNT(child.dirID)     dir_count,
   COUNT(media.mediaID)   media_count
FROM
   media_dir parent
   LEFT JOIN media_dir child ON(
      parent.dirID=child.parentID
   )
   LEFT JOIN media ON(
      parent.dirID=media.dirID
   )
WHERE
   parent.parentID=2  -- Oops!
GROUP BY
   parent.dirID
ORDER BY
   parent.name ASC

[drm]

Bij nader inzien heeft Janoz ook wel een beetje gelijk Maar met een COUNT(DISTINCT ...) is dat op te lossen. Dat maakt overigens in jouw geval niet uit omdat je het over (neem ik aan) unieke id's hebt, maar goed.

[ Voor 32% gewijzigd door drm op 29-08-2005 17:45 ]

[Nielsz]

Zo, de oplossing was uiteindelijk:

code:

    select 
                    
                            media_dir.dirID as media_dir_id,                    
                            media_dir.name as media_dir_name,
                            media_dir.parentID as media_dir_parent_id,
                    count(distinct md2.dirID) as aantal_dir,
                    count(distinct m.mediaID) as aantal_files 
        from
          media_dir
left join 
          media_dir as md2
                        on (media_dir.dirID = md2.parentID)
        left join 
          media as m
                        on (m.dirID = media_dir.dirID)
        where 
          media_dir.parentID = 2
        group by
          media_dir.dirID
        order by
          media_dir.name
        asc

Thanx allemaal