[MySQL] Limit op bestellingen met meerdere bestelregels

[DelTorro]

Ik ben bezig met een overzicht van bestellingen. Hiervoor haal ik bestellingen samen met de bestelregels uit de database met:

SQL:

SELECT
    `bestellingen`.`naam`,
    `artikelen`.`titel`,
    `bestelregels`.`aantal`,
    `artikelen`.`prijs`
FROM
    `bestellingen`
        Inner Join `bestelregels`
            ON `bestelregels`.`bestelID` = `bestellingen`.`ID`
        Inner Join `artikelen`
            ON `bestelregels`.`artikelID` = `artikelen`.`ID`

Dit geeft bijvoorbeeld de volgende output:

Naam	Artikel	Aantal	Prijs
Henk	Boormachine	2	86
Henk	Moersleutel	1	4
Henk	Schroevendraaier	3	3
Piet	Hamer	1	2.2
Klaas	Boormachine	1	86
Klaas	Andere boormachine	2	72

Nu wil ik graag 30 bestellingen uit de database halen. Een limit van 30 achter de query plakken gaat natuurlijk niet werken aangezien er dan 30 bestelregels uit de database worden gehaald.

Ik heb de query uitgebreid:

SQL:

SELECT
    `bestellingen`.`naam`,
    `artikelen`.`titel`,
    `bestelregels`.`aantal`,
    `artikelen`.`prijs`
FROM
    `bestellingen`
        Inner Join `bestelregels`
            ON `bestelregels`.`bestelID` = `bestellingen`.`ID`
        Inner Join `artikelen`
            ON `bestelregels`.`artikelID` = `artikelen`.`ID`
WHERE
    `bestellingen`.`ID` <=
        (SELECT
            MAX(`bestellingen`.`ID`)
        FROM
            `bestellingen`
        LIMIT
            30)

Dit is niet heel betrouwbaar en het probleem is: ik wil graag kunnen sorteren op bijvoorbeeld de naam van mensen die een bestelling hebben geplaatst en op andere kolommen. Hoe kan ik dit nu aanpakken? Ik ben in de veronderstelling dat eigenlijk alles wel door middel van 1 query uit een database is te halen. Is dit hier mogelijk of moet ik van elke bestelling de bestelregels met een extra query uit de database halen?

[eek]

Volgens mij moet je met GROUP BY een heel end kunnen komen

[DelTorro]

eek schreef op woensdag 06 juni 2007 @ 15:04:
Volgens mij moet je met GROUP BY een heel end kunnen komen

Ik zie niet in op welke manier GROUP BY mij kan helpen. In de subquery is hij niet nodig en plak ik 'm achter de hoofdquery, dan worden er bestelregels 'weggelaten'. Ook met behulp van de FAQ zie ik niet hoe GROUP BY een oplossing voor mij kan zijn. Ik denk dus dat ik de oplossing moet zoeken in iets anders?

[MichelVH]

Ik snap je hele subquery niet In je subquery selecteer je de hoogste ID in de tabel, en in je query selecteer je dan alle regels met ID kleiner of gelijk aan de hoogste (= alles).

Volgens mij moet je in je subquery de IDs selecteren van de bestellingen die je wilt, en daar dan de bestelregels bij:

SQL:

SELECT
    `bestellingen`.`naam`,
    `artikelen`.`titel`,
    `bestelregels`.`aantal`,
    `artikelen`.`prijs`
FROM
    `bestellingen`
        Inner Join `bestelregels`
            ON `bestelregels`.`bestelID` = `bestellingen`.`ID`
        Inner Join `artikelen`
            ON `bestelregels`.`artikelID` = `artikelen`.`ID`
WHERE
    `bestellingen`.`ID` IN
        (SELECT
            `bestellingen`.`ID`
        FROM
            `bestellingen`
        WHERE
            ...
        LIMIT
            30)

[DelTorro]

MichelVH schreef op woensdag 06 juni 2007 @ 17:58:
Ik snap je hele subquery niet In je subquery selecteer je de hoogste ID in de tabel, en in je query selecteer je dan alle regels met ID kleiner of gelijk aan de hoogste (= alles).

