[MySQL] Subquery geeft niet het juiste resultaat

[GWTommy]

Ik wil een systeembreed 'ongelezen berichten systeem' ontwikkelen. Om makkelijk te beginnen ben ik met mijn forum begonnen en probeer ik meteen dit idee er in te verwerken. Iedere hit wordt vastgelegd in de database met userId, tijd en pagina (eigenlijk controller, action en id parameter voor het gemak). Aan de hand hiervan, icm de tabel met gegevens van berichten, moet ik kunnen berekenen hoeveel ongelezen berichten er zijn per topic nadat de gebruiker voor het laatste elk topic heeft bekeken. Natuurlijk probeer ik ook rekening te houden met het feit dat er geen record van een hit voor topics kan bestaan. Ik loop nu toch echt vast. Ik heb werkelijk geen idee hoe ik mijn query moet joinen om het gewenste resultaat te krijgen, dus heb ik het eerst geprobeerd met een subquery, maar helaas geeft die gewoon voor ieder returned record aan hoeveel berichten in de hele tabel forumMessages staat (21 op dit moment dus). Eerst zal ik m'n query maar eens posten, dan weten jullie misschien ook waar ik het over heb.

MySQL:

SELECT
    `forumTopics`.`id`,
    forumTopics.id as topicId,
    `forumTopics`.`userId`,
    `forumTopics`.`title`,
    `forumTopics`.`date`,
    `forumTopics`.`locked`,
    `forumTopics`.`deleted`,
    `forumTopics`.`flagged`,
    `forumTopics`.`information`,
    COUNT(forumMessages.id) AS `posts`,
    `users`.`name` AS `userName`,
    `users`.`group` AS `userGroup`,
    hits.date AS lastHit,
    IF (lastHit IS NULL, 1, (SELECT COUNT(forumMessages.id) FROM forumMessages WHERE ((forumMessages.date between lastHit and NOW()) OR lastHit IS NULL) AND forumMessages.topicId = topicId))
FROM `forumTopics`
LEFT JOIN `users` ON users.id = forumTopics.userId
LEFT JOIN `forumMessages` ON forumMessages.topicId = forumTopics.id
LEFT JOIN hits ON hits.idParam = forumTopics.Id
WHERE
    (forumMessages.topicId = forumTopics.id)
    AND (forumTopics.forumId = 6)
    AND ((hits.userId = 973
    AND hits.controller = 'forum'
    AND hits.action = 'topic'
    AND hits.idParamType = 'id')
    OR hits.date IS NULL) 
