complexe order query

Lars Anderson

20/01/2015 13:37:05

Ik ben bezig aan een complexe query met 2 lagen 1 tot n relaties en daarnaast nog eens een laag 1 tot n relatie. Je moet je dit alsvolgt voorstellen.
Er is een order, aan deze order kunnen meerdere orderregels hangen (bijv. een t-shirt). Aan iedere orderregel kunnen vervolgens weer meerdere eigenschappen hangen (bijv. de kleur of maat van het shirt).
Daarnaast kunnen er nog 1 of meerder orderopties aan de order hangen (bijv. extra's bij de orders die niet op de orderregels thuis horen, maar waar wel een prijs aan hangt).

Nu heb ik het eerste stuk al wel rond gekregen middels subqueries.

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

SELECT
  `order_id`,
  `betaalmethode_prijs`,
  ... (etc.)
FROM
  orders
LEFT JOIN (
  SELECT
    `order_id`
    `order_regel_id`,
    `artikel_prijs`,
    ... (etc.)
  FROM
    order_regels
  LEFT JOIN (
   SELECT
     `order_regel_id`
     `order_regel_eigenchap`,
     `eigenschap_prijs`,
     ... (etc.)
   FROM
     order_regel_eigenschappen
  ) AS ore
  USING `order_regel_id`
) AS or
USING `order_id`
GROUP BY `order_id`

M.b.v. SUM() krijg ik daar mooi het totaal van een order mee. Echter nu wil ik dus ook de zijtak toevoegen. Ik dacht dit te kunnen doen door simpelweg een extra LEFT JOIN toe te voegen:

Code (php)

1
2
3
4
5
6
7
8
9

LEFT JOIN (
  SELECT
    `order_id`,
    `order_optie_id`,
    ... (etc.)
  FROM
    order_opties
) AS oe
USING (`order_id`)

Wat er dan gebeurd is, dat de het totaalbedrag van alles orderopties wordt toegevoegd, maar dan vermenigvuldig met het aantal order regels.
Ik kan er maar niet achterkomen wat er nu fout gaat. Waarschijnlijk (hopelijk!) is het iets heel simpels dat ik over het hoofd zie.
Wie helpt mij het licht te zien ??? ;-)

Gewijzigd op 20/01/2015 13:38:07 door Lars Anderson

PHP hulp

14/03/2025 03:56:58

Tutorial mod

Ger van Steenderen

20/01/2015 22:41:57

>>
M.b.v. SUM() krijg ik daar mooi het totaal van een order mee.

Weet je dat zeker?

Je laat niet de gehele query zien, maar vanuit wat ik kan zien heb je een EAV probleem.

Als een orderregel meerdere eigenschappen heeft zal je hem altijd meerdere malen in je result krijgen.

Lars Anderson

21/01/2015 09:04:17

Het is eigenlijk een uitbouw op een antwoord wat je me zelf gegeven hebt Ger ;-)
(http://www.phphulp.nl/php/forum/topic/sum-van-datediff-met-unique-idnummer/92914/)
Ik dacht het wat te versimpelen door een voorbeeld met orders te maken, maar wellicht heeft dat het alleen maar onduidelijker gemaakt.

Nog maar eens proberen dan.
Dit was jouw voorbeeld code:

Code (php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

SELECT
    SUM(fr.factuurprijs) omzet,
    SUM(fr.nights) aantal_nachten
FROM
    (SELECT
        t1_id,
        f.t1_price + SUM(ro.prijs_reservering) factuurprijs,
        SUM(ro.nachten) nights
    FROM
        facturen f
    JOIN
        (SELECT
            r.t1_id,
            t2_id,
            r.t2_price + COALESCE(SUM(o.t3_price),0) prijs_reservering,
            DATEDIFF(r.t2_end, r.t2_start) nachten
        FROM
            reserveringen r
        LEFT JOIN
            opties o
            USING (t2_id)
        GROUP BY
            r.t1_id,t2_id, nachten
        ) ro
        USING (t1_id)
    GROUP BY
        t1_id
    ) fr

Ik zou aan dit voorbeeld dus naast de geJOINde tabel "reserveringen" (met daarbinnen weer "opties") nog een extra tabel "products" willen toevoegen. Het uiteindelijk resultaat moet dan nog steeds de totaal factuurprijs worden (nu dus aangevuld met de totaalprijs van alle producten uit de separate tabel).

Ik heb het vooralsnog in PHP maar opgelost door een aparte query te gebruiken voor de producten (naast bovenstaande query). Dit betekent echter dat er net zo veel queries uitgevoerd worden als dat er facturen geselecteerd zijn. Dat is niet de ideale situatie. Ik zou het liefst allees in één query willen hebben.

Tutorial mod

Ger van Steenderen

21/01/2015 14:21:57

Je kan in een SELECT ook een subquery opnemen, deze moet dan wel maar één waarde teruggeven:

Code (php)

1
2
3
4
5
6
7
8

    .....
    JOIN
        (SELECT
            r.t1_id,
            t2_id,
            (SELECT SUM(product_price) FROM products p
                WHERE p.t1_id = r.t1_id) total_price,
            ......

Forum berichten

Reacties

PHP scripts

PHP tutorials

Actief op PHPhulp

complexe order query

Lars Anderson

PHP hulp

Ger van Steenderen

Lars Anderson

Ger van Steenderen

Over PHPhulp

Support

Gesponsord

Extra's

Privacy