Volgens mij moet je in je subquery de IDs selecteren van de bestellingen die je wilt, en daar dan de bestelregels bij:

Owja, natuurlijk. Ik zie nu pas dat het geheel niet klopt: stom. Ik ga zo je voorbeeldcode testen. Dan moet het wel lukken denk ik. Dankje

edit:
een limit in de subquery werkt niet in combinatie met (net buiten de subquery)

SQL:

`bestellingen`.`ID` IN
(subquery)

Ik krijg namelijk een foutmelding (in Navicat) wanneer ik dit wel doe: "This version of MySQL (5.0.33) doesn't yet support 'LIMIT _IN/ALL/ANY/SOME subquery'". Zodra ik de limit weghaal, werkt het. Probleem is dan dat ik nog steeds niet kan limitten op bestellingen. Zoiets dergelijks moet toch veel vaker voorkomen? Doordat ik er zo mee zit te stoeien, zit ik helemaal vast.

[ Voor 31% gewijzigd door DelTorro op 06-06-2007 22:29 ]

[BikkelZ]

ik wil graag 30 bestellingen uit de database halen

30 bestellingen? Als in? Niet meer dan 30 klanten die iets besteld hebben? De eerste 30 bestelde producten per klant? Niet meer dan 30 stuks per product? NIet meer dan 30 stuks van producten in totaal? Niet meer dan 30 productregels per klant?

Ik probeer een verklaring te vinden waarom je überhaupt niet gewoon LIMIT maar met een subquery gaat werken.

[DelTorro]

BikkelZ schreef op donderdag 07 juni 2007 @ 00:08:
[...]


30 bestellingen? Als in? Niet meer dan 30 klanten die iets besteld hebben? De eerste 30 bestelde producten per klant? Niet meer dan 30 stuks per product? NIet meer dan 30 stuks van producten in totaal? Niet meer dan 30 productregels per klant?

Ik probeer een verklaring te vinden waarom je überhaupt niet gewoon LIMIT maar met een subquery gaat werken.

Ik wil een limit van 30 klanten die iets besteld hebben (plus de daarbijbehorende bestelregels). Bestellingen toon ik op een pagina en door middel van Javascript kunnnen door een druk op de knop de bestelregels worden getoond (en verborgen). Het is dus belangrijk dat er een limit op bestellingen wordt toegepast.

[ Voor 4% gewijzigd door DelTorro op 07-06-2007 01:06 ]

[BikkelZ]

DelTorro schreef op donderdag 07 juni 2007 @ 01:05:
[...]


Ik wil een limit van 30 klanten die iets besteld hebben (plus de daarbijbehorende bestelregels). Bestellingen toon ik op een pagina en door middel van Javascript kunnnen door een druk op de knop de bestelregels worden getoond (en verborgen). Het is dus belangrijk dat er een limit op bestellingen wordt toegepast.

Stored procedure, cursor, for loop en dan een prepared statement opbouwen met de door de cursor verzamelde ID's. Met echt pure SQL kun je de logica niet op de plek leggen waar jij dat wilt volgens mij, tenzij je een tussenstapje maakt in je programma.

Andere optie is misschien een subquery voor 30 klanten JOIN subquery voor bestellingen ON klant.id = klant.id

Ik denk eigenlijk wel dat dat zou werken, hoe snel het is bij 100000 records laat ik even in het midden......

[Voutloos]

Tja, een eenvoudige oplossing is eerst een query voor die 30 klanten te doen en dan die 30 id's in de volgende query stoppen. Misschien niet de mooiste oplossing, maar je houd dan wel 2 zeer eenvoudige queries over welke qua performance nooit een probleem mogen opleveren.

[BikkelZ]

Voutloos schreef op donderdag 07 juni 2007 @ 07:53:
Tja, een eenvoudige oplossing is eerst een query voor die 30 klanten te doen en dan die 30 id's in de volgende query stoppen. Misschien niet de mooiste oplossing, maar je houd dan wel 2 zeer eenvoudige queries over welke qua performance nooit een probleem mogen opleveren.

