[PHP/mySQL] Actieve topics -> max. 5 per categorie

[radem205]

Hey,

Aan de hand van onderstaande query haal ik de actieve topics uit de database. Nu heeft elk topic een categorie (logisch). Wat ik nu wil is dat maximaal 5 berichten per categorie uit de database gehaald worden.

Dus dat je een lijst met categorieën krijgt en daaronder per categorie maximaal 5 actieve topics binnen de betreffende categorie.

SQL:

SELECT
                        forum_posts.topicid,
                        forum_topics.titel,
                        forum_topics.sticky,
                        forum_topics.gesloten,
                        MAX(forum_cats.id) AS catid,
                        MAX(forum_cats.titel) AS cattitel,
                        MAX(forum_subcats.id) AS subcatid,
                        MAX(forum_posts.id) AS maxid,
                        DATE_FORMAT(MAX(forum_posts.datum), '%d-%m-%Y om %H:%i uur') AS datum,
                        UNIX_TIMESTAMP(MAX(forum_posts.datum)) AS laatstereactieunix,
                        (COUNT(forum_posts.id) - 1) AS reactieaantal    
                    FROM
                        forum_posts
                    INNER JOIN
                        forum_topics
                    ON
                        forum_topics.id = forum_posts.topicid
                    INNER JOIN
                        forum_subcats
                    ON
                        forum_subcats.id = forum_topics.subcat
                    INNER JOIN
                        forum_cats
                    ON
                        forum_cats.id = forum_subcats.cat
                    WHERE
                        forum_topics.gesloten != '1'
                    GROUP BY
                        forum_posts.topicid,
                        forum_topics.titel,
                        forum_topics.sticky,
                        forum_topics.gesloten
                    ORDER BY
                        forum_cats.id ASC,
                        MAX(forum_posts.datum) DESC

Aangezien het om veel verschillende categorieën gaat wil ik dit bereiken door middel van 1 query (indien mogelijk natuurlijk).

Weet iemand hoe ik per "forum_cats.id" maximaal 5 berichten uit de database kan krijgen? Of moet ik dan voor elke categorie een aparte query maken (lijkt mij erg omslachtig)?

[ Voor 4% gewijzigd door radem205 op 19-05-2010 21:21 ]

[Rekcor]

Misschien helpt dit? http://www.xaprb.com/blog...max-row-per-group-in-sql/

Waarom groupeer je trouwens ook op forum_topics.titel, forum_topics.sticky en forum_topics.gesloten?

[ Voor 1% gewijzigd door Rekcor op 20-05-2010 12:10 . Reden: Janoz: ik zie het (moet toch weer wat vaker sql-en geloof ik ;)) ]

[Janoz]

Rekcor schreef op donderdag 20 mei 2010 @ 08:11:
Waarom groupeer je trouwens ook op forum_topics.titel, forum_topics.sticky en forum_topics.gesloten?

Omdat die kolommen wel opgevraagd worden, maar niet in een aggregerende functie gebruikt worden.

[radem205]

Ik heb nu het volgende aan de hand van de eerder genoemde link (dank daarvoor!):

SQL:

set @num := 0, @catid := 0;

                    SELECT 
                        forum_posts.topicid, 
                        MAX(forum_topics.titel), 
                        MAX(forum_topics.sticky), 
                        MAX(forum_topics.gesloten), 
                        MAX(forum_cats.id) AS catid, 
                        MAX(forum_cats.titel) AS cattitel, 
                        MAX(forum_subcats.id) AS subcatid, 
                        MAX(forum_posts.id) AS maxid, 
                        DATE_FORMAT(MAX(forum_posts.datum), '%d-%m-%Y om %H:%i uur') AS datum, 
                        UNIX_TIMESTAMP(MAX(forum_posts.datum)) AS laatstereactieunix, 
                        (COUNT(forum_posts.id) - 1) AS reactieaantal,
                        
                        @num := IF(@catid = MAX(forum_cats.id), @num + 1, 1) AS row_number,
                        @catid := MAX(forum_cats.id) AS dummy
                           
                    FROM 
                        forum_posts
                    INNER JOIN 
                        forum_topics 
                    ON 
                        forum_topics.id = forum_posts.topicid 
                    INNER JOIN 
                        forum_subcats 
                    ON 
                        forum_subcats.id = forum_topics.subcat 
                    INNER JOIN 
                        forum_cats 
                    ON 
                        forum_cats.id = forum_subcats.cat 
                    WHERE 
                        forum_topics.gesloten != '1' 
                    GROUP BY 
                        forum_posts.topicid
                    HAVING 
                        row_number <= 4
                    ORDER BY 
                        forum_cats.id ASC, 
                        MAX(forum_posts.datum) DESC

Echter krijg ik niet de gewenste output (4 rijen per forum_cats.id).

