Ik ben bezig aan een webwinkel. Heb al eens eerder een gemaakt, maar deze is lastiger.
De query moet een tabel opleveren die alle binnengekomen orders op een rijtje zet.
Ik heb 4 tabellen waar de informatie vandaan moet komen:
KLANTEN
id | voornaam | achternaam | etc...
ORDERS
id | klanten_id | datum
ORDERREGELS
orders_id | producten_id | aantal
PRODUCTEN
id | naam | prijs
Wat ik wil maken is een overzicht (een tabel) van de binnengekomen orders.
Ik heb al een vrij lange query; die gaat als volgt:
$sql = "SELECT orders.id, orders.datum, orders.status, klanten.voornaam, klanten.achternaam, count(producten.id) as aantal, sum(producten.prijs) as waarde, orderregels.orders_id, orderregels.producten_id, sum(orderregels.aantal) as totaalaantal
FROM orders, klanten, orderregels, producten
WHERE orderregels.orders_id = orders.id
AND producten.id = orderregels.producten_id
AND klanten.id = orders.klanten_id";
Het is een query die goed werkt. Het levert veel waardevolle informatie. Wat ik echter nog graag zou willen zien is de totale waarde van zo'n order. Als er zeg maar 2 producten van 50 euro zijn besteld, en 3 producten van 100 euro, dat er dan staat: Totale waarde: 400 euro.
De query die je in je eerste post hebt staan is niet correct, ondanks dat hij misschien juiste waarden lijkt te retourneren. Zodra je groepeert op een bepaalde kolom (orders.id) is het niet meer mogelijk om afzonderlijke velden uit dezelfde tabel of andere gekoppelde tabellen op te halen.
Een niet goed ingestelde MySQL database doet vermoeden dat de query wel goed uitgevoerd wordt, maar in werkelijkheid worden er gewoon willekeurige waarden voor die velden geretourneerd. Zie ook deze handleiding over het juiste gebruik van GROUP BY.
Het komt er dus op neer dat je aparte queries uit zult moeten voeren voor het opvragen van de orderdetails (id, orderregels en bijbehorende producten) en voor het berekenen van aantallen en totalen.
Nop niet noodzakelijkerwijs nodig... het kan in 1 query
De query zoals hij daar staat levert geen correcte resultaten op. Zodra je groepeert op orders.id is het onmogelijk om bijvoorbeeld nog alle orderregels.product_id apart op te halen. Als je de missende velden dan aan je GROUP BY zou toevoegen (ie. orderregels.product_id), leveren de aggregate functies niet meer de gewenste resultaten.
ps. Net zo min is het bepalen van het totaal van alle orders mogelijk in deze query, aangezien je dan alle orders als 1 groep beschouwt en je dus geen afzonderlijke gegevens per order kunt selecteren.
pps. Wat betreft de totaalprijs van de orders is er trouwens nog een belangrijk feit waar je rekening mee moet houden. Zodra een order geplaatst wordt, ligt de prijs daarvan vast. Prijzen van producten afzonderlijk kunnen in de toekomst echter wijzigen waardoor in de huidige opzet de orderprijs ook zou wijzigen. Dat is ongewenst en dus zou je ervoor moeten kiezen om de prijs van de order ook in de orders tabel op te nemen. Het berekenen van een totaalprijs van alle orders is in dat geval een fluitje van een cent!
Bedankt voor je reactie, maar volgens mij is er niets mis met mijn query, het geeft juiste resultaten, er groepeert mooi op orders.id.
En volgens mij worden er ook geen 'willekeurige waarden' geretourneerd.
Ik zat ook te denken aan aparte queries, maar toen dacht ik dat het vast wel in 1 query moet kunnen, ik weet alleen niet hoe.
Omdat je moet vermenigvuldigen en optellen.
Je moet de prijs nemen, die in de tabel PRODUCTEN staat, en die vermenigvuldigen met het aantal dat in de tabel ORDERREGELS staat, wanneer deze twee tabellen met elkaar gelinkt zijn dmv de id's (in ORDERREGELS staat producten_id).
En dat moet dan voor ieder product dat in de order is opgenomen.
Ik loop al vast als ik eraan denk. Noppes, jij zegt dat het in één query kan, hoe zou ik dat kunnen doen?
Blanche, wat je zegt over het veranderen van de prijs, daar heb je gelijk in. Dat is een hele goede tip, dat had ik waarschijnlijk over het hoofd gezien, super bedankt :)
Ik zal de prijs van het moment opnemen in de ORDERREGELS-entry, zodat wanneer de prijs verandert in PRODUCTEN een eerdere order niet wordt aangetast.
Blanche, zou je dan weten hoe ik dan die informatie die ik graag wil hebben, zoals welke producten zijn opgenomen in een order, en wat voor totale waarde die dan hebben, kan opvragen met een query?
welke producten zijn opgenomen in een order, en wat voor totale waarde die dan hebben
Zoals je zelf al ziet, zijn dat twee verschillende vragen dus zul je daar ook twee verschillende queries voor moeten gebruiken.
In eerste instantie 1 query waarmee je alle productinformatie per order ophaalt en ten tweede een waarmee je de totale waarde van alle orderregels (de som van de nieuwe waardes die je per orderregel opslaat) bepaalt.
Dus iets als:
SELECT
p.naam,
p.omschrijving,
o.aantal
FROM
orderregels AS o
INNER JOIN
producten AS p
ON p.id = o.product_id
WHERE
o.order_id = 123
Dit haalt alle van alle producten van order 123 de naam, omschrijving en aantal op.
SELECT
SUM(totaal) AS order_totaal
FROM
orderregels
WHERE
o.order_id = 123
