[MySQL] Probleem met Count in select statement

[NMe]

Ik ben eens gaan kloten om een eigen forum te maken in PHP en ben op een probleem gestuit. Ik wil (zonder extra queries) alle gegevens ophalen die ik nodig heb voor de categorie- en forumindex. Dat haal in uit de tabellen category, forum en moderator. In moderator staan alleen 2 ids om de forum tabel aan de user tabel te kunnen linken.

Nou probeer ik de volgende query:

code:

SELECT category.cid, category.name AS cname, category.desc AS cdesc, forum.name AS fname, forum.desc AS fdesc, forum.fid, usr.uname AS modname
FROM category, forum, usr, moderator
WHERE category.cid = forum.cid
AND usr.uid = moderator.mid
AND moderator.fid = forum.fid
ORDER BY cname, fname, modname

Deze query gaat geheel zonder problemen, maar geeft me alleen maar die categorieën terug waar fora in zitten (dit vind ik persoonlijk geen probleem dus dat mag zo blijven), en alleen maar alle fora die een moderator hebben. Dit laatste is mijn probleem, een forum heeft namelijk geen moderator nodig, administrators kunnen afdoende zijn. Nou dacht ik dat op te lossen met de volgende query:

code:

SELECT category.cid, category.name AS cname, category.desc AS cdesc, forum.name AS fname, forum.desc AS fdesc, forum.fid, usr.uname AS modname, COUNT(moderator.fid) AS cnt
FROM category, forum, usr, moderator
WHERE category.cid = forum.cid
AND ((usr.uid = moderator.mid
AND moderator.fid = forum.fid)
OR (cnt = 0))
ORDER BY cname, fname, modname

Nou zegt MySQL hierop dat hij de kolom 'cnt' in de where clause niet kent. Is het op de een of andere manier toch mogelijk om te doen wat ik hier probeer?

[whoami]

* Kijk eens naar het concept van 'OUTER JOINS'. (LEFT JOIN / RIGHT JOIN ).

* filteren op een aggregated column doe je niet in de WHERE clausule, maar in de HAVING clausule.
bv:

code:

SELECT naam, COUNT(*)
FROM tabel
GROUP BY naam
HAVING COUNT(*) > 2

[ Voor 57% gewijzigd door whoami op 26-02-2004 09:03 ]

[NMe]

Woei, tnx, tis opgelost met deze query:

code:

SELECT category.cid, category.name AS cname, category.desc AS cdesc, forum.name AS fname, forum.desc AS fdesc, forum.fid, usr.uname AS modname
FROM usr, category, ( forum
RIGHT  JOIN moderator ON usr.uid = moderator.mid AND moderator.fid = forum.fid )
WHERE category.cid = forum.cid
ORDER  BY cname, fname, modname

Edit: Whoeps, te vroeg gejuicht, ik krijg hiermee nog steeds dat forum zonder moderator niet...

[ Voor 13% gewijzigd door NMe op 26-02-2004 09:24 ]

[NMe]

Ik heb het opgelost door die outer join te combineren met een check in PHP zelf. Mocht iemand interesse hebben, hier is de code.

code:

<?php

  include("functions.inc.php");
  global $DB;
  
  $query = $DB->Query("SELECT category.cid, category.name AS cname, category.desc AS cdesc, forum.name AS fname, forum.desc AS fdesc, forum.fid, usr.uname AS modname, moderator.mid ".
                      "FROM usr, category, (forum LEFT  JOIN moderator ".
                      "ON moderator.fid = forum.fid) ".
                      "WHERE category.cid = forum.cid ".
                      "AND (usr.uid = moderator.mid OR ISNULL(moderator.mid)) ".
                      "ORDER  BY cname, fname, modname");
                      
  $str = "";
  $Cat = "";
  $Frm = "";
  $noMod = false;
  while ($row = $DB->GetObject($query)) {
        if ($noMod && ($Cat == $row->cname) && ($Frm == $row->fname)) {
          $noMod = false;
          continue;
        }
        $noMod = $row->mid == NULL;
        if ($row->cname != $Cat) $str .= ($Cat != "" ? "</table>\n" : "")."<table width=\"90%\">\n\t<tr>\n\t\t<th colspan=\"3\"><a href=\"viewcat.php?id=$row->cid\">$row->cname</a><br />\n\t\t\t<font size=\"-1\">$row->cdesc</font></th>\n\t</tr>\n\t";
        if ($row->fname != $Frm) $str .= (($Frm != "") && ($Cat != $row->cname) ? "</td>\n\t</tr>" : "")."<tr>\n\t\t<td>$row->fid</td>\n\t\t<td><a href=\"viewforum.php?id=$row->fid\">$row->fname</a><br />\n\t\t\t<font size=\"-1\">$row->fdesc</font></td>\n\t\t<td>".($row->mid == NULL ? "-" : $row->modname);
        else $str .= ",<br \>\n\t\t\t$row->modname";
        $Cat = $row->cname;
        $Frm = $row->fname;
    }
    if ($Cat != "") $str .= "</td>\n\t</tr>\n</table>";