GROUP BY `forumTopics`.`id``

Sorry voor de onleesbaarheid, een deel komt nog van ZF ->__toString() af en heb ik zo in m'n client gepaste om te bewerken. Het aantal posts klopt omdat dat via een simpele left join gaat, maar het aantal newMessages is dus het totaal aantal records in forumMessages voor ieder record dat er uit komt. Voor de goede orde, ik probeer nu dus een topic overzicht voor een bepaalde categorie (id = 6) en het id van mijn account is 973, om dus de hits op te halen.

Deze query zou ik het liefst met een hele mooie join zien, maar als het niet anders kan dan een nested query vind ik het ook OK (is alleen wat minder mooi te programmeren met Zend_Db_Statement.. Ik krijg nu met deze nested query de error dat lastHit niet bestaat binnen de nested, terwijl je toch zou denken dat eerst de hoofd query wordt afgewerkt en daarna de sub pas ingevuld wordt. Door de bomen het bos... Ik dacht dat ik het wel een beetje kon ondertussen.

Bedankt.

[ACM]

Als ik je (en je query) goed begrijp wil je voor elk record (elk topic) er bij vermelden hoeveel reacties er bijgekomen zijn sinds de laatste keer dat je het topic bekeken hebt?

Je huidige join kan veel netter, maar klopt nu sowieso niet; je OR staat verkeerd genest en volgens mij krijg je nu niet gegarandeerd de laatste hit terug.

Ik zou zoiets proberen, juist alles naar de subquery verhuizen. Tenzij je die informatie (bijv de last visit) ook nodig hebt is dat veel handiger.

SQL:

SELECT topic-velden,
 (SELECT count(*) FROM messages m 
  WHERE m.topicid = t.id AND m.date > 
                 -- Op null forceren als er nog geen max date was dmv coalesce(..., 0)
                 -- Wellicht wil je daar een andere begin-parameter voor gebruiken
                 COALESCE((SELECT MAX(date)
                   FROM hits h 
                   WHERE h.userid = 973 AND h.controller = 'forum' ... 
                          AND h.idParam = t.id), 0) as unReadCount
FROM topics t ...
WHERE t.forumid = 6 ...

Dan kan je ook gelijk je group by en het gros van je joins weglaten.

Als je zowel de last visit tijd, als unread count wilt hebben, dan wordt je query sowieso nog weer complexer. Dan zou je kunnen overwegen om juist wel de join met hits naar de buitenste query te verhuizen, maar dan moet je de unread count anders oplossen. Dat zou dan kunnen door de hele nieuwe query weer als subquery te gaan behandelen. De nieuwe query bijvoorbeeld zoiets:

select *, (select count messages...) from (select ... je normale topic-query + last visit ...) as topiclist

[ Voor 24% gewijzigd door ACM op 03-08-2011 08:08 ]

[Cartman!]

Ik zou het aantal posts en de laatste post lekker denormaliseren, gewoon wat extra velden die je update als er een nieuwe post oid is. Dat scheelt je een hoop joins, leesbaarheid en performance.

[ACM]

Cartman! schreef op woensdag 03 augustus 2011 @ 08:44:
Ik zou het aantal posts en de laatste post lekker denormaliseren, gewoon wat extra velden die je update als er een nieuwe post oid is. Dat scheelt je een hoop joins, leesbaarheid en performance.

Aantal posts is wel te denormaliseren, maar aangezien iedere bezoeker een andere laatste-bezoektijd zal hebben, is dat wat lastiger. Tenzij je dat natuurlijk voor elk topic voor elke bezoeker wilt gaan opslaan...

Tenzij je natuurlijk alleen maar wilt aangeven dat er nieuwe posts zijn, ipv hoeveel. Dan is het wel vrij triviaal met denormalisatie mogelijk.

[ Voor 12% gewijzigd door ACM op 03-08-2011 08:50 ]

[Cartman!]

Dat laatste is makkelijker ja, dan vergelijk je enkel de datetime van de laatste hit van de user met de datetime van de laatste post in je presentatie-laag. Anders zou ik dat onderdeeltje ook niet denormaliseren waarschijnlijk maar wellicht er n losse query voor maken zodat het ophalen van het topic zelf goed te cachen is.

edit: hangt ook n beetje af van hoe je moet nadenken over performance, als 4 mensen t gaan gebruiken boeit t opzich ook weinig dan.

[ Voor 17% gewijzigd door Cartman! op 03-08-2011 09:10 ]

[GWTommy]

Onze motor club heeft bijna 1000 leden waarvan er maar een stuk of 200 echt actief zijn, nooit meer dan 10 a 12 online op de drukste momenten. Performance van zo'n query hebben dan dus weinig invloed (denk ik), mocht dat wel het geval zijn, bekijk ik dat tzt wel. Ook heeft onze host alleen ons op z'n servers en zijn eigen site (statische site), we hebben dus zelfs een aparte bak voor de db die anders ongebruikt is.

Ik hoef de laatste hit niet te hebben, dat is nutteloze informatie voor de gebruiker om bij iedere topic result te hebben. Daarom ga ik die subquery inderdaad zo aanpassen als je in je eerste post al zegt ACM. Denormaliseren zou kunnen, maar dat ligt niet mijn voorkeur. Eerst eens zo proberen. Als het lukt, mag ik beginnen aan het volgende vraagstuk. Voor alle subfora, dus van oa het forum overzicht (index) hoeveel nieuwe topics (dus alleen nieuw geopende topics, niet gelezen topics met nieuwe berichten) en hoeveel nieuwe berichten. Maar goed, eerst dit. Ik maak het me zelf wel moeilijk.

Bedankt.

MySQL:

SELECT
    `forumTopics`.`id`,
    forumTopics.id as topicIdC,
    `forumTopics`.`userId`,
    `forumTopics`.`title`,
    `forumTopics`.`date`,
    `forumTopics`.`locked`,
    `forumTopics`.`deleted`,
    `forumTopics`.`flagged`,
    `forumTopics`.`information`,
    COUNT(forumMessages.id) AS `posts`,
    `users`.`name` AS `userName`,
    `users`.`group` AS `userGroup`,
    (
        SELECT COUNT(forumMessages.id)
        FROM forumMessages
        WHERE
            forumMessages.topicId = topicIdC
            AND forumMessages.date > COALESCE(
                                        (SELECT MAX(hits.date)
                                        FROM hits
                                        WHERE
                                            hits.userId = 973
                                            AND hits.idParam = topicIdC
                                            AND hits.controller = 'forum'
                                            AND hits.action = 'topic'
                                            AND hits.idParamType = 'id'
                                    ), 0)
        GROUP BY forumMessages.topicId
    ) AS newMessages
FROM `forumTopics`
LEFT JOIN `users` ON users.id = forumTopics.userId
LEFT JOIN `forumMessages` ON forumMessages.topicId = forumTopics.id
WHERE
    (forumMessages.topicId = forumTopics.id)
    AND (forumTopics.forumId = 6)
GROUP BY `forumTopics`.`id`

Dat is 'm geworden, werkt als een trein. 12,5ms. Zal straks de database eens volpompen met berichten en hits, dan kijken hoe die wil. De volgende ga ik ook eerst zelf proberen, maar verwacht mij hier maar gewoon terug met een nieuwe quest.

[ Voor 40% gewijzigd door GWTommy op 03-08-2011 12:43 ]

[GlowMouse]

Als hij traag wordt, probeer dan een index in de tabel hits op (idParam,userId) of op (userId,idParam) (net wat handiger is, laat je keuze afhangen van of je een andere index weg kan doen).

De GROUP BY in je subquery is onnodig.

De forumMessages.topicId = forumTopics.id kan uit je WHERE als je een gewone JOIN ipv een LEFT JOIN gebruikt.

Ik mis een ORDER BY, volgens mij gaat hij nu oplopend sorteren op ID. Een ORDER BY `forumTopics`.`id` DESC zou de query niet trager maken.

Voor je nieuwe query is nodig wanneer iemand voor het laatst een bepaald forum heeft bezocht. Dat lijkt me met je huidig datamodel niet eenvoudig te achterhalen.

[GWTommy]

Zoals ik al zei, traag zal ie niet snel worden. De huidige database heeft maar 20k posts en 1k topics. En zo vaak zal de query niet lopen ivm niet veel bezoekers (besloten club) en wat caching dat ik ga toepassen (vooral bij forum index natuurlijk).

Na aanleiding van je opmerking dat je een order by miste, bedacht ik me, ik moet natuurlijk inderdaad sorteren op laatste post. Dus ben ik weer even terug gegaan naar m'n die query en heb ik wat zitten klooien. Het werkt nou wel, maar van ieder topic wordt het eerste bericht, dus OP, mee genomen als laatste bericht en daar op gesorteerd. Dit komt omdat de order by forumMessages.date terugslaat op het uiteindelijke resultaat van de query en niet van de LEFT JOIN. Na wat zoeken kwam ik er achter dat dat opgelost zou zijn als ik de tabel waarop de LEFT JOIN gedaan werd vervangen zou worden door een subquery die al sorteerde op datum. Helaas had dat precies het zelfde resultaat, heel vreemd dus. Ik heb bij m'n query voor de laatste post voor een forum overzicht het zelfde probleem.
Beide queries

MySQL:

SELECT
    `forumTopics`.`id`,
    forumTopics.id as topicIdC,
    `forumTopics`.`userId`,
    `forumTopics`.`title`,
    `forumTopics`.`date`,
    `forumTopics`.`locked`,
    `forumTopics`.`deleted`,
    `forumTopics`.`flagged`,
    `forumTopics`.`information`,
    COUNT(forumMessages.id) AS `posts`,
    forumMessages.date AS messageDate,
    forumMessages.userId AS messageUserId,
    `users`.`name` AS `userName`,
    `users`.`group` AS `userGroup`,
    users.name AS messageUserName,
    users.group AS messageUserGroup,
    (
        SELECT COUNT(forumMessages.id)
        FROM forumMessages
        WHERE
            forumMessages.topicId = topicIdC
            AND forumMessages.date > COALESCE(
                                        (SELECT MAX(hits.date)
                                        FROM hits
                                        WHERE
                                            hits.userId = 973
                                            AND hits.idParam = topicIdC
                                            AND hits.controller = 'forum'
                                            AND hits.action = 'topic'
                                            AND hits.idParamType = 'id'
                                    ), 0)
            AND forumMessages.date > DATE_ADD(NOW(), INTERVAL -2 WEEK)
    ) AS newMessages
FROM `forumTopics`
LEFT JOIN `users` ON users.id = forumTopics.userId
LEFT JOIN `forumMessages` ON forumMessages.topicId = forumTopics.id
LEFT JOIN users AS messageUsers ON forumMessages.userId = messageUsers.id
WHERE
    (forumMessages.topicId = forumTopics.id)
    AND (forumTopics.forumId = 6)
GROUP BY `forumTopics`.`id`
ORDER BY forumMessages.date DESC

En de query voor m'n forum overzicht (niet gesorteerd en geen WHERE omdat de hierarchische structuur door PHP bepaald wordt en direct naar de cache geschreven wordt.

MySQL:

SELECT
    forumCategories.*,
    forumTopics.id AS topicIdC,
    forumTopics.title,
    forumMessages.date,
    forumMessages.userId,
    users.name AS userName,
    users.group AS userGroup,
    (
        SELECT COUNT(forumMessages.id)
        FROM forumMessages
        WHERE
            forumMessages.topicId = topicIdC
            AND forumMessages.date > COALESCE(
                                        (SELECT MAX(hits.date)
                                        FROM hits
                                        WHERE
                                            hits.userId = 973
                                            AND hits.idParam = topicIdC
                                            AND hits.controller = 'forum'
                                            AND hits.action = 'topic'
                                            AND hits.idParamType = 'id'
                                    ), 0)
    ) AS newMessages 
FROM forumCategories
LEFT JOIN forumTopics ON forumTopics.forumId = forumCategories.id
LEFT JOIN (SELECT * FROM forumMessages ORDER BY forumMessages.date DESC) AS forumMessages ON forumMessages.topicId = forumTopics.id
LEFT JOIN users ON forumMessages.userId = users.id
GROUP BY forumCategories.id
ORDER BY
    forumCategories.parent,
    forumCategories.order,
    forumMessages.date DESC

[GlowMouse]

Programming FAQ - SQL

[GWTommy]

Zo blijkt maar weer, ik weet vrijwel niks van SQL. Dit wist ik bijvoorbeeld niet. Kan ik beter mijn query anders opbouwen en de GROUP BY (als dat uberhaupt mogelijk is) er uit werken, zorgen dat ieder veld maar een waarde terug geeft per JOIN of nog anders?

Sorry, ik heb totaal geen idee hoe ik dit moet oplossen. Boven mijn capaciteiten vrees is. Erg lastige materie allemaal. Ik ga nog wat lezen. Btw, ik vraag niet om een directe oplossing, een aanzetje is voor mij al goed, wil het best verder uitzoeken, maar met de voorbeeldjes van die HowTo kom ik er helaas niet. Thnx

[GlowMouse]

Ik denk dat je dit nodig hebt: http://dev.mysql.com/doc/...mum-column-group-row.html

[GWTommy]

Dat was wat ik zocht. Alleen nog een probleem met m'n eerste query.

MySQL:

SELECT
    `forumTopics`.`id`,
    forumTopics.id as topicIdC,
    `forumTopics`.`userId`,
    `forumTopics`.`title`,
    `forumTopics`.`date`,
    `forumTopics`.`locked`,
    `forumTopics`.`deleted`,
    `forumTopics`.`flagged`,
    `forumTopics`.`information`,
    COUNT(forumMessages.id) AS `posts`,
    forumMessages.id AS messageId,
    MAX(forumMessages.date) AS messageDate,
    forumMessages.userId AS messageUserId,
    `users`.`name` AS `userName`,
    `users`.`group` AS `userGroup`,
    messageUsers.name AS messageUserName,
    messageUsers.group AS messageUserGroup,
    (
        SELECT COUNT(forumMessages.id)
        FROM forumMessages
        WHERE
            forumMessages.topicId = topicIdC
            AND forumMessages.date > COALESCE(
                                        (SELECT MAX(hits.date)
                                        FROM hits
                                        WHERE
                                            hits.userId = 973
                                            AND hits.idParam = topicIdC
                                            AND hits.controller = 'forum'
                                            AND hits.action = 'topic'
                                            AND hits.idParamType = 'id'
                                    ), 0)
            AND forumMessages.date > DATE_ADD(NOW(), INTERVAL -2 WEEK)
    ) AS newMessages
FROM `forumTopics`
LEFT JOIN `users` ON users.id = forumTopics.userId
LEFT JOIN `forumMessages` ON forumMessages.topicId = forumTopics.id
LEFT JOIN forumMessages AS forumMessagesC ON forumMessages.id = forumMessagesC.id AND forumMessages.date < forumMessagesC.date
LEFT JOIN users AS messageUsers ON forumMessagesC.userId = messageUsers.id
WHERE
    (forumMessages.topicId = forumTopics.id)
    AND (forumTopics.forumId = 6)
    AND forumMessagesC.id IS NULL
GROUP BY `forumTopics`.`id`
ORDER BY forumMessages.date DESC

Het probleem is dat ik messageUserName en messageUserGroup NULL krijg omdat die niet gevonden kunnen worden ivm forumMessagesC.id IS NULL. Straks maar even door spitten kijken of ik niet iets over het hoofd heb gezien. Enorm bedankt voor de hulp GlowMouse. Thnx

offtopic:
Vaag, de codeparser ziet m'n 'ADD (' als functie syntax

Edit: het lijkt toch niet te werken. De MAX() functie deed het werk waardoor alleen voor de datum het goede record werd gebruikt. Terug bij af dus.

[ Voor 4% gewijzigd door GWTommy op 07-08-2011 00:50 ]

[GlowMouse]

Bij jouw "LEFT JOIN forumMessages AS forumMessagesC ON forumMessages.id = forumMessagesC.id AND forumMessages.date < forumMessagesC.date WHERE forumMessagesC.id IS NULL" moet je wel topicid ipv id pakken om op te joinen, en dan nog gaat het niet goed omdat jij juist wel iets uit forumMessages wilt (namelijk de laatste). Ik denk dat je twee left joins nodig hebt (C voor de data, en D om te garanderen dat er geen nieuwer record is):

LEFT JOIN forumMessages AS forumMessagesC ON forumMessages.topicid = forumMessagesC.topicid
LEFT JOIN forumMessages AS forumMessagesD ON forumMessagesC.topicid = forumMessagesD.topicid AND forumMessagesC.date < forumMessagesD.date
WHERE forumMessagesC.id IS NOT NULL AND forumMessagesD.id IS NULL

[GWTommy]

Kijk, dat was 'm. Bedankt voor de hulp. Is dit normaal, zo veel joins om in principe zo'n simpel resultaat te krijgen? Kan dit niet versimpeld worden of is dit de enige mogelijkheid?

[GlowMouse]

Zulke data wordt gewoonlijk gedenormaliseerd.