Activetopics - Get max 5 topics per category

Question

Hey,

I want to get the 5 latest active topics within several category's. Each topic has a subcatid and this subcatid relates to a catid.

What I want is to get the 5 active topics within each catid.

I'm trying to use the query below, but this isn't working at all:

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

                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,

                    @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_cats.id IN (1)
                AND
                    forum_topics.gesloten != '1'
                GROUP BY
                    forum_posts.topicid,
                    forum_topics.titel,
                    forum_topics.sticky,
                    forum_topics.gesloten
                HAVING
                    row_number <= 5                 
                ORDER BY
                    forum_cats.id ASC,
                    MAX(forum_posts.datum) DESC

When executing this code I get always the same number (1) for row_number, so this is not the result I want.

Does anyone know how I can get this work?

Thanks!

Answer 1

Try this simplified query, if it returns something close to what you want, then you can add the other needed columns to it:

set @num := 0, @id := 0;
SELECT
    forum_cats.id
    forum_topics.id,
    @id:=if(@id > forum_topics.id, @id, forum_topics.id) , -- the latest forum_topics.id for each category
    @num := IF(@id = forum_topics.id, 1, @num + 1) AS row_number
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_cats.id IN (1,3,5)
AND
    forum_topics.gesloten != '1'
GROUP BY 
    forum_cats.id,
    forum_topics.id
ORDER BY
    forum_cats.id ASC,
    MAX(forum_posts.datum) DESC

Answer 2

I tried something similar with just 2 tables in MySQL, and got some very weird results. First of all, your variable assignments in the query are wrong (at least, if I interpret what you're trying to achieve).

So first replace :

@num := IF(@catid = MAX(forum_cats.id), @num + 1, 1) AS row_number,
@catid := MAX(forum_cats.id) AS dummy 

with

@num := IF(@catid = forum_cats.id, @num + 1, 1) AS row_number,
@catid := forum_cats.id AS dummy

But the weird thing is that if you run the query without any filtering on row_number, you get the expected result (ie. you get a row_number per forum category). But when adding the having restriction you will note that the row_number will suddenly jump by 2 (ie row_number will be 1,3,5 instead of 1,2,3 if you have 3 categories). You can get the expected results by using having row_number < 10 instead of your having row_number <= 5.

btw I got this weird behaviour in MySQL 5.0.51b on Windows and I just got the same in MySQL 5.1.41 on Ubuntu, so I guess it's a bug...

Oh, btw the rest of your query must be wrong (you should drop all the MAX(...) as ...), and @ceteras is right, you'd better start simply and extend the query as you go along. The weird thing I noted can apparently be solved by adding an ORDER BY clause (go figure).

Answer 3

Thanks for your reply's.

With the query of Ceteras I get the following results: click here

I think this is the correct result, but I don't know how to edit to get it like I want.

When I use the query underneath the output is correct:

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 < 5;

But when I want to get the topics per category by using the code below, the result isn't what I expected. But when I look clearly I don't find the mistake I propably made.

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;

I hope you want to help me guys! Thanks!