?>
<html>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
  <title>Frum</title>
</head>
<body>
<?php echo $str; ?>
</body>
</html>

(Die HTML zooi gaat er nog uit om plaats te maken voor templates, dit was maar een testje)

[whoami]

NMe84 schreef op 26 februari 2004 @ 09:20:
Woei, tnx, tis opgelost met deze query:

code:

SELECT category.cid, category.name AS cname, category.desc AS cdesc, forum.name AS fname, forum.desc AS fdesc, forum.fid, usr.uname AS modname
FROM usr, category, ( forum
RIGHT  JOIN moderator ON usr.uid = moderator.mid AND moderator.fid = forum.fid )
WHERE category.cid = forum.cid
ORDER  BY cname, fname, modname

Edit: Whoeps, te vroeg gejuicht, ik krijg hiermee nog steeds dat forum zonder moderator niet...

Dat komt waarschijnlijk omdat je een LEFT join moet gebruiken:

code:

SELECT *
FROM forum
LEFT JOIN moderator ON forum.moderator_id = moderator.moderator_id

Ik vind het trouwens niet logisch dat je je mod's in een aparte tabel stopt. Een moderator is toch ook gewoon een user?

[NMe]

whoami schreef op 26 februari 2004 @ 10:35:
Dat komt waarschijnlijk omdat je een LEFT join moet gebruiken:

Zoals je ziet heb ik dat al gedaan.

whoami schreef op 26 februari 2004 @ 10:35:
Ik vind het trouwens niet logisch dat je je mod's in een aparte tabel stopt. Een moderator is toch ook gewoon een user?

Veel op veel relatie, dus heb ik die volgens normalisatieregels in een aparte tabel gezet. De tabel moderator bevat alleen een verwijzing naar een gebruikersid en naar een forumid. Voor zover ik weet pakt PHPbb het ook zo aan.

[ Voor 1% gewijzigd door NMe op 26-02-2004 10:40 . Reden: Foutje ]

[gorgi_19]

Veel op veel relatie, dus heb ik die volgens normalisatieregels in een aparte tabel gezet. De tabel moderator bevat alleen een verwijzing naar een gebruikersid en naar een forumid. Voor zover ik weet pakt PHPbb het ook zo aan.

Een moderator is imho net zoiets als een functie; je kan ook moderator zijn zonder enige extra rechten en je kan user zijn met edit rechten. Oftewel: Een user zit in een groep en een groep heeft een of meerdere rechten op een forum.

[NMe]

Niet elke moderator heeft dezelfde rechten, dus leek deze aanpak me beter. Jan mag bijvoorbeeld forum1 modereren, maar Piet mag dat niet, en om daar nou aparte groepen voor te maken...

[ Voor 16% gewijzigd door NMe op 26-02-2004 10:43 ]

[gorgi_19]

NMe84 schreef op 26 februari 2004 @ 10:42:
Niet elke moderator heeft dezelfde rechten, dus leek deze aanpak me beter. Jan mag bijvoorbeeld forum1 modereren, maar Piet mag dat niet.

Erhm.. Dus? Daar heb je toch functiegroepen voor?

Oftewel: Een user zit in een groep en een groep heeft een of meerdere rechten op een forum.

[ Voor 17% gewijzigd door gorgi_19 op 26-02-2004 10:43 ]

[NMe]

Zoals ik in mijn edit al zei: ik vind het een beetje omslachtig om voor elke moderator een aparte user groep te maken, want daar komt het wel op neer. Stel ik heb 10 moderators, met ieder andere rechten, dan heb ik dus 10 aparte rechtengroepen, waar dan ook nog eens in opgeslagen moet worden wat die rechten dan wel niet zijn. Is dit dan niet veel handiger? Nu heb ik ook 10 records nodig, maar met veel minder data. Ik kan nog altijd een groepentabel toevoegen, maar voor dit doeleinde ben ik (nog?) niet overtuigd van het nut ervan.

[gorgi_19]

Ik vind niet dat je uit kan gaan van een relatie 1 moderator op 1 forum. Verder ben ik ook niet met je eens dat er in een forum 1 user en 1 moderator is. Waarom zouden sommige users niet meer rechten hebben als user, maar minder als moderator?

Maar goed; ik hoef je niet te overtuigen; 't is jouw forum.

[NMe]

Haha, daar heb je gelijk in. Ik denk trouwens dat je niet helemaal begrijpt wat ik wil. Ik wil gewoon per forum kunnen instellen wie daar voor modje mag spelen. Daarbij wil ik ook nog user groups maken met rechten die voor een bepaalde user groep op ALLE fora gelden. Zie mijn moderator tabel dus als iets dat de standaard rechten uitbreidt met gebruikerspecifieke rechten.