Het vervelende vind ik zelf (en de TS waarschijnlijk ook) dat je dan ontzettend veel 'rotzooi' voor het vergaren van gegevens in je code krijgt, terwijl ik liever maar twee stappen maak, namelijk 'mits gegevens, ga deze weergeven'. Scheiding van gegevens vergaring en het programma zelf is sowieso een goede zaak.

Te veel query-'rotzooi' vind ik mijn code vertroebelen, ik heb eens voor de test een keer alle queries bovenaan gezet in losse strings of waar nodig in stored procedures, views, etcetera en dat resulteerde in enorm compacte en leesbare code én queries (daar geef ik namelijk ook het liefste enters en tabjes in namelijk).

[BikkelZ]

Dit bedoel ik bijvoorbeeld, voorbeeldje van een query voor een database van mij:

SQL:

SELECT k.id
FROM (

SELECT k.id
FROM kontakt k
LIMIT 30
)k
JOIN reactie r ON k.id = r.kontakt_id

Je maakt dus een tijdelijke tussentabel door een subquery met een limit te doen die je gebruikt als tabel die joint naar je gewone tabellen verder.

[ Voor 44% gewijzigd door BikkelZ op 07-06-2007 10:32 ]

[DelTorro]

BikkelZ schreef op donderdag 07 juni 2007 @ 10:30:
Je maakt dus een tijdelijke tussentabel door een subquery met een limit te doen die je gebruikt als tabel die joint naar je gewone tabellen verder.

Hulde! Dit doet, na een beetje sleutelen, precies wat ik wil.

SQL:

SELECT
    b.`id`,
    b.`naam`,
    a.`titel`,
    br.`aantal`,
    a.`prijs`
FROM
    (SELECT
        b.`id`,
        b.`naam`
    FROM
        `bestellingen` b
    LIMIT
        3) b
        Inner Join `bestelregels` br
            ON br.`bestelID` = b.`ID` 
        Inner Join `artikelen` a
            ON br.`artikelID` = a.`ID`
ORDER BY
    b.`naam` ASC

[Janoz]

Heeft Mysql nog geen 'in' ? In dat geval had je met je start query al een heel eind kunnen komen:

SQL:

SELECT
    `bestellingen`.`naam`,
    `artikelen`.`titel`,
    `bestelregels`.`aantal`,
    `artikelen`.`prijs`
FROM
    `bestellingen`
        Inner Join `bestelregels`
            ON `bestelregels`.`bestelID` = `bestellingen`.`ID`
        Inner Join `artikelen`
            ON `bestelregels`.`artikelID` = `artikelen`.`ID`
WHERE
    `bestellingen`.`ID` IN
        (SELECT
            `bestellingen`.`ID`
        FROM
            `bestellingen`
        LIMIT
            30)

Sorteren enz kun je dan gewoon in die subquery doen lijkt me.

@hieronder: Ah, overheen gelezen. het is alweer een tijd terug dat ik iets met MySQL gedaan heb. Een dergelijke aanpak werkt in MSSQL wel.

[ Voor 9% gewijzigd door Janoz op 07-06-2007 16:14 ]

[BikkelZ]

Je mag geen LIMIT op een subquery doen die valt onder een IN, anders was het topic nooit geopend.

[Voutloos]

LIMIT in een dergelijke IN (subquery) werkt niet.

[DelTorro]

Janoz schreef op donderdag 07 juni 2007 @ 15:53:
Heeft Mysql nog geen 'in' ? In dat geval had je met je start query al een heel eind kunnen komen:

Sorteren enz kun je dan gewoon in die subquery doen lijkt me.

Dit heb ik al geprobeerd naar aanleiding van de post van MichelVH in dit topic maar werkt niet zoals even hierboven ook is te lezen.

[ Voor 23% gewijzigd door DelTorro op 07-06-2007 16:10 ]