SQL Limit im Join

lockdoc

lockdoc

Well-Known Member
Hi,

ich haette da ein kleines Problem mit einem SQL Query:

Es gibt X viele Kategorien.
Jede Kategorie hat Y viele Foren.
Nun wuerde ich gerne in einem einzigen query:

+ 6 Kategories laden
+ Zu jeder Kategorie jeweils die letzten 2 Foren

Geordnet via
+ Category.sort ASC
+ Forum.created DESC

+ Also insgesamt 12 Rows (6*2)

Code: 
SELECT
    Category.id    AS `Category.id`,
    Category.name  AS `Category.name`,
    Forum.id       AS `Forum.id`,
    Forum.title    AS `Forum.title`
From
    categories AS Category
LEFT JOIN
    forums AS Forum
ON (Category.id = Forum.category_id)
ORDER BY
    Category.sort ASC, Forum.created DESC
Leider kann ich nicht am Ende einfach LIMIT schreiben, da sich dass ja dann auf die gesamten Rows auswirkt.

Jemand eine Idee, wie ich in einen Join LIMITen kann?
 
Also ich habe mal folgendes mit variablen probiert:

Code: 
SELECT
	Category.id		AS `Category.id`,
	Category.name	AS `Category.name`,
	Forum.id			AS `Forum.id`,
	Forum.name		AS `Forum.name`,
	Forum.modified	AS `Forum.modified`,
	@catId:=Category.id AS catId
FROM
	forum_categories AS Category
LEFT JOIN forum_forums AS Forum ON
(
	Forum.id IN(
		SELECT * FROM (SELECT f.id FROM forum_forums AS f  WHERE f.fk_forum_category_id=@catId LIMIT 2) AS tt
	)
)

Das klappt leider nicht und es nur soviele rows zurueck, wie es Kategorien gibt.


Das folgende klappt:
Code: 
SELECT
	Category.id		AS `Category.id`,
	Category.name	AS `Category.name`,
	Forum.id			AS `Forum.id`,
	Forum.name		AS `Forum.name`,
	Forum.modified	AS `Forum.modified`,
	@catId:=Category.id AS catId
FROM
	forum_categories AS Category
LEFT JOIN forum_forums AS Forum ON
(
	Forum.id IN(
		SELECT f.id FROM forum_forums AS f  WHERE f.fk_forum_category_id=@catId
	)
)

