[PHP/SQL] Alleen laatste rij joinen

[Elektronicanet]

Ik ben bezig met een database voor een webshop. Om de omzet in een bepaalde periode te berekenen, al dan niet voor bepaalde producttypen / betaalmethoden gebruik ik de volgende SQL query:

code:

SELECT bestellingen.*, order_producten.*, producten.type
FROM order_producten 

JOIN bestellingen ON order_producten.order_id=bestellingen.id 
JOIN producten ON order_producten.bestelnr=producten.id 

WHERE bestellingen.datum < FROM_UNIXTIME(%s) 
AND bestellingen.datum > FROM_UNIXTIME(%s) 
AND betaalmethode LIKE '%s' 
AND type LIKE '%s'

Dit werkt prima, ik krijg een mooi overzicht van alle bestelde producten. Met PHP tel ik de bedragen / verzendkosten op, dat gaat goed.

Nu had ik echter één ding over het hoofd gezien. Bestellingen hebben een status. Er zijn verschillende statussen, maar er zijn er 2 belangrijk:
- Bestelling geplaatst
- Geannuleerd

In deze gevallen mag de bestelling (bestaande uit 1 of meerdere producten) niet worden meegenomen voor de omzet berekening, omdat er (nog) geen betaling is ontvangen.
De statussen zijn opgeslagen in tabel order_statussen, en krijgen een datum (timestamp), id (=ordernummer) en status mee.

Nu kan ik de statustabel joinen met:

code:

LEFT JOIN order_statussen ON order_producten.order_id=order_statussen.id

Maar het probleem is dat er per bestelling vaak meerdere statussen zijn. Eerst bestelling geplaatst, dan betaling ontvangen, dan verzonden. Alleen de laatste status (hoogste timestamp waarde) mag worden gekoppeld aan de tabel. Ik wil dus eigenlijk een WHERE voorwaarde binnen de LEFT JOIN opgeven, maar dat kan volgens mij niet.

Misschien kan ik een WHERE voorwaarde op de gehele query toevoegen, die dan alleen de nieuwste status (op basis van order_statussen.datum) binnen een bepaalde order laat zien. Maar ik weet niet hoe ik dat moet doen. Wie kan me helpen?

[Verwijderd]

Sub-query gebruiken om die status per bestelling op te zoeken?

[Creepy]

Kijk eens naar aggregated functies. Met MAX() kan je prima de hoogste datum selecteren. Daar hoort uiteraard nog een GROUP BY bij. Zie ook Programming FAQ - SQL- Group By

[frickY]

SQL:

HAVING order_producten.order_datum = MAX(order_producten.order_datum)

zou volgens mij moeten werken, na het toevoegen van je genoemde join.

[Elektronicanet]

Creepy schreef op vrijdag 27 juli 2007 @ 13:53:
Kijk eens naar aggregated functies. Met MAX() kan je prima de hoogste datum selecteren. Daar hoort uiteraard nog een GROUP BY bij. Zie ook Programming FAQ - SQL- Group By

Bedankt, hiermee ben ik al wat verder gekomen. Maar ik denk dat ik iets nog niet goed heb begrepen, omdat ik nu willekeurige statussen krijg:

code:

SELECT bestellingen.*, order_producten.*, producten.type, order_statussen.status, max(order_statussen.datum) 
FROM order_producten 

JOIN bestellingen ON order_producten.order_id=bestellingen.id 
JOIN producten ON order_producten.bestelnr=producten.id 
LEFT JOIN order_statussen ON order_producten.order_id=order_statussen.id 

WHERE bestellingen.datum < FROM_UNIXTIME(%s) 
AND bestellingen.datum > FROM_UNIXTIME(%s) 
AND betaalmethode LIKE '%s' 
AND type LIKE '%s' 

GROUP BY bestellingen.id

De code van Fricky resulteert in de volgende fout:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE bestellingen.datum < FROM_UNIXTIME(1185919200) AND bestellingen.datum > FR' at line 1

Ik heb de code alsvolgt ingevoegd:

code:

LEFT JOIN order_statussen ON order_producten.order_id=order_statussen.id 
HAVING order_statussen.datum = MAX(order_statussen.datum) 
WHERE ......

[ Voor 23% gewijzigd door Elektronicanet op 27-07-2007 15:43 ]

[_js_]

In de juiste volgorde zetten
select
from
where
group by
having
order by

[Elektronicanet]

Dat helpt inderdaad. Ik ben nu een heel eind met de code van Fricky. Hier heb ik nog een GROUP BY toegevoegd vóór HAVING. Ik weet alleen niet op welk criterium ik de GROUP BY moet doen. Ik zou zeggen op order_producten.bestelnr (dus maar 1 productcode per bestelling laten zien met 1 status dus) maar dan mis ik een hoop orders.

[_js_]

In principe op alles, behalve de velden uit order_statussen.

Als je MySQL gebruikt kun je ook met alleen de primairy keys uit de andere tabellen toe.

[Elektronicanet]