Kortweg krijg ik onderstaand als output:

catid	row_number
1	3
1	2
1	1
1	1
1	1
2	1
10	1
10	1

Zoals je wellicht kan zien ligt het probleem bij het niet goed optellen van de variabele row_number per categorie.
Normaal zal in bovenstaand voorbeeld catid = 1 de row_numbers '1,2,3,4,5' moeten bevatten toch?

Nu hebben zij in de link hierboven een "force index(type)" toegevoegd aan de query. Wanneer ik dit toepas in mijn voorbeeld (dus force(forum_cats.id)) gaat het fout (de query wordt niet uitgevoegd).

Kan iemand mij verder helpen?

Edit: Het lijkt wel alsof de IF statement niet goed werkt. Want elke keer dat de @catid gelijk is aan MAX(forum_cats.id) moet hij @num met 1 verhogen. Echter gebeurt dit niet.

[ Voor 6% gewijzigd door radem205 op 20-05-2010 11:40 ]

[ZaZ]

in Oracle zou je hiervoor RANK gebruiken. Weet zelf niet zoveel af van MySQL maar volgens mij heeft dat geen RANK. Wellicht met googlen naar 'rank equivalent mysql' dat je een eind komt?

[martin149]

Is het niet gewoon mogelijk om een Limit in te stellen per topic, met de gewone LIMIT??

[Voutloos]

Nee, zie bovenstaande link. Er is geen standaard sql voor, en ook geen specifieke mysql extra feature.

[radem205]

martin149 schreef op donderdag 20 mei 2010 @ 17:11:
Is het niet gewoon mogelijk om een Limit in te stellen per topic, met de gewone LIMIT??

Nee dit is helaas niet mogelijk of je moet het in allemaal aparte query's gaan uitvoeren. Dit geeft een aardige belasting op de database, zeker wanneer het (in dit geval) een forum betreft.

Ik heb gekeken naar de een equivalent van RANK voor mysql en kom op dezelfde methode die ik in de code in een eerdere post heb aangegeven. Ik doe iets fout (en ik denk in de group by), maar ik kan maar niet achterhalen wat.

Ik heb het één en ander aangepast (aan de hand van een aantal artikelen), maar het werkt maar niet.

SQL:

set @cnt = 0;
set @trk = 0;
       
        SELECT *
        FROM (       
            SELECT
                forum_posts.topicid AS topicid,
                MAX(forum_subcats.id) AS subcatid,
                MAX(forum_cats.id) AS catid,
                MAX(forum_topics.titel) AS topictitel,
                MAX(forum_posts.datum) AS datum,
                
                @cnt:=if(@trk = MAX(forum_cats.id), @cnt + 1, 0) AS cnt, 
                @trk:= MAX(forum_cats.id) AS trk
                
            FROM
                forum_posts
            INNER JOIN
                forum_topics
            ON
                forum_topics.id = forum_posts.topicid
            INNER JOIN
                forum_subcats
            ON
                forum_subcats.id = forum_topics.subcat
            INNER JOIN
                forum_cats
            ON
                forum_cats.id = forum_subcats.cat
            GROUP BY
                forum_posts.topicid
            ORDER BY
                MAX(forum_posts.datum) DESC
            ) AS c1
        WHERE c1.cnt < 5;

Wanneer ik de binnenste query (subquery) uitvoer krijg ik gewoon de gewenste rijen, echter zijn het dan meer dan 5 rijen per categorie.
Ik zie echt niet wat ik fout doe. Ik zal het zeer op prijs stellen wanneer iemand mij op weg kan helpen....

[ Voor 48% gewijzigd door radem205 op 20-05-2010 17:23 ]

[martin149]

je kunt ook misschien een tabel maken met van elk topic de laatste 5 en die steeds updaten bij het plaatsen van een reactie, dat zal al fling schelen... maar dan heb je wel meer data...

[radem205]

martin149 schreef op donderdag 20 mei 2010 @ 17:19:
je kunt ook misschien een tabel maken met van elk topic de laatste 5 en die steeds updaten bij het plaatsen van een reactie, dat zal al fling schelen... maar dan heb je wel meer data...

Dat is te omslachtig, aangezien wanneer je een topic / reactie verwijderd ook de tabel weer moet aanpassen. Dit wil ik voorkomen.

[martin149]

ja, maar ik denk wel dat dat beter is dan waneer je als iedereen op de site komt er een bult query's gedaan moeten worden

[RobIII]

Al eens gedacht aan caching?

[radem205]

martin149 schreef op donderdag 20 mei 2010 @ 17:26:
ja, maar ik denk wel dat dat beter is dan waneer je als iedereen op de site komt er een bult query's gedaan moeten worden