Allerdings kann ich hier kein LIMIT benutzen. (SQL Error(1235) This version of MySQL doesnt yet support LIMIT & IN/ALL/ANY/SOME subquery
 
Bfhhuuh, an einem spätem Abend ein SQL-Rätsel von jemandem, der SQL-Hohepriester werden will....

Also wenn da wirklich Bedarf besteht, die Vorgabe in EINER Query aus welchem Grund auch immer abfackeln zu wollen, bitteschön, man kann ja auch gerne 4GL- und 3GL-Sprachelemente mixen, um folgendes Monster zu erzeugen indem je Kategorie Teil-Ergebnisse vereinigt werden:

Q = UNION Q0 UNION ... UNION Q5
Zum Vergrößern anklicken....

Qkat
=
SELECT * FROM

(SELECT
Category.id,
Category.name,
Forum.id,
Forum.title
FROM
(SELECT
id,
name,
FROM
categories
ORDER BY
sort ASC
SKIP kat
LIMIT 1
) AS Category

LEFT JOIN

forums AS Forum
ON (Category.id = Forum.category_id)
ORDER BY
Forum.created DESC
)

LIMIT 2
Zum Vergrößern anklicken....

Ob das gefällt, ist eine andere Frage. Ein wenig verbessern könnte man das Query-Monster, indem 'Category' als temporäre Tabelle erzeugt wird, dann ist alles evtl. einen Tick schneller, zumindest aber Seiteneffekte bzgl. der Kategorien wird vorgebeugt.

Will man das Monster nicht, dann durch die Tupel von

SELECT
Category.id,
Category.name,
Forum.id,
Forum.title
FROM
(SELECT
id,
name,
sort
FROM
categories
ORDER BY
sort ASC
LIMIT 6
) AS Category

LEFT JOIN

forums AS Forum
ON (Category.id = Forum.category_id)
ORDER BY
Category.sort ASC,Forum.created DESC
Zum Vergrößern anklicken....

iterieren und nur die gewünschten Foren ausgeben.


Mehr fällt mir zu dem Thema erst mal nicht ein (getestet ist beides nicht, sollte aber funtkionieren). Verbesserungsvorschläge sind gewünscht.
 
Zuletzt bearbeitet:
spaulding schrieb:
Code: 
Qkat
=
SELECT * FROM

(SELECT
Category.id,
Category.name,
Forum.id,
Forum.title
FROM
(SELECT
id,
name,
FROM
categories
ORDER BY
sort ASC
[B][COLOR="red"]SKIP kat[/COLOR][/B]
LIMIT 1
) AS Category

LEFT JOIN

forums AS Forum
ON (Category.id = Forum.category_id)
ORDER BY
Forum.created DESC
)

LIMIT 2
Zum Vergrößern anklicken....

Was ist denn das SKIP kat?
 
Also OFFSET ist doch so aehnlich wie LIMIT, darum versteh ich nicht ganz, warum dort beide sind.
Und was ist Qkat=?
 
LIMIT begrenzt die max. Anzahl der zurückzugebenden Tupel aus der qualifizierten Tupelmenge. OFFSET hingegen gibt an aber welchem Tupel der q. Tupelmenge Tupel zurückgegeben werden sollen.

SELECT name FROM categories ORDER BY sort ASC LIMIT 1 OFFSET 0
Zum Vergrößern anklicken....
liefert genau die erste Kategorie.

SELECT name FROM categories ORDER BY sort ASC LIMIT 1 OFFSET 1
Zum Vergrößern anklicken....
liefert genau die zweite Kategorie.

Deshalb wird dann auch sechs mal quasi die selbe Query benötigt nur mit jew. anderem OFFSET-Wert. (Ob das insgesamt so toll ist, ist eine andere Frage.)

Qkat ist die Query, die zur kat-en Kategorie (max.) die zwei aktuellsten Foren zurückgibt.
 
Zuletzt bearbeitet:
So mein erster query sieht so aus und funktioniert

Code: 
SELECT y.*
  FROM
  (
  SELECT
    x.*,
    @num   := if(@catId = c_id, @num + 1, 1) as row_number,
    @catId := c_id
    FROM (
      SELECT
        c.id	AS c_id,
        c.name	AS c_name,
        f.id	AS f_id,
        f.name	AS f_name
      FROM categories AS c
      LEFT JOIN forums AS f ON ( c.id = f.category_id )
      ORDER by c.id ASC, f.id ASC
    ) x
) y
WHERE y.row_number <= 2;

Ich muss jetzt noch rausfinden, wie ich zu jedem Forum Beispielsweise genau 4 Threads fetchen kann
 
Die Sache mit den Variablen als globale Merker ist genial. Ist das SQL-Standard oder MySQL-Dialekt? Insgesamt ist das ein tolle Sache.
 
Das weiss ich leider nicht, an sich auch nicht so schlimm wenn es woanders nicht geht, denn woanders hat man ja auch direkt row_numbers was hier nur emuliert wird
 
In anderen Datenbanken als MySql habe ich noch nichts gemacht. In Oracle - so wie ich es verstehe - ist die rownum eindeutig bzgl. der Ergebnismenge, deine emulierte row_number ist aber mehrdeutig.

Wie auch immer, wieder was gelernt und auf der Basis könnte ich jetzt genug Queries vereinfachen.
 
Ja das stimmt und vor allem kann man damit auch die anzahl der queries reduzieren.

Ich verkrepel grad daran ein 2. JOIN reinzubekommen auf die selbe weise.
So wie ich es mir gedacht habe, muesste ich die auesserste Tabelle y joinen oder kann ich das noch weiter reinschachteln?
 
So ich hab jetzt jetzt endlich so, dass es fuer viele JOINs funktioniert.
Hier mal das Beispiel mit X vielen Threads per Forum.

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

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

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

    -- [B]ORDER BY[/B] c.id [B]ASC[/B], f.id [B]ASC[/B], t.id [B]ASC[/B]
) c
 [B]WHERE [/B]CatRow <= [B]HOW_MANY_CATS[/B] [B]AND [/B]ForumRow <= [B]HOW_MANY_FORUMS_PER_CAT [/B][B]AND [/B]ThreadRow <= [B]HOW_MANY_THREADS_PER_FORUM[/B]

Man kann Jetzt X viele verschiedene Kategorien selektieren, zu jeder Kategoriy Y viele Foren und zu jedem Forum Z viele Threads.


Das ganze laesst sich jetzt recht einfach auf immer tiefere Ebenen anwenden. Hier noch ein Beispiel mit K viele Posts per Threads in dem ganzen obigen
Code: 
[B]SELECT[/B]
    CatRow,
    c_id,
    c_name,
    ForumRow,
    f_id,
    f_name,
    ThreadRow,
    t_id,
    t_title,
    PostRow,
    p_id, 
    p_title