Haalt helaas niets uit. Ik voeg steeds meer velden toe bij GROUP BY maar ik blijf records missen. Bovendien krijg ik vaak niet de laatste status mee ondanks HAVING order_statussen.datum = MAX(order_statussen.datum)

[ Voor 1% gewijzigd door Elektronicanet op 27-07-2007 17:09 . Reden: ORDER BY --> GROUP BY ]

[Verwijderd]

Elektronicanet schreef op vrijdag 27 juli 2007 @ 17:06:
Haalt helaas niets uit. Ik voeg steeds meer velden toe bij ORDER BY maar ik blijf records missen. Bovendien krijg ik vaak niet de laatste status mee ondanks HAVING order_statussen.datum = MAX(order_statussen.datum)

Je bedoelt neem ik aan de GROUP BY?

Wat ik altijd doe met al die joins is de query in bijv PHPMyadmin gooien zonder group by en dan net zo lang 'GROUP BY-en' totdat het klopt (in stapjes dus). Handige is dan dat je de hele tabel voor je hebt en goed kan zien waarop je moet groeperen met een beetje logisch nadenken.

[Creepy]

Je moet ook niet ORDER BY uitbreiden maar de GROUP BY. Daarmee selecteer je in principe welke rijen er wegvallen en welke rijen er mee worden genomen in de MAX. Overigens staat de werking van group by ook uitgelegd in het FAQ linkje dat ik poste.

[Elektronicanet]

ORDER BY was een typefout, sorry. FAQ heb ik uiteraard doorgelezen. Op basis daarvan dacht ik te sorteren op ordernummer, vervolgens op productnummer. Dan zou ik alle records toch moeten hebben

Ik ga de myadmin manier proberen, is inderdaad handiger.

Update: ik heb nu de manier van Creepy nog geprobeerd omdat ik er met GROUP BY / HAVING echt niet uit kom. Het probleem is nu dat max(order_statussen.datum) netjes de juiste datum doorgeeft waarop de status is aangepast, maar de bijbehorende status niet. Ik gebruik een GROUP BY op bestellingen.id en order_producten.bestelnr. Het lijkt er op dat alle producten netjes worden weergegeven, alleen krijg ik dus willekeurige statussen.

[ Voor 59% gewijzigd door Elektronicanet op 27-07-2007 17:40 ]

[frickY]

Anders moet je even een 2de join op de status tabel doen, en daarop een MAX() doen om de eerste te filteren

Dus in de trant van

SQL:

SELECT bestellingen.*, order_producten.*, producten.type, order_statussen.status, max(order_statussen.datum), MAX(order_producten1.order_datum) AS laatsteStatus
FROM order_producten 
JOIN bestellingen ON order_producten.order_id=bestellingen.id 
JOIN producten ON order_producten.bestelnr=producten.id 
LEFT JOIN order_statussen1 ON order_producten1.order_id = order_statussen.id 
LEFT JOIN order_statussen2 ON order_producten2.order_id = order_statussen.id 
WHERE bestellingen.datum < FROM_UNIXTIME(%s) 
AND bestellingen.datum > FROM_UNIXTIME(%s) 
AND betaalmethode LIKE '%s' 
AND type LIKE '%s' 
HAVING order_producten2.order_datum = laatsteStatus

Vind het altijd lastig dergelijke queries in 1x goed te krijgen, maar met een beetje kliederen zou je eruit moeten komen Misschien dat je de MAX() ook al wel in de ON-clause van de join van order_statussen2 kunt opnemen, of misschien dat dit juist helemaal niet meer werkt.

Voor de volledigheid;
Een WHERE gebruik je om je gegevensset te filteren. Vervolgens worden de joins gemaakt met de gegevens die overblijven na het filteren, en heb je een recordset. Die recordset wordt vervolgens weer gefiltert op je HAVING.
In je HAVING kun je dus filteren op resultaten van bepaalde joins of aggregate functions.
Technisch zal het wellicht iets anders werken, maar ik meen dat dit de werking wel correct uitlegt.

[ Voor 24% gewijzigd door frickY op 27-07-2007 18:53 ]

[ACM]

Je kan natuurlijk ook gewoon met een subselect werken...

SQL:

SELECT bestellingen.*, order_producten.*, producten.type,
   order_statussen.status, order_statussen.datum
FROM order_producten 
JOIN bestellingen ON order_producten.order_id=bestellingen.id 
JOIN producten ON order_producten.bestelnr=producten.id 
LEFT JOIN order_statussen ON order_producten.order_id=order_statussen.id 
     AND order_statussen.datum = 
       (SELECT max(datum) FROM order_statussen
                     WHERE id = order_producten.order_id)
WHERE bestellingen.datum < FROM_UNIXTIME(%s) 
AND bestellingen.datum > FROM_UNIXTIME(%s) 
AND betaalmethode LIKE '%s' 
AND type LIKE '%s' 

[Elektronicanet]

ACM Super! Precies wat ik bedoelde. Wist alleen niet dat je het op die manier kon formuleren. Rest natuurlijk ook bedankt, maar dit werkt gewoon in 1x perfect!