LIMIT in JOIN via emulated row numbers

Dev Shed Forums Sponsor:

October 4th, 2012, 07:38 AM

pantu

Registered User

Join Date: Aug 2012

Posts: 2

Time spent in forums: 20 m 48 sec
Reputation Power: 0

LIMIT in JOIN via emulated row numbers

I have X Categories, Y Forums and Z threads.
+ Threads belong to one Forum
+ Forums belong to one Category

I want to be able to select X Categories and
to each Category select the first 3 Forums and
to each Forum select the first 4 Threads.

(numbers only as an example)

I did this by emulating row_numbers via variables, as mysql does not support this out of the box.

Unfotunately there is still something wrong with the row-numbers. Maybe someone could have a look what is wrong here.

Query

Code:

SELECT
    CatRow,
    c_id,
    c_name,
    ForumRow,
    f_id,
    f_name,
    ThreadRow,
    t_id,
    t_title
FROM (
    SELECT
        @cat_row    := IF(@prev_cat    = c.id, @cat_row+1, 1)   AS CatRow,
        @forum_row  := IF(@prev_forum  = f.id, @forum_row+1, 1) AS ForumRow,
        @thread_row	:= IF(@prev_thread = t.id, @thread_row+1, 1)AS ThreadRow,
        c.id        AS c_id,
        c.name      AS c_name,
        f.id        AS f_id,
        f.name      AS f_name,
        t.id        AS t_id,
        t.title     AS t_title,
        @prev_cat   := c.id,
        @prev_forum := f.id,
        @prev_thread:= t.id
    FROM (
        SELECT
            *
        FROM
            forum_categories c,
            (SELECT @cat_row := 1) AS x,
            (SELECT @prev_cat := '') AS y
        ORDER BY @cat_row
    ) AS c

    LEFT JOIN (
        SELECT
            *
        FROM
            forum_forums AS f,
            (SELECT @forum_row := 1) AS x,
            (SELECT @prev_forum := '') AS y
        ORDER BY @forum_row
    ) AS f ON (c.id = f.fk_forum_category_id )

    LEFT JOIN (
        SELECT
            *
        FROM
            forum_threads AS t,
            (SELECT @thread_row := 1) AS x,
            (SELECT @prev_thread := '') As y
        ORDER BY @thread_row
    ) AS t ON (f.id = t.fk_forum_forums_id )

    ORDER BY c.id ASC, f.id ASC, t.id ASC
) c

Results:

Code:

CatRow	c_id	c_name	ForumRow f_id	f_name		ThreadRow	t_id	t_title
4	1	General	4	2	Talk			1	42	talk
5	1	General	5	2	Talk			1	43	Talk...
6	1	General	6	2	Talk			1	44	locked thread
7	1	General	7	2	Talk			1	45	closed thread
3	1	General	3	2	Talk			1	48	:(:red::confuse::)
1	1	General	1	2	Talk			1	50	gsfdgsdg
2	1	General	2	2	Talk			1	51	asdasd
9	1	General	2	5	Voting			1	47	some title
8	1	General	1	5	Voting			1	49	sadfsad
1	2	Support	1	3	Help			1	40	Hueeelefe
2	2	Support	1	4	Features and Bugs	1	41	What is a bug?
3	2	Support	1	7	Test			1	NULL	NULL	
2	3	News	2	1	News			1	39	News by admin
1	3	News	1	1	News			1	46	further news

At the end I need to be able to specify:
WHERE CatRow <= AND ForumRow <= 3 AND ThreadRow <= 4

This is not yet possible due to the wrong row_numbers.
Any ideas???

Actually I want the results to look like this:

Code:

CatRow	c_id	c_name	ForumRow f_id	f_name		ThreadRow	t_id	t_title
1	1	General	1	2	Talk			1	42	talk
1	1	General	1	2	Talk			2	43	Talk...
1	1	General	1	2	Talk			3	44	locked thread
1	1	General	1	2	Talk			4	45	closed thread
1	1	General	1	2	Talk			5	48	:(:red::confuse::)
1	1	General	1	2	Talk			6	50	gsfdgsdg
1	1	General	1	2	Talk			7	51	asdasd
1	1	General	2	5	Voting			1	47	some title
1	1	General	2	5	Voting			2	49	sadfsad
2	2	Support	1	3	Help			1	40	Hueeelefe
2	2	Support	2	4	Features and Bugs	1	41	What is a bug?
2	2	Support	3	7	Test			1	NULL	NULL	
3	3	News	1	1	News			1	39	News by admin
3	3	News	1	1	News			2	46	further news

LIMIT in JOIN via emulated row numbers

Developer Shed Advertisers and Affiliates