Dat sowieso, maar vandaar dat ik het ook in 1 query (met evt. subquery's) wil oplossen. Hiermee voorkom je dat er foutieve informatie in de database komt te staan.

Om één of andere reden werkt deze code:

SQL:

@cnt:= IF(@trk = forum_cats.id, @cnt + 1, 0) AS cnt, 
@trk:= forum_cats.id AS trk

niet correct, aangezien ik bij de uitvoer van de query in de kolom "cnt" overal "0" zie staan.

RobIII schreef op donderdag 20 mei 2010 @ 17:30:
Al eens gedacht aan caching?

Nee nog niet aan gedacht. Wat is hier het grote voordeel van in dit specifieke geval?

[ Voor 14% gewijzigd door radem205 op 20-05-2010 17:37 ]

[martin149]

radem205 schreef op donderdag 20 mei 2010 @ 17:31:
[...]


Dat sowieso, maar vandaar dat ik het ook in 1 query (met evt. subquery's) wil oplossen. Hiermee voorkom je dat er foutieve informatie in de database komt te staan.

Om één of andere reden werkt deze code:

SQL:

@cnt:= IF(@trk = forum_cats.id, @cnt + 1, 0) AS cnt, 
@trk:= forum_cats.id AS trk

niet correct, aangezien ik bij de uitvoer van de query in de kolom "cnt" overal "0" zie staan.


[...]


Nee nog niet aan gedacht. Wat is hier het grote voordeel van in dit specifieke geval?

gewoon uitgebreid uitrekenen en dan cachen = nog minder rekenen

[ Voor 83% gewijzigd door martin149 op 20-05-2010 17:53 ]

[radem205]

Ik heb nu query caching aan staan, maar dit verhelpt mijn probleem uiteraard niet...

[RobIII]

Nee, natuurlijk niet.
Ik heb 't over per (sub)forum gewoon een query te doen voor de 5 topics (dus met een simpele limit) en die resultaten te cachen in memcache/filesystem/who_cares. En die cache invalidate je (bijv.) wanneer er een topic in dat forum wordt aangemaakt/verwijderd/hernoemd. Is je dat te veel werk dan invalidate je de cache voor dat forum gewoon elke x minuten ofzo.

[ Voor 52% gewijzigd door RobIII op 20-05-2010 18:43 ]

[radem205]

Ook al heb je zeker gelijk; in het cachen ben ik nog niet echt thuis en ik ben er van overtuigd dat mijn opzet om het gewenste resultaat te krijgen met een aantal aanpassingen moet werken, maar ik zie niet wat ik fout doe.

Neem me niet kwalijk dat ik nu jouw (goede) oplossing te makkelijk van de hand doe, maar ik zou het graag op mijn aangegeven manier willen doen aangezien ik (nog) niet veel verstand heb van cachen en de tijd het niet toe laat om daar diep in te gaan duiken.

[Voutloos]

Denormaliseer de max(forum_post.datum): sla dat bij het topic op. Vervolgens wordt je query al een heel stuk goedkoper omdat je niet meer als een gek met elke post aan het joinen bent. Cache het resultaat van die query (of query per cat, moet je zelf adhv aantallen bepalen) en klaar ben je.

[martin149]

je kunt de topics een kolom meegeven met bijvoorbeeld of deze bij de 5 hoort, of als je voor topic en reacties apart hebt kun je doen welke id's van de reacties er bij horen...
of alles ophalen en php laten rekenen, maar dat is ook weer niet alles...

[radem205]

martin149 schreef op donderdag 20 mei 2010 @ 20:45:
je kunt de topics een kolom meegeven met bijvoorbeeld of deze bij de 5 hoort, of als je voor topic en reacties apart hebt kun je doen welke id's van de reacties er bij horen...
of alles ophalen en php laten rekenen, maar dat is ook weer niet alles...

Dit is volgens mij wel een hele extreme vorm van denormaliseren. Dit lijkt mij niet echt een geschikte oplossing helaas. Maar bedankt voor het meedenken!

Voutloos schreef op donderdag 20 mei 2010 @ 19:02:
Denormaliseer de max(forum_post.datum): sla dat bij het topic op. Vervolgens wordt je query al een heel stuk goedkoper omdat je niet meer als een gek met elke post aan het joinen bent. Cache het resultaat van die query (of query per cat, moet je zelf adhv aantallen bepalen) en klaar ben je.

Het is wellicht een goede oplossing, echter betreft het 20 categorieën. Ook met het oog op het verwijderen van reacties (dat je dan de datum van de laatste reactie in de topic tabel moet wijzigen) zou ik het graag in één query willen (ik heb query caching aan staan voor zover dat verschil in performance uit maakt in dit geval).

Om een extra join te vermijden heb ik wel de datum van de laatste post in de topic tabel toegevoegd, echter moet ik (natuurlijk) dan nog steeds gebruik maken van joins om per categorie de topics te selecteren.

Wanneer ik onderstaande code uitvoer dan krijg ik het gewenste resultaat, ik zie 2 topics waar het laatst op gereageerd is per subcategorie, echter wil ik het dus niet per subcategorie maar per categorie (waar dus enkele subcategorieën onder vallen).

SQL:

set @cnt = 0;
set @trk = 0;

SELECT *
FROM (
    SELECT
        forum_topics.id,
        @cnt := IF(@trk = forum_topics.subcat, @cnt+1, 0) AS cnt, 
        @trk := forum_topics.subcat AS subcatid
    FROM
        forum_topics
    ORDER BY
        forum_topics.subcat ASC,
        forum_topics.laatstereactie DESC
) AS c1 

WHERE c1.cnt < 2;

Nu, wanneer ik een 2 INNER JOINs toevoeg om per categorie de 2 topics weer te geven dan gaat het fout (zie de gebruikte code hieronder.

SQL:

set @cnt = 0;
set @trk = 0;

SELECT *
FROM (
    SELECT
        forum_topics.id,
        @cnt := IF(@trk = forum_cats.id, @cnt+1, 0) AS cnt, 
        @trk := forum_cats.id AS catid
    FROM
        forum_topics
    INNER JOIN
        forum_subcats
    ON
        forum_subcats.id = forum_topics.subcat
    INNER JOIN
        forum_cats
    ON
        forum_cats.id = forum_subcats.cat
    ORDER BY
        forum_cats.id ASC,
        forum_topics.laatstereactie DESC
) AS c1 

WHERE c1.cnt < 2;

Het veld "forum_cats.id" heeft hier elke keer 1 waarde, te weten het id van de categorie, echter lijkt het er op dat de user variables in mysql dan niet goed meer werken waardoor er meer dan 2 topics per categorie worden getoond.

Ik ben van mening dat dit moet kunnen werken, echter vind ik het raar dat de velden die door middel van de joins worden geselecteerd om een duistere reden niet goed werken met de user variables in mysql.

Je zou toch zeggen dat wanneer je logisch kijkt naar de query dat het zal moeten werken, of ziet iemand wellicht wat ik fout doe?

Ps: Er bestaat geen directe equivalent van RANK in mysql. De oplossingen die aangedragen worden is degene die ik nu ook wil gebruiken (aan de hand van user variables in mysql).

[ Voor 12% gewijzigd door radem205 op 21-05-2010 15:02 ]

[Voutloos]

radem205 schreef op vrijdag 21 mei 2010 @ 14:53:
(ik heb query caching aan staan voor zover dat verschil in performance uit maakt in dit geval).

De Mysql query cache entries worden op een heel brute manier geinvalideert: zodra een tabel gewijzigd wordt worden alle entries met queries welke die tabel gebruikten (ongeacht kolom, where clause etc) ongeldig.
Aka na elke insert/edit/delete post krijg je deze query al niet uit de cache.

Korte versie: mysql query cache zuigt kamelenballen bij tabellen welke vaak wijzigen.

[radem205]

Voutloos schreef op vrijdag 21 mei 2010 @ 15:04:
[...]
De Mysql query cache entries worden op een heel brute manier geinvalideert: zodra een tabel gewijzigd wordt worden alle entries met queries welke die tabel gebruikten (ongeacht kolom, where clause etc) ongeldig.
Aka na elke insert/edit/delete post krijg je deze query al niet uit de cache.

Korte versie: mysql query cache zuigt kamelenballen bij tabellen welke vaak wijzigen.

Mooi gezegd ! Dus deze maar uitschakelen dan?
Is hier verder niemand bekend met het gebruik van user-defined variables in mysql die mij wellicht raad kan geven?

[radem205]

Voutloos schreef op vrijdag 21 mei 2010 @ 15:04:
[...]
De Mysql query cache entries worden op een heel brute manier geinvalideert: zodra een tabel gewijzigd wordt worden alle entries met queries welke die tabel gebruikten (ongeacht kolom, where clause etc) ongeldig.
Aka na elke insert/edit/delete post krijg je deze query al niet uit de cache.

Korte versie: mysql query cache zuigt kamelenballen bij tabellen welke vaak wijzigen.

Ik heb nu toch maar per categorie aparte query's gemaakt, dit gaat uiteraard goed, maar op welke manier kan je query's nog meer cachen dan de standaard query cache van mysql?

[RobIII]

radem205 schreef op vrijdag 21 mei 2010 @ 18:32:
[...]


Ik heb nu toch maar per categorie aparte query's gemaakt, dit gaat uiteraard goed, maar op welke manier kan je query's nog meer cachen dan de standaard query cache van mysql?

Beetje vergeetachtig?