[B]FROM [/B](
    [B]SELECT[/B]
        @cat_row    := [B]IF[/B](@prev_cat    = c.id, @cat_row, @cat_row+1)   [B]AS [/B]CatRow,
        @forum_row  := [B]IF[/B](@prev_forum  = f.id, @forum_row,  [B]IF[/B](@prev_cat   = c.id, @forum_row+1, 1)) [B]AS [/B]ForumRow,
        @thread_row := [B]IF[/B](@prev_thread = t.id, @thread_row, [B]IF[/B](@prev_forum = f.id, @thread_row+1, 1))[B]AS [/B]ThreadRow,
        @post_row   := [B]IF[/B](@prev_post   = t.id, @post_row,   IF(@prev_thread= t.id, @post_row+1, 1))  [B]AS [/B]PostRow,
        c.id        [B]AS [/B]c_id,
        c.name      [B]AS [/B]c_name,
        f.id        [B]AS [/B]f_id,
        f.name      [B]AS [/B]f_name,
        t.id        [B]AS [/B]t_id,
        t.title     [B]AS [/B]t_title,
        p.id        [B]AS [/B]p_id,
        p.title     [B]AS [/B]p_title,
        @prev_cat   := c.id,
        @prev_forum := f.id,
        @prev_thread:= t.id,
        @prev_post  := p.id
    [B]FROM [/B](
        [B]SELECT[/B]
            *
        [B]FROM[/B]
            forum_categories c,
            ([B]SELECT [/B]@cat_row := 0) [B]AS [/B]x,
            ([B]SELECT [/B]@prev_cat := '') [B]AS [/B]y
        [B]ORDER BY[/B] @cat_row
    ) [B]AS [/B]c

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

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

    [B]LEFT JOIN[/B] (
        [B]SELECT[/B]
            *
        [B]FROM[/B]
            forum_posts [B]AS [/B]p,
            ([B]SELECT [/B]@post_row := 0) [B]AS [/B]x,
            ([B]SELECT [/B]@prev_post := '') [B]As [/B]y
        [B]ORDER BY [/B]@post_row
    ) [B]AS [/B]p [B]ON [/B](t.id = p.fk_forum_thread_id )

--	[B]ORDER BY[/B] c.id [B]ASC[/B], f.id [B]ASC[/B], t.id [B]ASC[/B], p.id
) c
-- [B]WHERE [/B]CatRow <= HOW_MANY_CATS [B]AND [/B]ForumRow <= HOW_MANY_FORUMS_PER_CAT [B]AND [/B]ThreadRow <= HOW_MANY_THREADS_PER_FORUM [B]AND [/B]PostRow <= HOW_MANY_POSTS_PER_THREAD

Jetzt koennte man noch Y viele Edits per Post einbauen... und so weiter.
Am Ende erhaelt man jeweils eine Baumartige Struktur, die man dann super leicht ins OOP Konzept in die Sprache der Wahl einhaengen kann.


Edit: Hier mal die visuelle Ausgabe:
attachment.php
 

Anhänge

  • query_result.webp
    query_result.webp
    48,1 KB · Aufrufe: 779
Zuletzt bearbeitet:
Ich muss nochmal wegen der Performance nachhaken.
Also, so wie ich das sehe, waere das bei grossen Tabellen doch viel performanter, wenn ich die row_number Beschraenkungen direkt in den derived JOIN Tabellen machen wuerde, weil dadurch das Kreuzprodukt wesentlich kleiner waere.
Hier weiss ich allerdings nicht, ob mysql das obige query, von selbst schon so optimiert.

Hier nochmal der Explain, fuer das obige Query

attachment.php



Edit2:
Zum nachprobieren hab ich mal den table dump rangehangen.

Fuer den DB Client nutze ich heidisql, das laeuft unter wine auch in FreeBSD
 

Anhänge

  • explain.webp
    explain.webp
    79 KB · Aufrufe: 765
  • tables.sql.txt
    tables.sql.txt
    9,8 KB · Aufrufe: 589
Zuletzt bearbeitet:
Ich habe jetzt leider noch ein Problem bei der automatischen Code-generierung nach dem obigem Schema entdeckt.
Es sind ja alles one-to-many relations und wenn man die immer in einer Ebene tiefer verschachtelt, dann geht es. Hat am aber 2 one-to-many relations auf der selben Ebene, dann funktionieren die Limits in dieser Ebene nicht mehr.
Als Beispiel:

Eine Kategory hat sowohl viele Foren, als auch viele Bilder. Limitiere ich jetzt die Foren per Kategory, dann werden automatisch auch die Bilder per Kategory limitiert.

Gibt es da evtl noch einen Ausweg?
 
Du musst dich einloggen oder registrieren, um hier zu antworten.
Zurück
Oben