[SQL] row verdwijnt ipv waarde 0 met COUNT() - Softwareontwikkeling

dinsdag 21 november 2006 21:52

Acties:

Topicstarter

Ik programmeer mijn eigen forum, niet omdat ik vind dat er nog niet genoeg zijn, maar omdat ik het leuk vind en iets waar ik een boel kan leren.

Nou wil ik een systeem dat bijhoudt welke topics wel of niet gelezen zijn, maar een beetje correct, dus niet zoals phpBB of react dat heeft.

Hiertoe heb ik een tabel `gezien` waarin ik bijhoud wanneer (timestamp (epoch, niet mysql-style)) iemand een topic voor het laatst heeft bekeken. Dit gaat allemaal met afgedwongen relaties. Even de relevante informatie (tussen ` is dan ook de naam van de tabel):

`leden` hebben allen een id, de `subfora`, `topics` en `posts` ook.
Of je lid bent van een subforum wordt bepaald in `lidvansub`.
Of je een subforum mag zien wordt ook deels bepaald in `subfora`. Als een subforum private is moet je lid zijn om hem te kunnen inzien. Je kunt dus ook `subfora` hebben waar je wel geen lid van bent, maar die je wel kunt inzien mocht je daar toch behoefte aan hebben.
Wanneer een topic is gewijzigd wordt bepaald in `posts` (elke post heeft een timestamp, die wil ik niet redundant ook in `topics` hebben)

genoemde tabel `gezien` bevat id van lid en van topic, en een timestamp van de laatste keer dat het lid het topic bezocht. Maximale grote van deze tabel is dus altijd aantal topics maal aantal leden.
In onderstaande query is er nog sprake van een veld wat in `gezien`, dit is omdat er ook een CMS en nog wel meer bij komt waar ook allemaal dingen ongelezen kunnen zijn, en ik eigenlijk niet voor al die dingen een nieuwe `xxgezien` wil maken, maar misschien dat ik dat doe mocht het echt handiger blijken. De relatie tussen `gezien`.wat_id en `topics`.id is dan ook als enige niet afgedwongen.

Nou heb ik een query weten te construeren waarmee per subforum wordt bepaald hoeveel topics je ongelezen hebt. Subfora waar je geen lid van bent en die private zijn worden gefilterd, en laatste addertje onder het gras is dat als een topic niet voorkomt in `gezien`, dat hij dan dus ook ongelezen is (immers hij is nieuw), vandaar de NOT IN constructie. De query is:

SQL:

SELECT s.id, s.naam, COUNT(t.id) aantal FROM topics t, subfora s
  WHERE t.sub_id=s.id AND t.id
  NOT IN
  (
    SELECT topic_id FROM
    (
      SELECT topic_id, MAX(posted) ma FROM posts
      GROUP BY topic_id
    ) p, gezien g
    WHERE p.topic_id=g.wat_id AND g.wat=1 AND p.ma<=g.tijd AND g.lid_id='.$iLidId.'
  ) AND s.id IN
  (
    SELECT s.id FROM subfora s, lidvansub v
    WHERE v.sub_id=s.id AND
    (
      v.lid_id='.$iLidId.'
      OR s.private=0
    )
    GROUP BY s.id
  )
  GROUP BY s.id ORDER BY s.volgorde

die $iLidId is het id van het lid (wordt bij login in een session gezet).

Deze query doet z'n werk heel best, op 1 vervelend detail na. Als een subforum geen ongelezen topics heeft, dan wordt aantal geen 0, maar wordt het hele subforum niet meegenomen. En zo ben je na dit hele verhaal dan toch bij de titel van dit topic beland...

Voordat er eventuele opmerkingen komen over andere verbeterpunten van deze query, ik ben mezelf SQL aan het leren en vind dit al heel diep gaan. De query zoals ik hem nu heb snap ik, en dat gaat nu even voor

Hoe neem ik subfora met 0 ongelezen topics wel mee met voor het veld aantal gewoon 0?

Als er meer informatie nodig is dan hoor ik het wel, maar om te voorkomen dat ik veel teveel informatie geef heb ik het hier bij gelaten.

dinsdag 21 november 2006 22:32

Acties:

Robtimus

me Robtimus no like you

SQL:

1 2	SELECT s.id, s.naam, (SELECT COUNT(t.id) FROM topics t WHERE t.sub_id = s.id) AS unread FROM subfora s

Zelf even je where's aanpassen.

More than meets the eye
There is no I in TEAM... but there is ME
system specs

Reageer